Convert table 1 to table 2:
Table 1
Table 2
To get the results of table 2, you need to go through several pivot transformations, and then join together by union, the code is as follows:
1 SelectID, type,sum([1])[1],sum([2])[2],sum([3])[3],sum([4])[4] from2 (3 Select 'a' asType* fromTable_14Pivotsum(a) forPinch([1],[2],[3],[4])) asa5 Union All6 Select 'b' asType* fromTable_17Pivotsum(b) forPinch([1],[2],[3],[4])) asb8 Union All9 Select 'C' asType* fromTable_1TenPivotsum(c) forPinch([1],[2],[3],[4])) asC One Union All A Select 'D' asType* fromTable_1 -Pivotsum(d) forPinch([1],[2],[3],[4])) asD - ) T1 the Group byId,type - Order byId,type
View Code
At this point the code looks more, if you need n multiple pivot transformations, the code is too numerous.
At this point, you can substitute a variety of code by defining a variable in the form of a spelling string:
1 Declare @str varchar(8000) 2 Set @str = "'3 Select @str = @str + 'SELECT" "+NAME+ " "As type,* from Table_1 pivot (SUM ('+NAME+') 4 For p in ([1],[2],[3],[4])) as'+NAME+'Union All' 5 fromsyscolumns6 where object_id('Table_1')=Id andNAME<> 'P' andNAME<> 'ID'7 Select @str = Left(@str,Len(@str)-Len('Union All'))8 Select @str ='Select ID, type,sum ([1]) [1],sum ([2]) [2],sum ([3]) [3],sum ([4]) [4] FROM ('+ @str +') T1 GROUP by Id,type ORDER by Id,type'9 exec(@str)
View Code
The result of both approaches is the same, except that the latter code is more concise.
optimization of SQL Server multiple row and column transpose