CODEWITHSUNDEEP
sqlsql-servert-sql
VeniceKing
VeniceKing

Reputation: 25

Dynamic pivot table query with grouping in T-SQL

I'm trying to figure out a query for a pivot, but grouping by a column first. I have the following data in my table:

ID Label Tag1 Tag2 Tag3 Tag4
1 A1 1 0 0 1
2 B1 0 0 1 0
2 C1 0 1 0 0
3 A1 1 1 0 0
4 B1 0 0 0 1
4 C1 1 0 0 1

The final result I'm looking for is something like this:

ID 1 2
1 A1 (1,0,0,1)
2 B1 (0,0,1,0) C1 (0,1,0,0)
3 A1 (1,1,0,0)
4 B1 (0,0,0,1) C1 (1,0,0,1)

In this example I have 2 columns max, but it could be N columns. I worked with pivots before, but this one adds an extra difficulty with the Tag columns in there.

Upvotes: 2

Views: 95

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Based on comments, it seems you are looking for some Dynamic SQL

Example

Declare @SQL varchar(max) = '
Select *
 From  (
        Select ID
              ,Item = row_number() over (partition by ID order by Label)
              ,Value = concat(Label,'' ('',Tag1,'','',Tag2,'','',Tag3,'','',Tag4,'')'')
         From YourTable
       ) src
 Pivot ( max(Value) for Item in ( ' + stuff(( Select distinct ','+quotename(row_number() over (partition by ID order by Label)) 
                                              From  YourTable
                                               For XML Path('')),1,1,'') +')) pvt
'

Exec(@SQL)


Results

enter image description here

Upvotes: 1

Related Questions