Need help in writing query to show data from rows into columns

  • Hi All,

    I'm new to SQL , I'm facing issue in converting data from rows into columns ,Can some one please help in getting the results as shown in the following query.

    Any help would be highly appreciated

    CREATE TABLE #temp

    (

    STORE_ID INT

    ,PRINTER_NM VARCHAR(50)

    ,PRIORITY_IND INT

    ,PRINTER_ID INT

    )

    INSERT INTO #temp

    values(1000,'PRINT1',1,100)

    ,(1000,'PRINT2',2,101)

    ,(1001,'PRINT1',1,102)

    ,(1002,'PRINT2',2,103)

    ,(1002,'PRINT1',1,104)

    ,(1002,'PRINT2',2,105)

    ,(1003,'PRINT1',1,106)

    ,(1003,'PRINT2',2,107)

    SELECT 1000 AS 'STORE_ID'

    ,'PRINT1' AS PRIMARYPRINTER_NM

    , 100 AS PRIMARYPRINTER_ID

    ,'PRINT2' AS SECONDARYPRINTER_NM

    ,101 AS SECONDARYPRINTER_ID

    union All

    SELECT 1001 AS 'STORE_ID'

    ,'PRINT1' AS PRIMARYPRINTER_NM

    , 102 AS PRIMARYPRINTER_ID

    ,'PRINT2' AS SECONDARYPRINTER_NM

    ,103 AS SECONDARYPRINTER_ID

    drop table #temp

  • Quick suggestion

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

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

    CREATE TABLE #temp

    (

    STORE_ID INT

    ,PRINTER_NM VARCHAR(50)

    ,PRIORITY_IND INT

    ,PRINTER_ID INT

    )

    INSERT INTO #temp

    values(1000,'PRINT1',1,100)

    ,(1000,'PRINT2',2,101)

    ,(1001,'PRINT1',1,102)

    ,(1002,'PRINT2',2,103)

    ,(1002,'PRINT1',1,104)

    ,(1002,'PRINT2',2,105)

    ,(1003,'PRINT1',1,106)

    ,(1003,'PRINT2',2,107);

    SELECT

    T.STORE_ID

    ,MAX(CASE WHEN T.PRIORITY_IND = 1 THEN T.PRINTER_NM END) AS PRIMARYPRINTER_NM

    ,MAX(CASE WHEN T.PRIORITY_IND = 1 THEN T.PRINTER_ID END) AS PRIMARYPRINTER_ID

    ,MAX(CASE WHEN T.PRIORITY_IND = 2 THEN T.PRINTER_NM END) AS SECONDARYPRINTER_NM

    ,MAX(CASE WHEN T.PRIORITY_IND = 2 THEN T.PRINTER_ID END) AS SECONDARYPRINTER_ID

    FROM #temp T

    GROUP BY T.STORE_ID;

    Output

    STORE_ID PRIMARYPRINTER_NM PRIMARYPRINTER_ID SECONDARYPRINTER_NM SECONDARYPRINTER_ID

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

    1000 PRINT1 100 PRINT2 101

    1001 PRINT1 102 NULL NULL

    1002 PRINT1 104 PRINT2 105

    1003 PRINT1 106 PRINT2 107

  • Thank you very much Eirikur Eiriksson , your Quick suggestion made my day . you deserve a round of applause

  • ramineni.suresh661 (6/2/2016)


    Thank you very much Eirikur Eiriksson , your Quick suggestion made my day . you deserve a round of applause

    The question now is... do you understand how it works and why? Do you know what the technique is called? If not, please see the following article.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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