CODEWITHSUNDEEP
sql-serverpivot
user2210516
user2210516

Reputation: 683

Turn Date into Column with Pivot

Im not good at using Pivot but i think that's the only way to solve my Problem.

I have this SQL

SELECT DISTINCT ADR_Adressen.AdressNrADR
, LEFT(ADR_Adressen.Name, 3) AS Name
, LEFT(ADR_Adressen.Vorname, 3) AS Vorname
, CRM_Aufgaben.TerminVon
, LAG_Artikel.ArtikelNrLAG
, CRM_AufgabenLink.MitNrPRO
FROM  ADR_Adressen
    INNER JOIN PRO_Auftraege ON ADR_Adressen.AdressNrADR = PRO_Auftraege.Kunde
    INNER JOIN CRM_Aufgaben ON PRO_Auftraege.AuftragNrPRO = CRM_Aufgaben.AuftragNrPRO
    INNER JOIN CRM_Status ON CRM_Aufgaben.StatusCRM = CRM_Status.StatusCRM
    INNER JOIN LAG_Artikel ON CRM_Aufgaben.ArtikelNrLAG = LAG_Artikel.ArtikelNrLAG
    INNER JOIN ADR_GruppenLink ON ADR_Adressen.AdressNrADR = ADR_GruppenLink.AdressNrADR
    INNER JOIN ADR_Gruppen ON ADR_GruppenLink.GruppeADR = ADR_Gruppen.GruppeADR
    INNER JOIN CRM_AufgabenLink ON CRM_Aufgaben.AufgabenNrCRM = CRM_AufgabenLink.AufgabenNrCRM
WHERE  { d '2016-03-07'} <= CRM_Aufgaben.TerminVon
   AND { d '2016-03-11'} + 1 >= CRM_Aufgaben.TerminBis
   AND CRM_AufgabenLink.MitNrPRO != 0
   AND ADR_Gruppen.GruppeADR IN ( 'KIND' )

This is my result:

enter image description here

My wish is to get a Output like this: enter image description here

The different Dates in TerminVon has to be Columns with the Values from ArtikelNrLAG+MitNrPRO. If the same AdressNrADR has more then one TerminVon on the same Date i have to make more rows. (Example where Name = Boc,Alt)

Can someone help me please =)

Upvotes: 0

Views: 53

Answers (2)

JamieD77
JamieD77

Reputation: 13949

To PIVOT what you have, you can use a query similar to this.

SELECT  AdressNrADR,  
        Name,
        Vorname,
        [2016-03-07],
        [2016-03-08],
        [2016-03-09],
        [2016-03-10],
        [2016-03-11]
FROM (
    SELECT DISTINCT
            ADR_Adressen.AdressNrADR,
            LEFT(ADR_Adressen.Name,3) AS Name,
            LEFT(ADR_Adressen.Vorname,3) AS Vorname,
            CONVERT(VARCHAR(10), CRM_Aufgaben.TerminVon, 120) AS TerminVon, -- Convert date to yyyy-mm-dd format
            LAG_Artikel.ArtikelNrLAG + '+' + CRM_AufgabenLink.MitNrPRO AS [Value], -- Combine column values
            ROW_NUMBER() OVER 
                (PARTITION BY AdressNrADR, 
                              LEFT(ADR_Adressen.Name,3), 
                              LEFT(ADR_Adressen.Vorname,3), 
                              CAST(CRM_Aufgaben.TerminVon AS DATE) 
                        ORDER BY CRM_Aufgaben.TerminVon) Rn -- So we can get 1 row per time value
    FROM    ADR_Adressen
            INNER JOIN PRO_Auftraege ON ADR_Adressen.AdressNrADR = PRO_Auftraege.Kunde
            INNER JOIN CRM_Aufgaben ON PRO_Auftraege.AuftragNrPRO = CRM_Aufgaben.AuftragNrPRO
            INNER JOIN CRM_Status ON CRM_Aufgaben.StatusCRM = CRM_Status.StatusCRM
            INNER JOIN LAG_Artikel ON CRM_Aufgaben.ArtikelNrLAG = LAG_Artikel.ArtikelNrLAG
            INNER JOIN ADR_GruppenLink ON ADR_Adressen.AdressNrADR = ADR_GruppenLink.AdressNrADR
            INNER JOIN ADR_Gruppen ON ADR_GruppenLink.GruppeADR = ADR_Gruppen.GruppeADR
            INNER JOIN CRM_AufgabenLink ON CRM_Aufgaben.AufgabenNrCRM = CRM_AufgabenLink.AufgabenNrCRM
    WHERE   { d '2016-03-07'} <= CRM_Aufgaben.TerminVon
            AND { d '2016-03-11'} + 1 >= CRM_Aufgaben.TerminBis
            AND CRM_AufgabenLink.MitNrPRO != 0
            AND ADR_Gruppen.GruppeADR IN ('KIND') 
) t
PIVOT (
    MAX([Value]) 
    FOR TerminVon IN ([2016-03-07],[2016-03-08],[2016-03-09],[2016-03-10],[2016-03-11])
) p

If you get that query to work. Your next step would be to make it Dynamic.

Upvotes: 2

Steve Salowitz
Steve Salowitz

Reputation: 1335

The difficult part of using t-sql's pivot functionality is that the output column names have to be hard coded. In your example we would need to know the value of each date and use that in the query in order to get the matching values by date. Fortunately other fine developers have experienced this frustration for us and have created scripts that will generate a dynamic pivot. I have included two links that will help you on your way.

https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

Upvotes: 0

Related Questions