以一行转列如下表结构PERAPERBDSAB32AC54AD42....要求转后为如下显示(所谓的B_D就是PERB的值加D列的值)PER
以一行转列
如下表结构
PERAPERBDS
AB32
AC54
AD42
....
要求转后为如下显示(所谓的B_D就是PERB的值加D列的值)
PERAB_DB_SC_DC_S...
A3254...
请不要提供
- SQL code
declare @s nvarchar(4000)Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in('+@s+'))b ')这类,上面要求的不同，相同于一科有两个值，一个是期中试，一个是期末试的分数。
[解决办法]
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([PERA] VARCHAR(1),[PERB] VARCHAR(1),[D] INT,[S] INT)INSERT [tb]SELECT 'A','B',3,2 UNION ALLSELECT 'A','C',5,4 UNION ALLSELECT 'A','D',4,2--------------开始查询--------------------------DECLARE @s VARCHAR(MAX)SET @s = ''SELECT @s = @s + ',[' + LTRIM([PERB]) + '_D]=max(case when [PERB]=' + QUOTENAME([PERB], '''') + ' then [D] else 0 end)'FROM [tb]GROUP BY [PERB]SELECT @s = @s + ',[' + LTRIM([PERB]) + '_S]=max(case when [PERB]=' + QUOTENAME([PERB], '''') + ' then [S] else 0 end)'FROM [tb]GROUP BY [PERB]SET @s = 'select [PERA]' + @s + ' from [tb] group by [PERA]'EXEC(@s)/*PERA B_D C_D D_D B_S C_S D_S---- ----------- ----------- ----------- ----------- ----------- -----------A 3 5 4 2 4 2(1 行受影响)a*/