Summary of dynamically added columns for each row

Question

I have a table with many columns (dynamically generated columns) and a rows with USER_KEY (which has type INT).

Type of dynamic added columns is DECIMAL(15,2).

My table looks like this:

What I would like to do is get summary for each user for all columns in that row. Since there are so many of them dynamically generated, I can not hard type this. How to do this?

I also have variable @COLDEPARTMENTS, where all those dynamic columns are separated by comma, like this:

[120000003],[120000002],[140000001],[120000005],[120000021],[120000025]

score 1 · Answer 1 · edited May 23 '17 at 12:09

1

I assume you are using temp tables

select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name like '#MyTempTable%'

Please refer How to retrieve field names from temporary table (SQL Server 2008)

edited May 23 '17 at 12:09

Community

1
1

answered Feb 21 '17 at 10:35

ben yip

117
1
6

score 1 · Answer 2 · answered Feb 21 '17 at 10:47

You can use the below script to get all the auto-generated columns as single row with comma separated.

Declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + Column_Name + ', ' from [AdventureWorksDW2014].INFORMATION_SCHEMA.COLUMNS
where table_name like '%FactInternetSales%'

select SUBSTRING(@tmp, 0, LEN(@tmp)) as Columns

You can also store the result in variable and use that with your original table.

score 1 · Answer 3 · answered Feb 21 '17 at 10:47

If you are trying to identify which columns have values and you don't want to type out the column names. Then the following should do what you want.

SELECT 'SELECT user_key, '+ 
       + cols.ColumnList + CHAR(10) +
       + ' FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + CHAR(10)
FROM   sys.tables t
       CROSS APPLY (SELECT DISTINCT STUFF(
                    ( SELECT CHAR(10) + CHAR(9) + ', ' 
                             + QUOTENAME(c.name) + ' = CASE WHEN ' + QUOTENAME(c.name) + ' IS NULL THEN 1 ELSE 0 END'
                      FROM   sys.columns c 
                      WHERE  c.object_id = t.object_id
                      AND    c.name != 'user_id'
                      FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,3,'') AS ColumnList
            )cols 
WHERE  t.name = '{TABLE NAME}'

Summary of dynamically added columns for each row

3 Answers3