我有三个表格-第一个表格包含具有唯一ID的问题
QID | Question
------------------
1 | Question 1
2 | Question 2
3 | Question 3
4 | Question 4
5 | Question 5
第二个包含这些问题的答案,第一个表中的唯一ID,然后是将答案链接在一起的记录的ID
QID | Answer | Parent
-------------------------
1 | Yes | 123
2 | No | 123
3 | No | 123
4 | Yes | 123
5 | Yes | 123
1 | No | 124
2 | Yes | 124
3 | No | 124
4 | No | 124
5 | No | 124
第三个包含有关父级的其他信息
Parent | Parent Title
----------------------------------------
123 | Questionnaire Sample 1
124 | Questionnaire Sample 2
理想情况下,我想创建一个将数据转置或旋转为更可用格式的视图,我在考虑以下方面:
Parent | Question 1 | Question 2 | Question 3 | Question 4 | Question 5
123 | Yes | No | No | Yes | Yes
124 | No | Yes | No | No | No
我在这里工作过的几个非常好的线程:
还有另一个:
我从以下方面拉了以下东西
SELECT [First_Set], [Second_Set]
FROM
(
SELECT B.ColumnName, A.Value
, row_number() over(partition by a.Table2ID
order by a.Value) seq
FROM Table_1 AS A
INNER JOIN Table_2 AS B
ON A.Table2ID = B.ID
) AS P
PIVOT
(
min(P.Value)
for P.ColumnName in ([First_Set], [Second_Set])
) AS PIV;
然后,我将其修改为:
SELECT [PARENT], [QUESTION], [ANSWER]
FROM
(
SELECT B.PARENT_TITLE, C.QUESTION, A.ANSWER
, row_number() over(partition by a.PARENT
order by a.PARENT) seq
FROM answerTable AS A
INNER JOIN parentTable AS B
ON A.PARENT = B.PARENT
INNER JOIN questionTable AS C
ON A.QID = C.QID
) AS P
PIVOT
(
min(P.RESULT)
for P.PARENT in ([PARENT], [QUESTION], [ANSWER])
) AS PIV;
哪个返回但仍然不是我需要的。
对我而言,创建新表是不可行的,因此理想情况下,我正在这里寻找一种动态手段,这是为了进行报告,因此我认为视图最简单/最佳,但我愿意接受建议。
谢谢。
以下是动态执行操作的方法:
create table #t1(QID int, Question char(10))
insert #t1 values
(1, 'Question 1'),
(2, 'Question 2'),
(3, 'Question 3'),
(4, 'Question 4'),
(5, 'Question 5')
create table #t2 (QID int, Answer char(3), Parent int)
insert #t2 values
(1, 'Yes', 123),
(2, 'No ', 123),
(3, 'No ', 123),
(4, 'Yes', 123),
(5, 'Yes', 123),
(1, 'No ', 124),
(2, 'Yes', 124),
(3, 'No ', 124),
(4, 'No ', 124),
(5, 'No ', 124)
declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(Question)
FROM #t1 -- your table here
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @collist
declare @q nvarchar(max)
set @q = '
select *
from (
select
Question, Answer, Parent
from (
select #t1.*, #t2.Answer, #t2.parent
from #t1
inner join #t2 on #t1.QID = #t2.QID
) as x
) as source
pivot (
max(Answer)
for Question in (' + @collist + ')
) as pvt
'
exec (@q)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句