Dynamic pivot table query with grouping in T-SQL

user_17252773 0
Viewed 47

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.

tsql sql-server sql
1 Answers

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

user_15700000
Related