How to query using an array of columns on SQL Server 2008

Question

Can you please help on this, Im trying to write a query which retrieves a total amount from an array of columns, I dont know if there is a way to do this, I retrieve the array of columns I need from this query:

    USE Facebook_Global
GO

SELECT c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns AS c 
    ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name LIKE '%Lifetime Likes by Gender and###$%' and c.name like '%m%'

Which gives me this table

column_name
M#13-17
M#18-24
M#25-34
M#35-44
M#45-54
M#55-64
M#65+

So I need a query that gives me a TotalAmount of those columns listed in that table. Can this be possible?

Just to clarify a little:

I have this table

Date    F#13-17 F#18-24 F#25-34 F#35-44 F#45-54 F#55-64 F#65+   M#13-17 M#18-24 M#25-34 M#35-44 M#45-54 M#55-64 M#65+
2015-09-06 00:00:00.000 257 3303    1871    572 235 116 71  128 1420    824 251 62  32  30
2015-09-07 00:00:00.000 257 3302    1876    571 234 116 72  128 1419    827 251 62  32  30
2015-09-08 00:00:00.000 257 3304    1877    572 234 116 73  128 1421    825 253 62  32  30
2015-09-09 00:00:00.000 257 3314    1891    575 236 120 73  128 1438    828 254 62  33  30
2015-09-10 00:00:00.000 259 3329    1912    584 245 131 76  128 1460    847 259 66  37  31
2015-09-11 00:00:00.000 259 3358    1930    605 248 136 79  128 1475    856 261 67  39  31
2015-09-12 00:00:00.000 259 3397    1953    621 255 139 79  128 1486    864 264 68  41  31
2015-09-13 00:00:00.000 259 3426    1984    642 257 144 80  129 1499    883 277 74  42  32

And I need a column with a SUM of all the columns containing the word F and other containig the word M, instead of using something like this: F#13-17+F#18-24+F#25-34+F#35-44+F#45-54+etc.

Is this possible?

Yes, certainly it is possible. You will have to create a dynamic query and execute it. Stored Procedure is how I'd go about it. Here's an example of stored procedure with dynamic SQL. — zedfoxus
– zedfoxus, Commented Nov 6, 2015 at 17:30

Dan Bracuk · Accepted Answer · 2015-11-06 17:34:44Z

1

Try something like this:

with derivedTable as 
(sql from your question goes here)
select column_name
from derivedTable 
union
select cast(count(*) as varchar (10) + 'records'
from derivedTable

answered Nov 6, 2015 at 17:34

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dasg7 Over a year ago

Thank you for your answer Dan, I just edited my question because I think I wasnt too clear.

Collectives™ on Stack Overflow

How to query using an array of columns on SQL Server 2008

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related