如何在SQL中将行转换为列 [英] How to convert rows to columns in 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屋！