SQL动态 行转列
数据库查询的数据结果如下：
要求显示如下样子：
动态显示行转列，sql 2008 数据。
在数据库，或者是代码里转都可以的，先谢谢各位了！ SQL动态?行转列??
[解决办法]
http://bbs.csdn.net/topics/240002706
[解决办法]
你这个和之前我的那个公司里的需要很相似，你只要动态生成一个语句就可以了
[解决办法]
数据图片截图不好弄，有没有文本形式的数据
[解决办法]
IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test
(
IDCHAR(6)
,NameNVARCHAR(40)
,SubNameNVARCHAR(40)
,YearONYearDECIMAL(9,2)
,LastYearScoreDECIMAL(9,2)
,ScoreDECIMAL(9,2)
,TotalScoreDECIMAL(9,2)
)
INSERT INTO #Test
SELECT 'M99101', N'M乌鲁木齐五一路店', NULL, NULL, NULL ,NULL, NULLUNION ALL
SELECT 'M89801', N'M三亚大东海鹿回头广场店(N9)', NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT 'M87102', N'M昆明环城南路双龙店', NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT 'M87101', N'M昆明世博店', NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'安全管理专项', NULL, NULL ,100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'案例八：服务意识', NULL, NULL, 0, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'案例二：入住接待', NULL ,NULL, 62.79, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'案例六：叫醒服务', NULL, NULL, 30, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'案例七：礼仪行为', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'案例三：离店结帐', NULL ,NULL, 59.26, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'案例四：早餐服务', NULL, NULL, 63.64, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'案例五：借用物品', NULL, NULL, 0, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'案例一：电话预订', NULL, NULL, 42.11, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'餐厅家具及物品', NULL, NULL, 100 ,85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'餐厅家具及物品（清洁）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'大堂设施设备', NULL, NULL ,100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'大堂设施设备（清洁）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'大堂用品和装饰', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'大堂用品和装饰（清洁）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'地面、墙面和顶面（餐厅）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'地面、墙面和顶面（餐厅清洁）', NULL ,NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'地面、墙面和顶面（大堂）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'地面、墙面和顶面（大堂清洁）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'地面、墙面和顶面（公区）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'地面、墙面和顶面（公区清洁）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'地面、墙面和顶面（客房）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'地面、墙面和顶面（客房清洁）', NULL, NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'电梯', NULL ,NULL, 100, 85.31 UNION ALL
SELECT 'M85102', N'M贵阳解放路店', N'电梯（清洁）', NULL, NULL, 100, 85.31
IF OBJECT_ID('tempdb..#ColumnNames','U') IS NOT NULL DROP TABLE #ColumnNames
CREATE TABLE #ColumnNames
(
ColumnNameCHAR(4)
,NameNVARCHAR(40)
)
INSERT INTO #ColumnNames
SELECT CAST(ROW_NUMBER() OVER(ORDER BY GETDATE()) AS VARCHAR) AS ColumnID
,SubName
FROM #Test
DECLARE @SqlA NVARCHAR(MAX),@SqlB NVARCHAR(MAX),@SqlC NVARCHAR(MAX),@SqlD NVARCHAR(MAX)
SELECT @SqlA=ISNULL(@SqlA+',','')+'['+'LastYearScore'+ColumnName+']' FROM #ColumnNames
SELECT @SqlB=ISNULL(@SqlB+',','')+'['+'Score'+ColumnName+']' FROM #ColumnNames
SELECT @SqlC=ISNULL(@SqlC+',','')+'['+'YearONYear'+ColumnName+']' FROM #ColumnNames
SELECT @SqlD=ISNULL(@SqlD+',','')+'A.'+'['+'LastYearScore'+ColumnName+']'+
','+'B.'+'['+'Score'+ColumnName+']'+
','+'C.'+'['+'YearONYear'+ColumnName+']'FROM #ColumnNames
IF OBJECT_ID('tempdb..##TotalA','U') IS NOT NULL DROP TABLE ##TotalA
SELECT @SqlA=N' SELECT ID,'+@SqlA+'
INTO ##TotalA
FROM (SELECT ID
,''LastYearScore''+CAST(ROW_NUMBER() OVER(ORDER BY GETDATE()) AS VARCHAR) AS ColumnID
,LastYearScore
FROM #Test
) AS T
PIVOT(MAX(T.LastYearScore) FOR T.ColumnID IN('+@SqlA+'))p'
EXECUTE(@SqlA)
IF OBJECT_ID('tempdb..##TotalB','U') IS NOT NULL DROP TABLE ##TotalB
SELECT @SqlB=N' SELECT ID,'+@SqlB+'
INTO ##TotalB
FROM (SELECT ID
,''Score''+CAST(ROW_NUMBER() OVER(ORDER BY GETDATE()) AS VARCHAR) AS ColumnID
,Score
FROM #Test
) AS T
PIVOT(MAX(T.Score) FOR T.ColumnID IN('+@SqlB+'))p'
EXECUTE(@SqlB)
IF OBJECT_ID('tempdb..##TotalC','U') IS NOT NULL DROP TABLE ##TotalC
SELECT @SqlC=N' SELECT ID,'+@SqlC+'
INTO ##TotalC
FROM (SELECT ID
,''YearONYear''+CAST(ROW_NUMBER() OVER(ORDER BY GETDATE()) AS VARCHAR) AS ColumnID
,YearONYear
FROM #Test
) AS T
PIVOT(MAX(T.YearONYear) FOR T.ColumnID IN('+@SqlC+'))p'
EXECUTE(@SqlC)
SELECT @SqlD='SELECT A.ID
,(SELECT TOP 1 Name FROM #Test WHERE ID=A.ID) AS Name
,'+@SqlD+'
,(SELECT SUM(TotalScore)
FROM #Test
WHERE ID=A.ID) AS TotalScore
FROM ##TotalA AS A
JOIN ##TotalB AS B ON A.ID=B.ID
JOIN ##TotalC AS C ON B.ID=C.ID'
EXECUTE(@SqlD)
DROP TABLE #Test
DROP TABLE #ColumnNames
DROP TABLE ##TotalA
DROP TABLE ##TotalB
DROP TABLE ##TotalC
我用笨办法写了个，可这么多NULL，统计有意义？