重新格式化SQL输出

Question

我有如下所示的数据

Name  XX YY
alpha 10 77
beta  10 90
alpha 20 72
beta  20 91
alpha 30 75
beta  30 94
alpha 40 76
beta  40 95

如果我使用

select * from scores order by Name, XX

我会得到

Name  XX YY
alpha 10 77
alpha 20 72
alpha 30 75
alpha 40 76
beta  10 90
beta  20 91
beta  30 94
beta  40 95

目前，我正在检索此表单中的数据，并使用一些C代码将其格式化为

Name   xx=10  xx=20  xx=30  xx=40
alpha  77     72     75     76
beta   90     91     94     95

假设每个名字总是有10、20、30、40个条目，这在SQL中是可能的，而不需要像在SQL Reformatting table columns中那样创建一个新表。

Answer 1

你需要使用PIVOT来获得你想要的结果。在使用PIVOT之前，需要对列XX中的值进行一些自定义，以便最终的列输出可以满足您的期望。

SELECT * FROM
(
SELECT Name,'XX='+CAST(XX AS VARCHAR) XX,YY 
FROM your_table
)AS P
PIVOT(
    SUM(YY)
    FOR XX IN ([XX=10],[XX=20],[XX=30],[XX=40])
) PP

输出-

Name    XX=10   XX=20   XX=30   XX=40
alpha   77      72      75      76
beta    90      91      94      95

使用以下查询也可以获得相同的输出：

SELECT Name,
[10] AS [XX=10],
[20] AS [XX=20],
[30] AS [XX=30],
[40] AS [XX=40]
FROM
(
    SELECT Name, XX,YY 
    FROM your_table
)AS P
PIVOT(
    SUM(YY)
    FOR XX IN ([10],[20],[30],[40])
) PP

Answer 2

您可以使用dynamic PIVOT来实现相同的结果，这将更加健壮，

CREATE TABLE #table1 (Name varchar(100), XX INT, YY INT)

INSERT INTO #table1 VALUES
('alpha',10,77),
('beta',10,90),
('alpha',20,72),
('beta',20,91),
('alpha',30,75),
('beta',30,94),
('alpha',40,76),
('beta',40,95)

DECLARE @pvt NVARCHAR(MAX) = '';
DECLARE @dynamicCol NVARCHAR(MAX) = '';

SELECT @pvt +=  ', ' +QUOTENAME([XX]) FROM #table1 GROUP BY [XX];
SELECT @dynamicCol +=  ', ' +QUOTENAME([XX]) + ' AS ' + QUOTENAME('XX=' + CAST([XX] AS VARCHAR(25))) FROM #table1 GROUP BY [XX];
SET @pvt = STUFF(@pvt,1,1,'')
SET @dynamicCol = STUFF(@dynamicCol,1,1,'')

EXEC ('
SELECT [Name],' + @dynamicCol+'
FROM #table1 a
PIVOT 
(
  SUM([YY])
  FOR [XX] IN ('+ @pvt+')
) PIV');

结果如下：

Name    XX=10   XX=20   XX=30   XX=40
alpha   77      72      75      76
beta    90      91      94      95

Answer 3

使用case的另一个解决方案

SELECT Name
  ,SUM(CASE when XX = '10' then YY else 0 END) AS 'xx=10'
  ,SUM( CASE when XX = '20' then YY else 0 END) AS 'xx=20'
  ,SUM( CASE when XX = '30' then YY else 0 END) AS 'xx=30'
  ,SUM( CASE when XX = '40' then YY else 0 END) AS 'xx=40'
  FROM temp_0
  group by Name