sql server query

Question

HI,

i have a table with 5 columns

keyword D1 D2 D3 Total
how      1  1  1   3
are      0  0  2   2
you      0  1  0   1

i want to retrieve only columns count where the name starts with D so form the above table the result should be 3 please help.

Answer 1

@vj4u, you can format your table data better by editing your question, highlighting the table data and clicking the button shaped like this:

101
010

Like so:

keyword D1 D2 D3 Total 
how     1  1  1  3 
are     0  0  2  2 
you     0  1  0  1

Answer 2

SELECT D1,
       D2,
       ...etc...
       D3
    FROM mytable;

I would recommend that you do not attempt to develop any 'automatic' method of finding which columns start with 'D'. That sort of thing only ever ends in tears.

Changes to the schema should mean changes to the commands reading it.

Answer 3

If I understand your question, and my belief that SQL Server has the same metadata tables as Sybase is correct, then something like

select  count(*)
from    sysobjects o,
        syscolumns c
where   c.id = o.id
and     o.type = 'U' -- user tables
and     o.name = '<your_table_name>'
and     c.name like 'D%'

should so the trick.

Answer 4

SELECT
    Keyword, D1, D2, D3, D1 + D2 + D3 AS Total
FROM
    MyTable

You know the 3 columns starting D already: it's inane to do it dynamically in case you add a new column D4