CODEWITHSUNDEEP
sql-serverdatabaset-sqldynamicrelational-database
m6a-uds
m6a-uds

Reputation: 935

T-SQL grouping with dynamic column number

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!

Upvotes: 0

Views: 599

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

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?

Upvotes: 0

Taryn
Taryn

Reputation: 247850

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

Upvotes: 3

Related Questions