How to convert Row to Column using Pivot?

I am trying to convert rows to column

This is my table records

Records

SrID    ProID   Year    Qty   Months
-------------------------------------
4444    112112  2019    22    THREE
4444    112112  2019    44    FOUR
4444    112112  2019    20    FIVE
2244    112112  2019    17    SIX
5555    112112  2019    16    SEVEN

4444    222111  2019    5     ONE
4444    222111  2019    4     FOUR
4444    222111  2019    55    FIVE
2244    222111  2019    20    SIX

​x
 
SrID    ProID   Year    Qty   Months-------------------------------------4444    112112  2019    22    THREE4444    112112  2019    44    FOUR4444    112112  2019    20    FIVE2244    112112  2019    17    SIX5555    112112  2019    16    SEVEN​4444    222111  2019    5     ONE4444    222111  2019    4     FOUR4444    222111  2019    55    FIVE2244    222111  2019    20    SIX​

Expected Result

SrID    ProID   One   Two    Three    Four    Five   Six   Seven
----------------------------------------------------------------
4444    112112  null  null   22       44      20     null  null 
2244    112112  null  null   null     null    null   17    null
5555    112112  null  null   null     null    null   null  16   

4444    222111  5     null   null     4       55     null  null 
2244    222111  null  null   null     null    null   20    null

 
SrID    ProID   One   Two    Three    Four    Five   Six   Seven----------------------------------------------------------------4444    112112  null  null   22       44      20     null  null 2244    112112  null  null   null     null    null   17    null5555    112112  null  null   null     null    null   null  16   ​4444    222111  5     null   null     4       55     null  null 2244    222111  null  null   null     null    null   20    null​

I tried like below

SELECT
    SrID,ProID,ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN
FROM
    Records rec
PIVOT
(
    MAX(rec.Qty)
    FOR Months IN (ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN)
)AS P

 
SELECT    SrID,ProID,ONE,TWO,THREE,FOUR,FIVE,SIX,SEVENFROM    Records recPIVOT(    MAX(rec.Qty)    FOR Months IN (ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN))AS P​

But I got the result in a wrong way, like

SrID    ProID   One   Two    Three    Four    Five   Six   Seven
----------------------------------------------------------------
4444    112112  null  null   22       null    null   null  null 
4444    112112  null  null   null     44      null   null  null 
4444    112112  null  null   null     null    20     null  null

 
SrID    ProID   One   Two    Three    Four    Five   Six   Seven----------------------------------------------------------------4444    112112  null  null   22       null    null   null  null 4444    112112  null  null   null     44      null   null  null 4444    112112  null  null   null     null    20     null  null ​

can give me solution with how it works.

Answer

Everything looks good to me:

CREATE TABLE [dbo].[myTable](
    SrID[numeric](10),    
    ProID[numeric](10),   
    Year[numeric](10),    
    Qty[numeric](10),   
    Months[varchar](20) NULL)

INSERT INTO [myTable] VALUES 
(4444,   112112, 2019 ,   22    ,'THREE'),
(4444,   112112, 2019 ,   44    ,'FOUR'),
(4444,   112112, 2019 ,   20    ,'FIVE'),
(2244,   112112, 2019 ,   17    ,'SIX'),
(5555,   112112, 2019 ,   16    ,'SEVEN'),
(4444,   222111, 2019 ,   5     ,'ONE'),
(4444,   222111, 2019 ,   4     ,'FOUR'),
(4444,   222111, 2019 ,   55    ,'FIVE'),
(2244,   222111, 2019 ,   20    ,'SIX')

--select * from [myTable]

SELECT
    SrID,ProID,ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN
    FROM [dbo].[myTable] rec
    PIVOT
    (
        MAX(rec.Qty)
        FOR Months IN (ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN)
    )AS P
order by 2,1

/*
Result:
SrID    ProID   ONE TWO THREE   FOUR    FIVE    SIX SEVEN
2244    112112  NULL    NULL    NULL    NULL    NULL    17  NULL
4444    112112  NULL    NULL    22  44  20  NULL    NULL
5555    112112  NULL    NULL    NULL    NULL    NULL    NULL    16
2244    222111  NULL    NULL    NULL    NULL    NULL    20  NULL
4444    222111  5   NULL    NULL    4   55  NULL    NULL
*/

 
CREATE TABLE [dbo].[myTable](    SrID[numeric](10),        ProID[numeric](10),       Year[numeric](10),        Qty[numeric](10),       Months[varchar](20) NULL)​INSERT INTO [myTable] VALUES (4444,   112112, 2019 ,   22    ,'THREE'),(4444,   112112, 2019 ,   44    ,'FOUR'),(4444,   112112, 2019 ,   20    ,'FIVE'),(2244,   112112, 2019 ,   17    ,'SIX'),(5555,   112112, 2019 ,   16    ,'SEVEN'),(4444,   222111, 2019 ,   5     ,'ONE'),(4444,   222111, 2019 ,   4     ,'FOUR'),(4444,   222111, 2019 ,   55    ,'FIVE'),(2244,   222111, 2019 ,   20    ,'SIX')​--select * from [myTable]​SELECT    SrID,ProID,ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN    FROM [dbo].[myTable] rec    PIVOT    (        MAX(rec.Qty)        FOR Months IN (ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN)    )AS Porder by 2,1​/*Result:SrID    ProID   ONE TWO THREE   FOUR    FIVE    SIX SEVEN2244    112112  NULL    NULL    NULL    NULL    NULL    17  NULL4444    112112  NULL    NULL    22  44  20  NULL    NULL5555    112112  NULL    NULL    NULL    NULL    NULL    NULL    162244    222111  NULL    NULL    NULL    NULL    NULL    20  NULL4444    222111  5   NULL    NULL    4   55  NULL    NULL*/​

Can you see any diference with your table/data/query?

Advertisement

Answer