CODEWITHSUNDEEP
sqlt-sqlpivotsql-server-2014
pee2pee
pee2pee

Reputation: 3802

T-SQL Columns to rows with dynamic data not the same amount of rows

I've done this using Python but was wondering if the same was possible using T-SQL. So here is the result from my SQL currently.

locality | group | amount
-------------------------
A        | X     | 1
A        | Y     | 2
A        | Z     | 3
B        | X     | 4
B        | Y     | 5
C        | X     | 6
C        | Z     | 7
D        | X     | 8

What I want is to pivot the table so it looks like this

locality | X | Y | Z 
---------------------
A        | 1 | 2 | 3
B        | 4 | 5 | 0
C        | 6 | 0 | 7
D        | 8 | 0 | 0

As you can see, each locality may or may not have all groups. I've used A-D and X-Z but these can be in their dozens so this is just a cut down example.

I'm out of my depth in T-SQL unfortunately and was wondering how I'd go about getting this working in T-SQL and depend less on Python.

I've not tried anything as yet as not sure where to even start I'm afraid.

Thanks

Upvotes: 0

Views: 32

Answers (1)

D-Shih
D-Shih

Reputation: 46239

You can try to dynamic pivot.

use condition aggregate function with dynamic SQL to make it.

The key point might be how to contact SQL string for condition aggregate function.

We can try to use STUFF function to dynamic creates a condition aggregate function as @cols variable.

MAX(CASE WHEN [group] = 'X' THEN amount ELSE 0 END) as 'X', 
MAX(CASE WHEN [group] = 'Y' THEN amount ELSE 0 END) as 'Y', 
MAX(CASE WHEN [group] = 'Z' THEN amount ELSE 0 END) as 'Z'

so that the code might look as below.

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

SET @cols = STUFF((
            SELECT distinct ', MAX(CASE WHEN [group] = '''+ [group] + ''' THEN amount ELSE 0 END)' + ' as '''+[group]+''''
            FROM T
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT locality, ' + @cols + '  
FROM T t1
GROUP BY locality  '
EXEC(@query)

sqlfiddle

Upvotes: 2

Related Questions