Konvertieren Zeile Daten-Spalte in SQL Server

Heute wurde ich gefragt von meiner Kollegin zum transformieren von Daten von einer vertikalen staging-Tabelle in eine horizontale Tabelle. Ich meine, verwandeln Zeilen auf der Spalte. Ich benutzte PIVOT und gelöst. Bekam aber in situation, wo ich bin immer ärger um Daten zu verschieben, wenn die Daten Feld wiederholt sich.

Hier ist der test-Daten, an denen ich arbeite:

CREATE TABLE STAGING 
(
    ENTITYID INT,
    PROPERTYNAME VARCHAR(25),
    PROPERTYVALUE VARCHAR(25)
)

INSERT INTO STAGING VALUES (1, 'NAME', 'DONNA')
INSERT INTO STAGING VALUES (1, 'SPOUSE', 'HENRY')
INSERT INTO STAGING VALUES (1, 'CHILD', 'JACK')
INSERT INTO STAGING VALUES (2, 'CHILD', 'KAYALA')

Ich verwendet PIVOT auf Zeile anzeigen Daten als Spalten:

SELECT * FROM 
(SELECT ENTITYID, PROPERTYNAME, PROPERTYVALUE FROM STAGING) AS T
PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN (NAME, SPOUSE, CHILD)) AS T2

Ausgabe:

ENTITYID    NAME    SPOUSE  CHILD
1           DONNA   HENRY   JACK
2           NULL    NULL    KAYALA

Aber er wollte die Ausgabe etwas wie:

ENTITYID    NAME    SPOUSE  CHILD   CHILD
1           DONNA   HENRY   JACK    KAYALA

Quintessenz ist, dass es kann mehr als ein CHILD-Attribut kommt in die staging-Tabelle. Und wir müssen dies berücksichtigen und verschieben Sie alle KINDER zu Spalten.

Ist das möglich?

Woher wissen Sie, dass Kayala gehört zu Donna und Henry? Oder ist das Beispiel falsch, dass Kayala hätte eine Entity-ID 1 auch?
Eigentlich ist es eine unterstützende Tabelle, entityid und parententityid Beziehung. Aber, ich denke, um dies zu vereinfachen, kann ich die gleiche Person id.

InformationsquelleAutor Sri Reddy | 2013-06-12

Können Sie fügen Sie eine Zeile Anzahl der propertyname, die Ihnen erlauben, das zu tun, was Sie wollen:

SELECT * FROM
(
SELECT ENTITYID
       , PROPERTYNAME = PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID, PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5))
      ,PROPERTYVALUE
FROM #STAGING   
) AS T
PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN (NAME1, SPOUSE1, CHILD1, CHILD2, CHILD3, CHILD4, CHILD5)) AS T2

Gehe ich davon aus, dass hier die ENTITYID Bindungen der Kinder an die Eltern, d.h. alle Kinder für die gleiche person haben ENTITYID 1, aber dein Beispiel zeigt einen 2-für Kayala.

Hier ist eine Demo: SQL Fiddle

Wenn Sie möchten, dass nur die zahlen für das UNTERGEORDNETE Felder, die Sie setzen könnte:

PROPERTYNAME = CASE WHEN PROPERTYNAME LIKE '%CHILD%' THEN PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID, PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5))                                                   ELSE PROPERTYNAME END

Dann entfernen Sie die Nummer aus, die anderen Felder in der IN () - Anweisung.

Bonus-Frage, die oben dynamisch:
Wir wollen nicht davon ausgehen, dass Menschen nur ein Ehepartner oder 2.3 Kinder, also machen wir das ganze etwas dynamisch:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + PROPERTYNAME
                    FROM (SELECT DISTINCT PROPERTYNAME = PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID, PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5))
                          FROM STAGING )sub
                    ORDER BY CASE WHEN PROPERTYNAME LIKE '%NAME%' THEN 1
                        WHEN PROPERTYNAME LIKE '%SPOUSE%' THEN 2
                        WHEN PROPERTYNAME LIKE '%CHILD%' THEN 3
                    ELSE 4
                    END
                    ,RIGHT(PROPERTYNAME,1) 
                  FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


SET @query = 'SELECT * FROM
                (
                SELECT ENTITYID, PROPERTYNAME = PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID, PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5)),PROPERTYVALUE
                FROM STAGING   
                ) AS T
                PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN ('+@cols+')) AS T2

'
EXEC(@query)

Hinweis: Die Bestellung funktioniert nur für Ehegatten 1-9 und Kinder 1-9, dass man einstellen kann, um den Anzug, aber es Willkür sowieso.

Dies funktioniert, wenn wir wissen, wie viele childs. Aber was passiert, wenn wir nicht wissen, die Grenze 🙂
Das fühlt sich wie eine bonus-Frage... aber ich werde Höhle.
Aktualisiert, um die dynamischen version.
🙂 Vielen Dank, das funktioniert. Da hätte ich klar mit meiner Frage.

InformationsquelleAutor Hart CO

wenn Sie nicht brauchen, um zu zeigen, Person id schau auf deine Frage richtig es ist, dass Sie wollen Ausgang:-

ENTITYID    NAME    SPOUSE  CHILD   CHILD
1           DONNA   HENRY   JACK    KAYALA(This one is entityId=2)

so falsch ist, dann auch Sie wollen Ergebnis wie dieses:

  NAME    SPOUSE  CHILD   CHILD
  DONNA   HENRY   JACK    KAYALA

SELECT * FROM  (SELECT  PROPERTYNAME, PROPERTYVALUE FROM STAGING) AS T PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN (NAME, SPOUSE, CHILD)) AS T2

InformationsquelleAutor Rahul Ganga

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.