Dynamic pivot table query with grouping in T-SQL-CodePudding

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.

CodePudding user response：

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