Bring row value to columns

Question

Ive got this table below,

Branch     Item No    Month    Qty_Sold
  A          XX        JAN        5
  A          XX        FEB       13   
  A          YY        JAN       11
  A          YY        FEB       37
  B          XX        JAN        8
  B          XX        FEB       16
  B          XX        MAR        3
  B          YY        FEB       20
  B          YY        MAR       19
  B          ZZ        MAR       31

what i want it to look like is

Branch     Item No     JAN      FEB      MAR
  A          XX          5       13        -
  A          YY         11       37        -
  B          XX          8       16        3
  B          YY          -       20       19
  B          ZZ          -        -       31

how do I do this...

any ideas?

thanks

Have you tried anything so far? Maybe pivot or sum with condition? — Andrey Korneyev, Feb 25 '15 at 11:09
https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx — Gordon Linoff, Feb 25 '15 at 11:11
I didnt really think about that tbh. we've only got JAN FEB MAR at the minute. But yeah, true.. you are correct we'd get APR and MAY ans so on. Probably those as columns too alongside JAN FEB MAR — Sinnerv, Feb 25 '15 at 11:20
possible duplicate of [MSSQL dynamic pivot column values to column header](http://stackoverflow.com/questions/19688697/mssql-dynamic-pivot-column-values-to-column-header) — Tab Alleman, Feb 25 '15 at 13:57

score 0 · Answer 1 · edited May 23 '17 at 10:24

You can take a look at this answer: Using PIVOT in SQL Server 2008

and this one : MSSQL dynamic pivot column values to column header

Take inspiration from MSDN: PIVOT

And you'd probably come to that:

-- Temporary table...
create table ##myTable (
        Branch      varchar(5)
        ,ItemNo     varchar(10)
        ,Mth        varchar(3)
        ,Qty_Sold   int
        )

-- ... with sample data
insert into ##myTable
        select  'A',          'XX',        'JAN',        5
union   select  'A',          'XX',        'FEB',       13   
union   select  'A',          'YY',        'JAN',       11
union   select  'A',          'YY',        'FEB',       37
union   select  'B',          'XX',        'JAN',        8
union   select  'B',          'XX',        'FEB',       16
union   select  'B',          'XX',        'MAR',        3
union   select  'B',          'YY',        'FEB',       20
union   select  'B',          'YY',        'MAR',       19
union   select  'B',          'ZZ',        'MAR',       31

-- So we have :
DECLARE @cols  AS NVARCHAR(MAX)
        ,@query AS NVARCHAR(MAX)

select @cols = STUFF((
                        SELECT  DISTINCT
                                ',' + QUOTENAME(Mth) 
                        FROM    ##myTable
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                    ,1,1,'')

set @query = '
    SELECT  Branch, ItemNo, ' + @cols + ' 
    FROM    (
              select Branch, ItemNo, Mth, Qty_Sold
              from ##myTable
            ) x
            pivot 
            (
                Sum(Qty_Sold)
                for Mth in (' + @cols + ')
            ) p'

execute sp_executesql @query

-- Remove temporary table
drop table ##myTable

score 0 · Accepted Answer · answered Feb 25 '15 at 11:30

Try this

DECLARE @Mytable TABLE(Branch char(1),ItemNo char(2),Month char(3),Qty_Sold int)

insert into @Mytable

values 
('A','XX','JAN','5'),
('A','XX','FEB','13'),
('A','YY','JAN','11'),
('A','YY','FEB','37'),
('B','XX','JAN','8'),
('B','XX','FEB','16'),
('B','XX','MAR','3'),
('B','YY','FEB','20'),
('B','YY','MAR','19'),
('B','ZZ','MAR','31')

SELECT *FROM @MYTABLE
PIVOT(MAX(QTY_SOLD) FOR MONTH IN ([JAN],[FEB],[MAR]))T
ORDER BY Branch

Bring row value to columns

2 Answers2