在sql server中使用强制转换 [英] pivot with cast in sql server

问题描述

大家好，

我有这样的样本数据

Hi all,
I have the sample data like this

student_id  metric         score
1          raw_score        20
1          scale_score       30
2          proficiency      level1
2          scale_score      60   
3          raw_score        45
3          proficiency      level2
4          scale_score       54
4          proficiency       level2

预期产量是

the expected output is

studentid raw_score scale_score proficiency
1            20         30          null
2            null        60         level1
3            45          null        level2
4             null        54         level2

提前谢谢。



问候，

Prakash

Thannks in advance.

Regards,
Prakash

推荐答案

请看一下：

Please, have a look:

CREATE TABLE #exdata (student_id  INT, metric VARCHAR(30), score VARCHAR(30))

INSERT INTO #exdata (student_id, metric, score)
SELECT 1, 'raw_score', '20'
UNION ALL SELECT 1, 'scale_score', '30'
UNION ALL SELECT 2, 'proficiency', 'level1'
UNION ALL SELECT 2, 'scale_score', '60'
UNION ALL SELECT 3, 'raw_score', '45'
UNION ALL SELECT 3, 'proficiency', 'level2'
UNION ALL SELECT 4, 'scale_score', '54'
UNION ALL SELECT 4, 'proficiency', 'level2'

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + metric
            FROM #exdata
            ORDER BY '],[' + metric
            FOR XML PATH('')),1,2,'') + ']'

--SELECT @cols As cols

SET @dt = N'SELECT *
        FROM #exdata'
--EXEC(@dt)

SET @pt = N'SELECT student_id, ' + @cols + ' ' +
            'FROM(' + @dt + ') AS DT ' +
            'PIVOT(MAX(score) FOR metric IN(' + @cols + ')) AS PT'
EXEC(@pt)

DROP TABLE #exdata

结果：

Result:

student_id	proficiency	raw_score	scale_score
1		NULL		20		30
2		level1		NULL		60
3		level2		45		NULL
4		level2		NULL		54

查看这些



使用PIVOT和UNPIVOT [ ^ ]

在SQL Server中传输数据 - （替代方式） [ ^ ]

Check these

Using PIVOT and UNPIVOT[^]
Pivoting data in SQL Server - (Alternate ways)[^]

SELECT student_id,raw_score,scale_score,proficiency FROM
(SELECT student_id,metric,score FROM Temp123)p
PIVOT(MAX(score) FOR metric IN(raw_score,scale_score,proficiency)) AS pvt

这篇关于在sql server中使用强制转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！