T-SQL grouping with dynamic column number

Question

Let's say I have a 1D-like table like this:

TBL_1
[ Task  ][ Entity ][ Timespan ]
[ TASK1 ][  ID1   ][    3     ]
[ TASK2 ][  ID2   ][    4     ]
[ TASK2 ][  ID1   ][    5     ]
[ TASK1 ][  ID2   ][    6     ]

How would I turn it into a 2D-like view like this:

[ Entity ][ TASK1 ][ TASK2 ]
[  ID1   ][   3   ][   5   ]
[  ID2   ][   6   ][   4   ]

Given that the number of tasks should be dynamic?

My current solution looks like that:

SELECT A.Entity, B.Task TASK1, C.Task TASK2
FROM (SELECT DISTINCT ENTITY FROM TBL_1) A
LEFT JOIN TBL_1 B
ON A.Entity = B.Entity AND B.Task = 'TASK1'
LEFT JOIN TBL_1 C
ON A.Entity = C.Entity AND C.Task = 'TASK2'

But this requires me to "Hardcode" the tasks. How can-I make this dynamic?

Thank you very much!

look up pivot for which ever version of sql server you are using. — Tony Hopkinson, Aug 07 '12 at 15:36
Reference this thread on PIVOT tables: [http://stackoverflow.com/questions/7674786/mysql-pivot-table][1] [1]: http://stackoverflow.com/questions/7674786/mysql-pivot-table — BoeroBoy, Aug 07 '12 at 15:55
@BoeroBoy those links are for MySQL and the question is referencing sql server. — Taryn, Aug 07 '12 at 16:15

score 3 · Accepted Answer · answered Aug 07 '12 at 15:40

3

You are looking for a PIVOT. You can use a Dynamic SQL Pivot for this. This tactic will get the column names to transform on execution of the query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(task) 
                    from t1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT entity, ' + @cols + ' from 
             (
                select entity, task, timespan
                from t1
            ) x
            pivot 
            (
                min(timespan)
                for task in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

answered Aug 07 '12 at 15:40

Taryn

242,637
56
362
405

Thank you very much, that was exactly what I was looking for! I'll have to learn how this whole STUFF/XML/PATH/PIVOT thing works, though :-) – m6a-uds Aug 07 '12 at 17:55
Is there any way I could do a SELECT ... FROM [THIS_SOLUTION_ENCAPSULATED] with this? VIEWS & FUNCTIONS don't seem to allow Dynamic SQL, we can't SELECT from a Stored Procedure – m6a-uds Aug 08 '12 at 15:00
how are you planning on using this? You can place this code in a stored proc and get the data from that. – Taryn Aug 08 '12 at 15:05
Well, let's say the resulting (pivoted) dataset is named TBL_2, I would like to be able to do something like `SELECT * FROM TBL_2 WHERE Entity LIKE 'ID%'`, or `Task1>20`, or whatever... For now I made a Stored Proc, but I can't WHERE on it, or JOIN, or everything we use to do on tables... For now it is no problem, but it would be more... elegant. – m6a-uds Aug 08 '12 at 17:22
1

@m6a-uds you will want to look at inserting the values from the stored procedure into a temp table. Look at using `OPENROWSET`, there are answer on SO about doing it – Taryn Aug 08 '12 at 17:48

Gordon Linoff · Answer 2 · 2012-08-07T16:06:58.617

0

You can also do this in the spirit of what you were trying. However, using a group by is much simpler than what you were attempting:

SELECT ENTITY,
       max(case when t.task = 'Task1' then timespan end) as task1,
       max(case when t.task = 'Task2' then timespan end) as task2,
       . . .
FROM TBL_1 t
group by entity

To dynamically generate the column, you would need to use dynamic SQL. Is this what you really want?

edited Aug 07 '12 at 16:06

answered Aug 07 '12 at 15:51

Gordon Linoff

1,242,037
58
646
786

1

True. I think that the OP is looking for a way to dynamically generate the column aliases as the filter – swasheck Aug 07 '12 at 15:53

T-SQL grouping with dynamic column number

2 Answers2