用于将行转换为列的SQL查询 [英] sql query to convert row into column

问题描述

我有一张图表如下图所示

i have a table with schema like below image

id     empcode      question      answer     date
1        011        ur name?      abcd         20/10/2013
2        011        ur lastname?  abcd         20/10/2013
3        011        ur number?    abcd         20/10/2013
4        011        ur email?     abcd         20/10/2013
5        011        ur name?      xyz          21/10/2013
6        011        ur lastname?  xyz          21/10/2013
7        011        ur number?    xyz          21/10/2013
8        011        ur email?     xyz          21/10/2013

i需要选择查询表，例如

i need select query table like

    date    empcode       ur name?     ur lastname?      ur number?  ur email?
 20/10/2013       011           abcd          abcd             abcd        abcd
21/10/2013        011            xyz           xyz             xyz        xyz

推荐答案

SELECT *

FROM

（

SELECT [question_id]，[date]，[answer]

来自dsr_ques_ans，其中empcode ='EMP-820485-2'

）AS来源

PIVOT

（

MAX（[答案]）

FOR [question_id] IN（[1]，[2]，[3]，[4]）

）as pvt ;





选择日期，

MAX（CASE WHEN question_id ='1'那么回答ELSE NULL END ）[1]，

MAX（例如，当question_id ='2'然后回答ELSE NULL END）[2]，

MAX（CASE WHEN question_id ='3'那么回答ELSE NULL END）[3]，

MAX（CASE WHEN question_id ='4'然后回答ELSE NULL END）[4]

FROM dsr_ques_ans

GROUP BY日期





得到我的解决方案

SELECT *
FROM
(
SELECT [question_id], [date], [answer]
FROM dsr_ques_ans where empcode='EMP-820485-2'
) AS source
PIVOT
(
MAX([answer])
FOR [question_id] IN ([1], [2], [3],[4])
) as pvt;


SELECT date,
MAX(CASE WHEN question_id= '1' THEN answer ELSE NULL END) [1],
MAX(CASE WHEN question_id= '2' THEN answer ELSE NULL END) [2],
MAX(CASE WHEN question_id= '3' THEN answer ELSE NULL END) [3],
MAX(CASE WHEN question_id= '4' THEN answer ELSE NULL END) [4]
FROM dsr_ques_ans
GROUP BY date


got my solution

使用PIVOT - UNPIVOT



使用PIVOT和UNPIVOT [ ^ ]

在SQL Server中透视数据 [ ^ ]（替代方式）

Use PIVOT - UNPIVOT

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

这篇关于用于将行转换为列的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！