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?

• zedfoxus: 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.

Answer 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 

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