Pivot Query Help

  • I have data in this format and i need to pivot it. 


    create table #temp(ID int, YDate int, DE varchar(200), KeyValue varchar(200), ReasonType varchar(200), ReasonText varchar(200))
    insert into #temp
    values (1, 20170101, 'A', 'CDN', 'Reason Text 1', null),
       (1, 20170101, 'B', 'CDN', 'Reason Text 1', null),
         (1, 20170101, 'C', 'CDN', 'Reason Text 2', null),
         (2, 20170101, 'A', 'USA', 'Reason Text 1', 'Other 1'),
       (2, 20170101, 'B', 'USA', 'Reason Text 2', 'Other 2'),
         (2, 20170101, 'C', 'USA', 'Reason Text 3', 'Other 3'),
         (3, 20170101, 'A', 'RSA', 'Reason Text 2', null),
       (3, 20170101, 'B', 'RSA', 'Reason Text 2', null),
         (3, 20170101, 'C', 'RSA', 'Reason Text 2', null)

    and my expected result set will be like this.


    select ID, YDate, A_KeyValue, A_ReasonType, A_ReasonText, B_KeyValue, B_ReasonType, B_ReasonText, C_KeyValue, C_ReasonType, C_ReasonText

    I really appreciate if any one able to help

    Thanks 

  • inayatkhan - Monday, June 12, 2017 7:48 AM

    I have data in this format and i need to pivot it. 


    create table #temp(ID int, YDate int, DE varchar(200), KeyValue varchar(200), ReasonType varchar(200), ReasonText varchar(200))
    insert into #temp
    values (1, 20170101, 'A', 'CDN', 'Reason Text 1', null),
       (1, 20170101, 'B', 'CDN', 'Reason Text 1', null),
         (1, 20170101, 'C', 'CDN', 'Reason Text 2', null),
         (2, 20170101, 'A', 'USA', 'Reason Text 1', 'Other 1'),
       (2, 20170101, 'B', 'USA', 'Reason Text 2', 'Other 2'),
         (2, 20170101, 'C', 'USA', 'Reason Text 3', 'Other 3'),
         (3, 20170101, 'A', 'RSA', 'Reason Text 2', null),
       (3, 20170101, 'B', 'RSA', 'Reason Text 2', null),
         (3, 20170101, 'C', 'RSA', 'Reason Text 2', null)

    and my expected result set will be like this.


    select ID, YDate, A_KeyValue, A_ReasonType, A_ReasonText, B_KeyValue, B_ReasonType, B_ReasonText, C_KeyValue, C_ReasonType, C_ReasonText

    I really appreciate if any one able to help

    Thanks 

    Will you always have A, B & C? Or will the values vary?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Different values

  • Try this...

    IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
      DROP TABLE #temp;

    CREATE TABLE #temp (
      ID INT,
      YDate INT,
      DE VARCHAR(200),
      KeyValue VARCHAR(200),
      ReasonType VARCHAR(200),
      ReasonText VARCHAR(200)
        );
    INSERT INTO #temp
    VALUES
        (1, 20170101, 'A', 'CDN', 'Reason Text 1', NULL),
      (1, 20170101, 'B', 'CDN', 'Reason Text 1', NULL),
      (1, 20170101, 'C', 'CDN', 'Reason Text 2', NULL),
      (2, 20170101, 'A', 'USA', 'Reason Text 1', 'Other 1'),
      (2, 20170101, 'B', 'USA', 'Reason Text 2', 'Other 2'),
      (2, 20170101, 'C', 'USA', 'Reason Text 3', 'Other 3'),
      (3, 20170101, 'A', 'RSA', 'Reason Text 2', NULL),
      (3, 20170101, 'B', 'RSA', 'Reason Text 2', NULL),
      (3, 20170101, 'C', 'RSA', 'Reason Text 2', NULL);

    SELECT
        t.ID,
        t.YDate,

        A_KeyValue = MAX(CASE WHEN t.DE = 'A' THEN t.KeyValue END),
        A_ReasonType = MAX(CASE WHEN t.DE = 'A' THEN t.ReasonType END),
        A_ResonText = MAX(CASE WHEN t.DE = 'A' THEN t.ReasonText END),

        B_KeyValue = MAX(CASE WHEN t.DE = 'B' THEN t.KeyValue END),
        B_ReasonType = MAX(CASE WHEN t.DE = 'B' THEN t.ReasonType END),
        B_ResonText = MAX(CASE WHEN t.DE = 'B' THEN t.ReasonText END),

        C_KeyValue = MAX(CASE WHEN t.DE = 'C' THEN t.KeyValue END),
        C_ReasonType = MAX(CASE WHEN t.DE = 'C' THEN t.ReasonType END),
        C_ResonText = MAX(CASE WHEN t.DE = 'C' THEN t.ReasonText END)

        -- continue pattern as needed...
    FROM
        #temp t
    GROUP BY
        t.ID,
        t.YDate;

  • Issue is DE not always "A","B","C"

  • inayatkhan - Monday, June 12, 2017 10:01 AM

    Issue is DE not always "A","B","C"

    ok....so please provide sample data that gives us ALL the possibilities.....
    will you only EVER require to return three "sets" or will there be times when you require more ?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • If you need the dynamic version, try the following...

    IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
      DROP TABLE #temp;

    CREATE TABLE #temp (
      ID INT,
      YDate INT,
      DE VARCHAR(200),
      KeyValue VARCHAR(200),
      ReasonType VARCHAR(200),
      ReasonText VARCHAR(200)
        );
    INSERT INTO #temp
    VALUES
      (1, 20170101, 'A', 'CDN', 'Reason Text 1', NULL),
      (1, 20170101, 'B', 'CDN', 'Reason Text 1', NULL),
      (1, 20170101, 'C', 'CDN', 'Reason Text 2', NULL),
      (2, 20170101, 'A', 'USA', 'Reason Text 1', 'Other 1'),
      (2, 20170101, 'B', 'USA', 'Reason Text 2', 'Other 2'),
      (2, 20170101, 'C', 'USA', 'Reason Text 3', 'Other 3'),
      (3, 20170101, 'A', 'RSA', 'Reason Text 2', NULL),
      (3, 20170101, 'B', 'RSA', 'Reason Text 2', NULL),
      (3, 20170101, 'C', 'RSA', 'Reason Text 2', NULL);

    --=======================================================

    DECLARE
        @PivotSQL NVARCHAR(MAX) = N'',
        @DeBug BIT = 1;        --<<< set to 0 to execute, set to 1 to print debug code...

    SELECT
        @PivotSQL = CONCAT(@PivotSQL, N',
        ', t.DE, N'_KeyValue = MAX(CASE WHEN t.DE = ''', t.DE, N''' THEN t.KeyValue END),
        ', t.DE, N'_ReasonType = MAX(CASE WHEN t.DE = ''', t.DE, N''' THEN t.ReasonType END),
        ', t.DE, N'_ResonText = MAX(CASE WHEN t.DE = ''', t.DE, N''' THEN t.ReasonText END)')
    FROM (
            SELECT t.DE FROM #temp t GROUP BY t.DE
        ) t
    ORDER BY
        t.DE;

    SET @PivotSQL = CONCAT(N'
    SELECT
        t.ID,
        t.YDate,',
    STUFF(@PivotSQL, 1, 2, ''), N'
    FROM
        #temp t
    GROUP BY
        t.ID,
        t.YDate;'
    )

    IF @DeBug = 1
    BEGIN
        PRINT(@PivotSQL);
    END;
    ELSE
    BEGIN
        EXEC sys.sp_executesql @PivotSQL;
    END;

Viewing 7 posts - 1 through 6 (of 6 total)

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