Pivoting Question

  • Good morning! I am new to pivoting data and trying to figure out if it is possible to pivot our questionnaires table by client and date:

    From this:

    | Date | Client | QType | Answer |

    ===========================

    | 11.01.16 | 1 | Q1 | 100 |

    | 11.01.16 | 1 | Q2 | 150 |

    | 11.01.16 | 1 | Q3 | 75 |

    | 11.01.16 | 2 | Q1 | 250 |

    | 11.01.16 | 2 | Q2 | 100 |

    | 11.01.16 | 2 | Q3 | 100 |

    | 12.01.16 | 1 | Q1 | 75 |

    | 12.01.16 | 1 | Q2 | 50 |

    | 12.01.16 | 1 | Q3 | 75 |

    | 12.01.16 | 2 | Q1 | 10 |

    | 12.01.16 | 2 | Q2 | 50 |

    | 12.01.16 | 2 | Q3 | 100 |

    Into This:

    | Client | Date | Q1 | Q2 | Q3 |

    ===============================

    | 1 | 11.01.16 | 100 | 50 | 75 |

    | 2 | 11.01.16 | 250 | 100 | 100 |

    | 1 | 12.01.16 | 75 | 50 | 75 |

    | 2 | 12.01.16 | 10 | 50 | 100 |

    I am hoping that someone out there can help a tired non-pivoting DBA in need!

    My apologies if the solution to this is blindingly obvious, I have been working all night and this one escapes me!!!!

    Many thanks for any help!

  • Quick suggestion

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

    --

    ;WITH SAMPLE_DATA(Date,Client,QType,Answer) AS

    (SELECT Date,Client,QType,Answer

    FROM (VALUES

    ('11.01.16',1,'Q1',100)

    ,('11.01.16',1,'Q2',150)

    ,('11.01.16',1,'Q3',75 )

    ,('11.01.16',2,'Q1',250)

    ,('11.01.16',2,'Q2',100)

    ,('11.01.16',2,'Q3',100)

    ,('12.01.16',1,'Q1',75 )

    ,('12.01.16',1,'Q2',50 )

    ,('12.01.16',1,'Q3',75 )

    ,('12.01.16',2,'Q1',10 )

    ,('12.01.16',2,'Q2',50 )

    ,('12.01.16',2,'Q3',100)

    ) AS X(Date,Client,QType,Answer)

    )

    SELECT

    SD.Date

    ,SD.Client

    ,MAX(CASE WHEN SD.QType = 'Q1' THEN SD.Answer END) AS Q1

    ,MAX(CASE WHEN SD.QType = 'Q2' THEN SD.Answer END) AS Q2

    ,MAX(CASE WHEN SD.QType = 'Q3' THEN SD.Answer END) AS Q3

    FROM SAMPLE_DATA SD

    GROUP BY SD.Date

    ,SD.Client;

    Output

    Date Client Q1 Q2 Q3

    -------- ----------- ----------- ----------- -----------

    11.01.16 1 100 150 75

    12.01.16 1 75 50 75

    11.01.16 2 250 100 100

    12.01.16 2 10 50 100

  • Thank you so much, worked perfectly!

  • slr010877 (8/1/2016)


    Thank you so much, worked perfectly!

    You are very welcome

    😎

    For further information on the subject, I suggest having a look at Jeff Moden's excellent articles, Cross Tabs and Pivots[/url].

  • Eirikur Eiriksson (8/1/2016)


    slr010877 (8/1/2016)


    Thank you so much, worked perfectly!

    You are very welcome

    😎

    For further information on the subject, I suggest having a look at Jeff Moden's excellent articles, Cross Tabs and Pivots[/url].

    +1

    The syntax for a crosstab is so much easier to read than a PIVOT. 🙂

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply