如何在SQL中将行转换为列 [英] How to convert rows to columns in SQL
本文介绍了如何在SQL中将行转换为列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张如下表格
Hi,
I have a table like below
CANDIDATEID SUB SUBID MARK TOTAL_QUESTIONS
41 IT 1 6 14
41 MATH 2 3 10
41 ENGLISH READING 3 4 10
41 ENGLISH GRAMMER 4 3 15
42 IT 1 3 14
42 MATH 2 2 10
我想像excel一样转置它
I want to transpose it like excel
CANDIDATEID IT_MARKS MATH_MARKS ER_MARKS EG_MARKS T_QNS_IT T_QNS_MATH T_QNS_ER T_QNS_EG
41 6 3 4 3 14 10 10 15
42 3 2 NULL NULL 14 10 10 15
请帮帮我怎样才能做到这一点。我在查询方面不是很好
我有什么尝试过:
我尝试使用SQL旋转,但没有工作
Please help me how can a do this.I am not very good in Querying
What I have tried:
I have tried with SQL pivoting,but not worked
推荐答案
请参阅以下内容:
使用PIVOT和UNPIVOT | Microsoft Docs [ ^ ]
Sql Server中的PIVOT和UNPIVOT - 15到20分钟 [ ^ ]
See the following:
Using PIVOT and UNPIVOT | Microsoft Docs[^]
PIVOT and UNPIVOT in Sql Server – 15 to 20 minutes[^]
CREATE TABLE #temp (
CANDIDATEID int, SUB varchar(500), UBID INT,MARK INT,TOTAL_QUESTIONS int)
INSERT INTO #TEMP
values
(41,'IT',1,6,14),
(41,'MATH',2,3,10),
(41,'ENGLISH READING',3,4,10),
(41,'ENGLISH GRAMMER',4,3,15),
(42,'INFORMATION TECHNOLOGY',1,3,14),
(42,'MATH',2,2,10);
WITH CTE AS (
SELECT CANDIDATEID,CASE WHEN SUB IN ('INFORMATION TECHNOLOGY','IT') THEN 'IT' ELSE SUB END AS SUB,MARK FROM #TEMP
UNION
SELECT CANDIDATEID,'T_QNS_'+CASE WHEN SUB IN ('INFORMATION TECHNOLOGY','IT') THEN 'IT' ELSE SUB END AS SUB,TOTAL_QUESTIONS FROM #TEMP
)
select CANDIDATEID,[IT],[MATH],[ENGLISH READING], [ENGLISH GRAMMER],
[T_QNS_IT],[T_QNS_MATH],[T_QNS_ENGLISH GRAMMER],[T_QNS_ENGLISH READING]
from CTE
PIVOT
( MAX(MARK) FOR SUB IN (
[ENGLISH GRAMMER],[ENGLISH READING],[INFORMATION TECHNOLOGY],[IT],[MATH],
[T_QNS_ENGLISH GRAMMER],[T_QNS_ENGLISH READING],[T_QNS_INFORMATION TECHNOLOGY],[T_QNS_IT],[T_QNS_MATH]
)
) AS ff
这篇关于如何在SQL中将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文