求助一个行列转化
数据如下：
Organization Province P93 P97
社会单位101 7.03 7.66
中石化 101 7.08 7.67
中石油 101 7.11 7.67
社会单位102 7.03 7.66
中石化 102 7.08 7.67
中石油 102 7.11 7.67
经行转列，数据变化成：
Province 社会单位P93 中石化P93 中石油P93 社会单位P97 中石化P97 中石油P97
101 7.03 7.08 7.11 7.66 7.67 7.67
102 7.03 7.08 7.11 7.66 7.67 7.67
[解决办法]
--测试数据create table testTable(Organization nvarchar(10),Province varchar(5),P93 float,P97 float)insert into testTableselect '社会单位','101',7.03,7.66 union allselect '中石化','101',7.08,7.67 union allselect '中石油','101',7.11,7.67 union allselect '社会单位','102',7.03,7.66 union allselect '中石化','102',7.08,7.67 union allselect '中石油','102',7.11,7.67--方法一,静态SQL语句select Province,max(case Organization when '社会单位' then P93 else 0 end) 社会单位P93,max(case Organization when '中石化' then P93 else 0 end) 中石化P93,max(case Organization when '中石油' then P93 else 0 end) 中石油P93,max(case Organization when '社会单位' then P97 else 0 end) 中石化P97,max(case Organization when '中石化' then P97 else 0 end) 中石化P97,max(case Organization when '中石油' then P97 else 0 end) 中石化P97from @table group by Province--方法二,动态SQLdeclare @sqlStr varchar(8000) set @sqlStr = 'select Province ' select @sqlStr = @sqlStr + ' , max(case Organization when ''' + Organization + ''' then P93 else 0 end) [' + Organization +'P93'+ ']' from (select distinct Organization from testTable) as a select @sqlStr = @sqlStr + ' , max(case Organization when ''' + Organization + ''' then P97 else 0 end) [' + Organization +'P97'+ ']'from (select distinct Organization from testTable) as a set @sqlStr = @sqlStr + ' from testTable group by Province' exec(@sqlStr)--结果Province 社会单位P93 中石化P93 中石油P93 社会单位P97 中石化P97 中石油P97-------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------101 7.03 7.08 7.11 7.66 7.67 7.67102 7.03 7.08 7.11 7.66 7.67 7.67(2 行受影响)
[解决办法]
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([Organization] VARCHAR(8),[Province] INT,[P93] NUMERIC(3,2),[P97] NUMERIC(3,2))INSERT [tb]SELECT '社会单位',101,7.03,7.66 UNION ALLSELECT '中石化',101,7.08,7.67 UNION ALLSELECT '中石油',101,7.11,7.67 UNION ALLSELECT '社会单位',102,7.03,7.66 UNION ALLSELECT '中石化',102,7.08,7.67 UNION ALLSELECT '中石油',102,7.11,7.67--------------开始查询--------------------------DECLARE @s VARCHAR(max)SELECT @s=ISNULL(@s+',','')+QUOTENAME(Organization+'P93')+'=max(case when [Organization]='''+LTRIM([Organization])+''' then [P93] else 0 end)' FROM [tb]SELECT @s=ISNULL(@s+',','')+QUOTENAME(Organization+'P97')+'=max(case when [Organization]='''+LTRIM([Organization])+''' then [P97] else 0 end)' FROM [tb]GROUP BY [Organization]SELECT @s='SELECT [Province],'+@s+'FROM [tb] GROUP BY Province'--PRINT @sEXEC(@s)----------------结果----------------------------/* Province 社会单位P93 中石化P93 中石油P93 社会单位P93 中石化P93 中石油P93 社会单位P97 中石化P97 中石油P97101 7.03 7.08 7.11 7.03 7.08 7.11 7.66 7.67 7.67102 7.03 7.08 7.11 7.03 7.08 7.11 7.66 7.67 7.67*/