问通过合并Sql中的行来格式化枢轴结果

EN

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, '04-12-2017', 311),
(2274, '04-12-2017', 311)

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

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

; 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

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

| ID | ADDRESS |
|----|---------|
|  1 | HOUSE 1 |    

| ID |  MEMBER  |    AGE | MEMBERNO |
|----|----------|--------|----------|
|  1 | MEMBER 1 |     18 |        1 |
|  1 | MEMBER 2 |     19 |        2 |

SELECT ID, ADDRESS FROM YourTable WHERE ADDRESS IS NOT NULL

SELECT MEMBER, AGE, MEMBERNO WHERE MEMBER IS NOT NULL AND AGE IS NOT NULL AND MEMBERNO IS NOT NULL

SELECT * FROM
  (SELECT ID, ADDRESS FROM YourTable WHERE ADDRESS IS NOT NULL) AS A
  INNER JOIN
  (SELECT MEMBER, AGE, MEMBERNO WHERE MEMBER IS NOT NULL AND AGE IS NOT NULL AND MEMBERNO IS NOT NULL) AS B
  ON A.ID = B.ID