在SQL Server中动态地将多行组合到多个列

我有一个大数据库表,我需要使用Microsoft sql Server动态执行下面的操作.

从这样的结果：

badge   |   name   |   Job   |   KDA   |   Match 
 - - - - - - - - - - - - - - - -
 T996    |  Darrien |   AP    |   3.0   |   20
 T996    |  Darrien |   ADC   |   2.8   |   16
 T996    |  Darrien |   TOP   |   5.0   |   120

对于使用sql的结果：

badge   |   name   |  AP_KDA | AP_Match | ADC_KDA | ADC_Match | TOP_KDA | TOP_Match 
- - - - - - - - -
T996    |  Darrien |   3.0   |   20     |  2.8    |   16      |   5.0   |  120

即使有30行,它也将组合成一列60列.

我目前可以通过硬编码(见下面的例子)来做,但不能动态地执行.

Select badge,name,(
 SELECT max(KDA)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'AP')
) AP_KDA,(
 SELECT max(Match)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'AP')
) AP_Match,(
 SELECT max(KDA)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'ADC')
) ADC_KDA,(
 SELECT max(Match)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'ADC')
) ADC_Match,(
 SELECT max(KDA)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'TOP')
) TOP_KDA,(
 SELECT max(Match)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'TOP')
) TOP_Match
from table h

我需要一个MSsql语句,允许我将多行组合成一行.第3列(作业)内容将与第4列和第5列(KDA和Match)结合,并成为新的列.

因此,如果Job有6个不同的值(例如Job1到Job6),则结果将有12列,例如：按照徽章和名称分组的Job1_KDA,Job1_Match,Job2_KDA,Job2_Match等.

我需要一个可以通过列3数据循环的语句,所以我不需要硬编码(重复每个可能的Job值的查询)或使用临时表.

解决方法

我会使用动态的sql,但这是( http://sqlfiddle.com/#!6/a63a6/1/0)PIVOT解决方案：

SELECT badge,[AP_KDa],[AP_Match],[ADC_KDA],[ADC_Match],[TOP_KDA],[TOP_Match] FROM
(
SELECT badge,col,val FROM(
 SELECT *,Job+'_KDA' as Col,KDA as Val FROM @T 
 UNION
 SELECT *,Job+'_Match' as Col,Match as Val  FROM @T
) t
) tt
PIVOT ( max(val) for Col in ([AP_KDa],[TOP_Match]) ) AS pvt

奖金：PIVOT如何与动态sql(http://sqlfiddle.com/#!6/a63a6/7/0)结合使用,再次,我更愿意在没有PIVOT的情况下更简单,但这对我来说只是很好的锻炼：

SELECT badge,cast(Job+'_KDA' as nvarchar(128)) as Col,KDA as Val INTO #Temp1 FROM Temp 
INSERT INTO #Temp1 SELECT badge,Match as Val FROM Temp

DECLARE @columns nvarchar(max)
SELECT @columns = COALESCE(@columns + ',','') + Col FROM #Temp1 GROUP BY Col

DECLARE @sql nvarchar(max) = 'SELECT badge,'+@columns+' FROM #Temp1 PIVOT ( max(val) for Col in ('+@columns+') ) AS pvt'
exec (@sql)

DROP TABLE #Temp1

在SQL Server中动态地将多行组合到多个列

解决方法

猜你在找的MsSQL相关文章