optimization of SQL Server multiple row and column transpose

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