sql-server – 通过在Sql中合并行来格式化枢轴结果

The background:

Think of an application that lets people make surveys with custom questions,In a particular case,interview families,An
interviewer goes to House 1 and interviews two members Member 1
and Member 2. He asks questions like. What is this house
address?,What is your name and age?. The answers for
that is common for the Members and the answers that are specific for
them are stored in the same table

在对某些表进行一些连接之后,转动结果,我最终得到以下表格结构.

迄今取得的成就

| 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 fiddle link with all the below tables

根据答案中的建议,我发布源表和查询,希望对问题有更好的了解

问题表

+------------+--------------+---------+----------+---------------+
| 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 |
+------------+--------------+---------+----------+---------------+

结果表

+----------+-------------------------+----------+
| 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,'')
         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(''),'')
        print @colsAggregated
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.QuestionText) 
            FROM #Temp c
            FOR XML PATH(''),'')
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)

解决方法

SELECT
   h1.Id,h2.Address,h1.Member,h1.Age,h1.MemberNo
FROM House h1
  INNER JOIN House h2
  ON h1.Id = h2.Id
WHERE h2.Address IS NOT NULL  -- Eliminates the results whre the Address is NULL after the join
  AND h1.Member IS NOT NULL -- Eliminates the results that would show up from the original table (t1) where there is no Member field

更新：

下面是使用临时表的表结构的简单示例：

DROP TABLE #Questions
DROP TABLE #Results
DROP TABLE #Chunks

CREATE TABLE #Questions
(
  QuestionId INT,QuestionText VARCHAR(MAX),type VARCHAR(MAX),SurveyID INT,IsIncremental INT
)

CREATE TABLE #Results
(
    ResultId INT,DateSubmitted DATETIME,SurveyID INT
)

CREATE TABLE #Chunks
(
    ChunkId INT,Answer VARCHAR(MAX),ResultId INT,QuestionId INT,SubFormIteration VARCHAR(20)
)

INSERT INTO #Results
VALUES (2272,'04-12-2017',311),(2273,(2274,311)

INSERT INTO #Chunks
VALUES (9606,'WhiteHouse',2272,3486,NULL),(9607,'MEMBER 1',3484,(9608,'12',3485,(9609,'MEMBER 2','s1'),(9610,'10',(9611,'s0'),(9612,(9613,(9614,(9615,'RpBhavan',2273,(9618,'MEMBER B',(9619,'25',(9620,'MEMBER A',(9621,'23','s0')

INSERT INTO #Questions
VALUES (3483,'subform',311,1),( 3484,'MEMBER','text',0 ),(3485,'AGE','number',0),(3486,'ADDRESS','address',0)

这是一种在不使用PIVOT和XML的情况下生成您的查找结果的方式：

; WITH Responses AS (
SELECT
    c.ResultId,QuestionText,Answer,c.SubFormIteration
FROM #Chunks c
    INNER JOIN #Results r
    ON c.ResultId = r.ResultId
    INNER JOIN #Questions q
    ON q.QuestionId = c.QuestionId
WHERE c.SubFormIteration IS NOT NULL -- Removes the "Address" responses and duplicate Answers
),FindAddress AS (
-- Pulls ONLY the address for each ResultId
SELECT
    ResultId,MAX(CASE WHEN QuestionText = 'ADDRESS' THEN Answer END) AS [Address]
FROM #Chunks c
    INNER JOIN #Questions q
    ON q.QuestionId = c.QuestionId
GROUP BY ResultId
)
-- Combines all responses and the address back together
SELECT
    r.ResultId,fa.Address,MAX(CASE WHEN QuestionText = 'MEMBER' THEN Answer END) AS [MEMBER],MAX(CASE WHEN QuestionText = 'AGE' THEN Answer END) AS [Age],SubFormIteration
FROM Responses r
    INNER JOIN FindAddress fa
    ON fa.ResultId = r.ResultId
GROUP BY r.ResultId,SubFormIteration,fa.Address

本质上,我将一个相当大的查询打破了一个通用表表达式(CTE).每个查询有一个目的：a)响应提取除地址之外的所有响应,b)仅拉取基于ResultId的地址,c)将两个查询合并在一起.

MAX(CASE …)后跟GROUP BY是使用PIVOTS的替代方法,它们基本上执行相同.

要将此查询应用于特定的情况,您只需要更改表的名称.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!