如何将列中的重复值更改为行，然后用相应的值填充相应的行值

Question

我有一张这样的桌子：

​

​

ID  ATTR_NAME   SUB_ATTR_NAME   VALUE
1   ATTR-1        SUB-ATTR-1    23
2   ATTR-1        SUB-ATTR-2    32
3   ATTR-1        SUB-ATTR-3    25
4   ATTR-1        SUB-ATTR-4    28
5   ATTR-2        SUB-ATTR-1    78
6   ATTR-2        SUB-ATTR-2    45
7   ATTR-2        SUB-ATTR-3    48
8   ATTR-2        SUB-ATTR-4    41
9   ATTR-3        SUB-ATTR-1    47
10  ATTR-3        SUB-ATTR-2    12
11  ATTR-3        SUB-ATTR-3    16
12  ATTR-3        SUB-ATTR-4    18

但是使用SQL时，我想要这样一个表：

​

​

            SUB-ATTR-1  SUB-ATTR-2  SUB-ATTR-3  SUB-ATTR-4
ATTR-1      23          32          25          28
ATTR-2      78          45          48          41
ATTR-3      47          12          16          18

请帮帮忙，我是SQL的新手

Answer 1

样本数据

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
CREATE TABLE #Temp(ID INT, ATTR_NAME VARCHAR(20),  SUB_ATTR_NAME VARCHAR(20),VALUE INT)
INSERT INTO #Temp

SELECT 1 ,'ATTR-1','SUB-ATTR-1', 23 UNION ALL
SELECT 2 ,'ATTR-1','SUB-ATTR-2', 32 UNION ALL
SELECT 3 ,'ATTR-1','SUB-ATTR-3', 25 UNION ALL
SELECT 4 ,'ATTR-1','SUB-ATTR-4', 28 UNION ALL
SELECT 5 ,'ATTR-2','SUB-ATTR-1', 78 UNION ALL
SELECT 6 ,'ATTR-2','SUB-ATTR-2', 45 UNION ALL
SELECT 7 ,'ATTR-2','SUB-ATTR-3', 48 UNION ALL
SELECT 8 ,'ATTR-2','SUB-ATTR-4', 41 UNION ALL
SELECT 9 ,'ATTR-3','SUB-ATTR-1', 47 UNION ALL
SELECT 10,'ATTR-3','SUB-ATTR-2', 12 UNION ALL
SELECT 11,'ATTR-3','SUB-ATTR-3', 16 UNION ALL
SELECT 12,'ATTR-3','SUB-ATTR-4', 18
SELECT * FROM #Temp

使用动态Sql

DECLARE @Sql nvarchar(max),
        @Col nvarchar(max),
        @Col2 nvarchar(max)

SELECT @Col=STUFF((SELECT DISTINCT ', '+QUOTENAME(SUB_ATTR_NAME)  FROM #Temp
FOR XML PATH ('')),1,1,'')
SELECT @Col2=STUFF((SELECT DISTINCT ', '+'MAX( '+QUOTENAME(SUB_ATTR_NAME)+' ) AS'+QUOTENAME(SUB_ATTR_NAME)   FROM #Temp
FOR XML PATH ('')),1,1,'')

SET @Sql='
SELECT ATTR_NAME,'+@Col2+' FROM
(
SELECT * FROM #Temp 
)AS SRC
PIVOT
(
SUM(VALUE) FOR SUB_ATTR_NAME IN ('+@Col+')
)AS PVT
GROUP BY ATTR_NAME'
PRINT @Sql
EXEC (@Sql)

结果

ATTR_NAME   SUB-ATTR-1  SUB-ATTR-2  SUB-ATTR-3  SUB-ATTR-4
----------------------------------------------------------
ATTR-1          23          32          25          28
ATTR-2          78          45          48          41
ATTR-3          47          12          16          18

演示:http://rextester.com/SURJ9296

Answer 2

您需要按ATTR_NAME对表进行分组，然后才能获得SUB_ATTR的总和

select attr_name ,
sum( case CASE  
     WHEN  SUB_ATTR_NAME = 'SUB-ATTR-1' THEN VALUE
     else 0 end) SUB-ATTR-1,
sum( case CASE  
     WHEN  SUB_ATTR_NAME = 'SUB-ATTR-2' THEN VALUE
     else 0 end) SUB-ATTR-2,
sum( case CASE  
     WHEN  SUB_ATTR_NAME = 'SUB-ATTR-3' THEN VALUE
     else 0 end) SUB-ATTR-3,
sum( case CASE  
     WHEN  SUB_ATTR_NAME = 'SUB-ATTR-4' THEN VALUE
     else 0 end) SUB-ATTR-4
END   sum(value)
from TABLE
group by attr_name