通过合并Sql中的行来格式化枢轴结果
通过合并Sql中的行来格式化枢轴结果
提问于 2017-04-11 11:07:40
背景： 想一想一个应用程序，它允许人们用定制的问题进行调查，在特定的情况下，面试家庭，面试官会去
House 1面试Member 1和Member 2的两个成员。他问的问题就像。这个房子的地址是什么?你的名字和年龄是多少？对于成员来说，答案是常见的，而对成员特定的答案则存储在同一个表中。
在对一些表进行一些连接并对结果进行旋转之后，我最终得到了下面的表结构。
到目前为止取得的成就
| ID | ADDRESS | MEMBER | AGE | SubformIteration |
|----|---------|----------|--------|-------------------|
| 1 | HOUSE 1 | (null) | (null) | (null) |
| 1 | (null) | MEMBER h | 18 | s0 |
| 1 | (null) | MEMBER i | 19 | s1 |
| 2 | HOUSE 2 | (null) | (null) | (null) |
| 2 | (null) | MEMBER x | 36 | s0 |
| 2 | (null) | MEMBER y | 35 | s1 |
| 3 | HOUSE 3 | (null) | (null) | (null) |
| 3 | (null) | MEMBER a | 18 | s0 |
| 3 | (null) | MEMBER b | 19 | s1 |我正试图找到一种方法，将表格式化如下：
期望输出
| ID | ADDRESS | MEMBER | AGE | SubformIteration |
|----|---------|----------|--------|-------------------|
| 1 | HOUSE 1 | MEMBER 1 | 18 | s0 |
| 1 | HOUSE 1 | MEMBER 2 | 19 | s1 |
| 2 | HOUSE 2 | MEMBER x | 36 | s0 |
| 2 | HOUSE 2 | MEMBER y | 35 | s1 |
| 3 | HOUSE 3 | MEMBER a | 18 | s0 |
| 3 | HOUSE 3 | MEMBER b | 19 | s1 |我没有足够的sql词汇表来描述和搜索所需的操作/过程，因为我对SQL很陌生，如果有人能告诉我实现这一目标的有效方法，我将非常感激。
重要
不要依赖于QuestionText列，因为当某人决定更改问题时，它将发生变化。
编辑
源表
使用以下所有表的Sql篡改链接
根据答案中的建议，我张贴源表和查询，希望能更好地理解这个问题。
Questions表
+------------+--------------+---------+----------+---------------+
| QuestionID | QuestionText | type | SurveyID | IsIncremental |
+------------+--------------+---------+----------+---------------+
| 3483 | subform | subform | 311 | 1 |
| 3484 | MEMBER | text | 311 | 0 |
| 3485 | AGE | number | 311 | 0 |
| 3486 | ADDRESS | address | 311 | 0 |
+------------+--------------+---------+----------+---------------+Results表
+----------+-------------------------+----------+
| ResultID | DateSubmitted | SurveyID |
+----------+-------------------------+----------+
| 2272 | 2017-04-12 05:11:41.477 | 311 |
| 2273 | 2017-04-12 05:12:22.227 | 311 |
| 2274 | 2017-04-12 05:13:02.227 | 311 |
+----------+-------------------------+----------+Chunks表，其中存储所有答案：
+---------+------------+----------+------------+------------------+
| ChunkID | Answer | ResultID | QuestionID | SubFormIteration |
+---------+------------+----------+------------+------------------+
| 9606 | HOUSE 1 | 2272 | 3486 | NULL |
| 9607 | MEMEBER 1 | 2272 | 3484 | NULL |
| 9608 | 12 | 2272 | 3485 | NULL |
| 9609 | MEMBER 2 | 2272 | 3484 | s1 |
| 9610 | 10 | 2272 | 3485 | s1 |
| 9611 | MEMEBER 1 | 2272 | 3484 | s0 |
| 9612 | 12 | 2272 | 3485 | s0 |
| 9613 | MEMBER 2 | 2272 | 3484 | s1 |
| 9614 | 10 | 2272 | 3485 | s1 |
| 9615 | HOUSE 2 | 2273 | 3486 | NULL |
| 9616 | MEMBER A | 2273 | 3484 | NULL |
| 9617 | 23 | 2273 | 3485 | NULL |
| 9618 | MEMBER B | 2273 | 3484 | s1 |
| 9619 | 25 | 2273 | 3485 | s1 |
| 9620 | MEMBER A | 2273 | 3484 | s0 |
| 9621 | 23 | 2273 | 3485 | s0 |
| 9622 | MEMBER B | 2273 | 3484 | s1 |
| 9623 | 25 | 2273 | 3485 | s1 |
| 9624 | HOUSE 3 | 2274 | 3486 | NULL |
| 9625 | MEMBER K | 2274 | 3484 | NULL |
| 9626 | 41 | 2274 | 3485 | NULL |
| 9627 | MEMBER J | 2274 | 3484 | s1 |
| 9628 | 26 | 2274 | 3485 | s1 |
| 9629 | MEMBER K | 2274 | 3484 | s0 |
| 9630 | 41 | 2274 | 3485 | s0 |
| 9631 | MEMBER J | 2274 | 3484 | s1 |
| 9632 | 26 | 2274 | 3485 | s1 |
+---------+------------+----------+------------+------------------+我编写了以下存储过程，生成了这个问题中给出的第一个表：
ALTER PROCEDURE [dbo].[ResultForSurvey] @SurveyID int
AS
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),@colsAggregated as nvarchar(max);
IF OBJECT_ID('tempdb.dbo.#Temp', 'U') IS NOT NULL
DROP TABLE #Temp;
SELECT *
INTO #Temp
FROM (Select Answer=( case
When Questions.type='checkboxes' or Questions.IsIncremental=1 THEN STUFF((SELECT distinct ',' + c.Answer
FROM Chunks c Where c.ResultID=Results.ResultID and c.QuestionID=Questions.QuestionID and (Chunks.SubFormIteration IS NULL )
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
else Chunks.Answer end),Chunks.SubFormIteration,Questions.QuestionText,Questions.type,Questions.QuestionID,Chunks.ResultID,Results.ResultID as Action,Results.DateSubmitted,Results.Username,Results.SurveyID from Chunks Join Questions on Questions.QuestionID= Chunks.QuestionID Join Results on Results.ResultID=Chunks.ResultID Where Results.SurveyID=@SurveyID) as X
SET @colsAggregated = STUFF((SELECT distinct ','+ 'max('+ QUOTENAME(c.QuestionText)+') as '+ QUOTENAME(c.QuestionText)
FROM #Temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
print @colsAggregated
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.QuestionText)
FROM #Temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ResultID,max(Username) as Username,max(DateSubmitted) as DateSubmitted,max(SubFormIteration) as SubFormIteration, ' + @colsAggregated + ' from
(
select *
from #Temp
) as y
pivot
(
max(Answer)
for QuestionText in (' + @cols + ')
) as p GROUP BY
ResultID,SubFormIteration'
execute(@query)Stack Overflow用户
发布于 2017-04-21 11:40:41
据我所知:您想动态地这样做。为此，您需要准备问题文本并运行它。
专栏正在准备中。然后与查询合并。
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)
SELECT @Columns = 'C.ResultId' +
(
SELECT
',' +
CASE WHEN COL.QuestionText = 'ADDRESS' THEN 'MAX(AA.Answer)' + COL.QuestionText
ELSE 'MAX(CASE WHEN Q.QuestionText = ''' + COL.QuestionText + ''' THEN C.Answer ELSE '''' END) AS ' + COL.QuestionText END
FROM
#Questions COL
WHERE
COL.QuestionText != 'subform'
FOR XML PATH ('')
) +
',MAX(C.SubFormIteration) AS SubFormIteration'
SET @Query = '
SELECT ' +
@Columns +
' FROM
#Chunks C INNER JOIN
#Results R ON C.ResultId = R.ResultId INNER JOIN
#Questions Q ON Q.QuestionId = C.QuestionId INNER JOIN
(
SELECT
IC.ResultId,
MAX(IC.Answer) AS Answer
FROM
#Chunks IC INNER JOIN
#Results IR ON IC.ResultId = IR.ResultId INNER JOIN
#Questions IQ ON IQ.QuestionId = IC.QuestionId
WHERE
IQ.QuestionText = ''ADDRESS''
GROUP BY
IC.ResultId
) AA ON C.ResultId = AA.ResultId
WHERE
C.SubFormIteration IS NOT NULL
GROUP BY
C.ResultId,
C.SubFormIteration
'
--SELECT @Query
EXEC sp_executesql @Query输出：
ResultId MEMBER AGE ADDRESS SubFormIteration
----------- ----------- ---- ------------ --------------------
2272 MEMBER 1 12 WhiteHouse s0
2272 MEMBER 2 10 WhiteHouse s1
2273 MEMBER A 23 RpBhavan s0
2273 MEMBER B 25 RpBhavan s1评论：
对列"ResultId“和"SubFormIteration”进行分组，结果是。但是分组操作是不正确的，因为地址信息如下所示。查询和结果如下。
ResultId MEMBER AGE ADDRESS SubFormIteration
----------- -------------------------------------------------------
2272 MEMBER 1 12 WhiteHouse NULL -- Which value you want to group. s1 or s0
2272 MEMBER 1 12 s0
2272 MEMBER 2 10 s1
2273 RpBhavan NULL -- Which value you want to group. s1 or s0
2273 MEMBER A 23 s0
2273 MEMBER B 25 查询：
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)
SELECT @Columns = 'C.ResultId' +
(
SELECT
',' +
'MAX(CASE WHEN Q.QuestionText = ''' + COL.QuestionText + ''' THEN C.Answer ELSE '''' END) AS ' + COL.QuestionText
FROM
#Questions COL
WHERE
COL.QuestionText != 'subform'
FOR XML PATH ('')
) +
',MAX(C.SubFormIteration
) AS SubFormIteration'
SET @Query = '
SELECT ' +
@Columns +
' FROM
#Chunks C INNER JOIN
#Results R ON C.ResultId = R.ResultId INNER JOIN
#Questions Q ON Q.QuestionId = C.QuestionId
GROUP BY
C.ResultId,
C.SubFormIteration
'
--SELECT @Query
EXEC sp_executesql @Query页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43344204
复制相关文章
相似问题
腾讯云开发者
