sql 行转列 例子

一.
在行转列时，拼结多个问题的答案，放到一个列中显示

Create FUNCTION [dbo].[AggregateString]
(
@func_code varchar(20),
@emp_id int,
@store_id int,
@BIZ_DATE varchar(20),
@acvt_id int,
@qst_id int ---问题id

)
RETURNS varchar(1024)
AS
BEGIN
declare @Str varchar(1024)

select @Str = isnull(@Str+',','')+val
from v_visit_acvt_for_fac
where qst_id = @qst_id
and func_code =@func_code
AND BIZ_DATE =@BIZ_DATE
AND STORE_ID =@store_id
and acvt_id=@acvt_id
and emp_id =@emp_id

return @Str
END


GO


二.行转列，关键是有一个固定的id值作为转运的id,如果没有这样的固定id,可以考虑用虚拟的列。生成固定的id值。
WITH acvt_qst
AS
(
SELECT maq.ACVT_ID,
maq.QST_ID,
mq.qst_name,
row_number() OVER(PARTITION BY maq.ACVT_ID ORDER BY maq.qst_id) rownum
FROM MS_ACVT_QST maq

INNER JOIN MS_QST mq
ON mq.id = maq.QST_ID
WHERE mq.qst_name <> '拍照' --AND maq.ACVT_ID = 18

)

SELECT v.emp_id,
v.STORE_ID,
v.BIZ_DATE,
v.IMG_ID,
v.FUNC_CODE,
v.acvt_id,

max(CASE WHEN qcq.rownum = 1 AND
v.qst_id = qcq.qst_id
THEN qcq.qst_name
ELSE NULL
END ) AS question1,

max(CASE WHEN qcq.rownum = 1 AND
v.qst_id = qcq.qst_id
THEN dbo.AggregateString(func_code,emp_id,store_id,biz_date,v.acvt_id,v.qst_id)
ELSE NULL
END ) AS answer1,

max(CASE WHEN qcq.rownum = 2 AND
v.qst_id = qcq.qst_id
THEN qcq.qst_name
ELSE NULL
END ) AS question2,

max(CASE WHEN qcq.rownum = 2 AND
v.qst_id = qcq.qst_id
THEN dbo.AggregateString(func_code,emp_id,store_id,biz_date,v.acvt_id,v.qst_id)
ELSE NULL
END ) AS answer2,

max(CASE WHEN qcq.rownum = 3 AND
v.qst_id = qcq.qst_id
THEN qcq.qst_name
ELSE NULL
END ) AS question3,

max(CASE WHEN qcq.rownum = 3 AND
v.qst_id = qcq.qst_id
THEN dbo.AggregateString(func_code,emp_id,store_id,biz_date,v.acvt_id,v.qst_id)
ELSE NULL
END ) AS answer3,

max(CASE WHEN qcq.rownum = 4 AND
v.qst_id = qcq.qst_id
THEN qcq.qst_name
ELSE NULL
END ) AS question4,

max(CASE WHEN qcq.rownum = 4 AND
v.qst_id = qcq.qst_id
THEN dbo.AggregateString(func_code,emp_id,store_id,biz_date,v.acvt_id,v.qst_id)
ELSE NULL
END ) AS answer4

FROM v_visit_acvt_temp v
INNER JOIN acvt_qst qcq
ON qcq.acvt_id = v.acvt_id

WHERE BIZ_DATE ='2012-03-01' AND func_code ='FAC_40'--AND STORE_ID =55140
GROUP BY v.emp_id,
v.STORE_ID,
v.BIZ_DATE,
v.IMG_ID,
v.FUNC_CODE,
v.acvt_id