一.
在行转列时，拼结多个问题的答案，放到一个列中显示
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 [email protected]_code
AND BIZ_DATE [email protected]_DATE
AND STORE_ID [email protected]_id
and [email protected]_id
and emp_id [email protected]_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