如何在sqlserver中动态地将行转换为列

Question

这是我的表结构

结果

id  ResultId  CategoryId   Total  Attempted  Score
----------------------------------------------------
1     8         1           30      25        20
2     8         2           30      30        19
3     8         3           30      27        21
4     7         1           20      15        10
5     7         2           20      20        15

类别

Id     CategoryName
-----------------------
1         General
2         Aptitude
3         Technical

我想要以下格式的数据对于ResultId=8

Id     General     Aptitude    Technical    Total
--------------------------------------------------
8        20          19           21          60

对于ResultId=7

Id     General     Aptitude    Total
-------------------------------------
7        10          15          25

我需要一个帮助来获取上面格式的数据。

注意：最终提取的数据包含Result表中的分数，但具有类别表中的列名，并且符合Result表的CategoryId。列名将是动态

尝试了以下代码(仅用于测试(，但未在中工作

DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)

SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(CategoryName)
FROM (  SELECT DISTINCT CategoryName 
FROM [dbo].[Category] c) AS PivotExample


SET   @SQLQuery = 
N'SELECT  DISTINCT ' +   @PivotColumns + '
FROM [dbo].[Category] c

PIVOT( SUM(c.Id) 
FOR CategoryName IN (' + @PivotColumns + ')) AS P'

EXEC sp_executesql @SQLQuery

Answer 1

我的查询将为您提供预期的输出。您可以在SQL Fiddle 中检查输出

--dynamic with case
DECLARE @Sql NVARCHAR(4000) = NULL
DECLARE @ColumnHeaders NVARCHAR(4000);
SET @ColumnHeaders = STUFF((
SELECT DISTINCT ',' + 'Max(CASE WHEN rn =' + quotename(rn, '''') + ' THEN Score  else null end ) as ' + CategoryName  + CHAR(10) + CHAR(13)
FROM (
SELECT CategoryName, row_number() OVER (ORDER BY CategoryName) rn
FROM ( SELECT DISTINCT CategoryName FROM Category) t0
) t1
FOR XML PATH('')
,TYPE
).value('.', 'varchar(max)'), 1, 1, '');
--print @ColumnHeaders
SET @sql = N' ;with cte as (
select * , Row_number() Over(Partition by ResultId  Order by ResultId ) rn from 
Result) 
Select  ResultId, ' + @ColumnHeaders + ', SUM(Score) Total from cte Group by 
ResultId ';
EXECUTE sp_executesql @sql