Question Formater un résultat de pivot en fusionnant des lignes dans Sql


L'arrière-plan:

Pensez à une application qui permet aux gens de faire des enquêtes avec des questions personnalisées. Dans un cas particulier, interrogez les familles,   l'intervieweur va à House 1 et interviewe deux membres Member 1   et Member 2. Il pose des questions comme. Quelle est cette maison   adresse?,Quel est ton nom et ton âge?. Les réponses pour   c'est commun pour les membres et les réponses spécifiques pour   ils sont stockés dans la même table

Après avoir fait quelques jointures sur certaines tables et pivoté le résultat, je finis par obtenir la structure de table suivante.

Ce qui a été réalisé jusqu'à présent

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

J'essaie de trouver un moyen pour que le tableau soit formaté comme suit:

Sortie désirée

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

Je n'ai pas assez de vocabulaire SQL pour décrire et rechercher les opérations / procédures requises. Comme je suis nouveau dans SQL et que je serais vraiment reconnaissant si quelqu'un pouvait me dire un moyen efficace d'y parvenir.

Important

Ne vous fiez pas à la QuestionText colonne car il y aura des changements Quand quelqu'un a décidé de changer les questions

modifier

Tables source

Sql lien de violon avec toutes les tables ci-dessous

Conformément aux suggestions dans les réponses, je publie la table source et les requêtes dans l’espoir qu’il y aura une meilleure compréhension du problème.

Questions table

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

+----------+-------------------------+----------+
| 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 table, où toutes les réponses sont stockées:

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

J'ai écrit la procédure stockée suivante qui donne la première table donnée dans cette question:

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)

13
2018-04-11 11:07


origine


Réponses:


Il peut être avantageux de publier la requête qui vous a fourni vos résultats originaux; il est possible que la requête d'origine soit réécrite pour éviter cette complexité. Avec l'information donnée, c'est le moyen le plus simple de résoudre ce problème:

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

Mettre à jour:

Voici un exemple simple de la structure de la table utilisant des tables temporaires:

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)

Voici un moyen de produire les résultats que vous recherchez sans utiliser les PIVOT et 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

Essentiellement, j'ai cassé une requête plutôt grande dans une expression de table commune (CTE). Chaque requête avait un but: a) La réponse tire toutes les réponses sauf l'adresse, b) Ne tire que l'adresse basée sur ResultId, et c) Combine les deux requêtes ensemble.

Le MAX (CASE ...) suivi de GROUP BY est une méthode alternative à l'utilisation de PIVOTS et ils fonctionnent essentiellement de la même manière.

Pour appliquer cette requête à votre cas spécifique, vous devez uniquement modifier le nom des tables.


4
2018-04-11 18:30



Pour autant que je sache: vous voulez le faire dynamiquement. Pour cela, vous devez préparer le texte de la question et l'exécuter.

Les colonnes sont en préparation. Puis fusionné avec la requête.

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

Sortie:

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

Pour commentaire:

Les colonnes "ResultId" et "SubFormIteration" sont regroupées et le résultat est. Mais l'opération de regroupement est incorrecte car les informations d'adresse ressemblent à ceci. La requête et le résultat sont ci-dessous.

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                           

Question:

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

3
2018-04-21 11:40



Si le tableau ci-dessus est le résultat de plusieurs jointures / etc, il serait préférable de donner une recommandation basée sur le schéma actuel disponible. Toutefois, si la table exemple du lien SQL Fiddle est tout ce dont vous avez besoin, essayez ce qui suit:

SELECT h.address, p.member, p.age, p.memberno
FROM House h
     INNER JOIN
       House p 
       ON h.id = p.id
         AND h.member IS NULL
         AND p.member IS NOT NULL

2
2018-04-11 11:36



Nous ne pouvons pas voir votre requête d'entrée, mais je suppose que vous obtenez ces colonnes nulles à cause d'un LEFT ou RIGHT rejoignez votre requête source. Si vous pouviez diviser votre résultat verticalement en deux vues comme ceci:

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

et

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

et ensuite les rejoindre sur ID domaine, vous obtiendrez précisément le résultat dont vous avez besoin.

modifier

Après avoir examiné votre modification, voici comment appliquer la méthode ci-dessus dans votre scénario:

Première requête:

SELECT ID, ADDRESS FROM YourTable WHERE ADDRESS IS NOT NULL

Deuxième requête:

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

Maintenant, rejoignez-les ensemble ID:

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

2
2018-04-11 11:15



En regardant de votre réponse, je pense que vous avez besoin du résultat de CROSS JOIN de deux table

Vous pouvez utiliser cette requête:

SELECT * from table1,table2

Cela vous aidera ...


1
2018-04-11 11:18