SQL Server Pivot multiple columns based on rows from another table

Question

I have following two source and one destination tables in sql server 2008R2. How can I do pivot(s) in TSQL to get to the destination from sources? I need it urgently.

Source Table 1: ProdType


key     name
--------------
1       Magazines
2       Journals
3       Books
4       Newspaper

Source Table 2: Orders


seqno   ODate       Key     Qty      UnitPrice
--------------------------------------------------
1   2013-10-12      1    10       5
2   2013-10-12      4    20       3
3   2013-10-13      2    5        3
4   2013-10-14      4    50       5
5   2013-10-15      1    100      2.5

Destination Table: Orders Detail

                         
Odate       Magazine       Journals       Books       Newspaper
-----------------------------------------------------------------   
12/10/2013      10    5    0    0       0      0      20      3
13/10/2013       0    0    5    3       0      0       0      0
14/10/2013       0    0    0    0       0      0      50      5
15/10/2013     100    2.5  0    0       0      0       0      0
-----------------------------------------------------------------
NOTE            *qty    *unit price

Any help would be greatly appreciated! As you can tell, I'm quite new to T-SQL (or SQL in general) and SQL Server. Thanks in advance!

I have asked a very similar question - stackoverflow.com/questions/18732445/… — Leptonator
– Leptonator, Commented Oct 17, 2013 at 6:00

roman · Accepted Answer · 2013-10-17 08:12:14Z

1

with cte as (
    select
        O.ODate, P.Name,
        sum(O.Qty) as Qty,
        sum(O.UnitPrice * O.Qty) / sum(O.Qty) as UnitPrice
    from Orders as O
        inner join ProdType as P on P.Id = O.Id
    group by O.ODate, P.Name
)
select
    ODate,
    max(case when Name = 'Magazines' then Qty else 0 end) as Magazines_Qty,
    max(case when Name = 'Magazines' then UnitPrice else 0 end) as Magazines_UnitPrice,
    max(case when Name = 'Journals' then Qty else 0 end) as Journals_Qty,
    max(case when Name = 'Journals' then UnitPrice else 0 end) as Journals_UnitPrice,
    max(case when Name = 'Books' then Qty else 0 end) as Books_Qty,
    max(case when Name = 'Books' then UnitPrice else 0 end) as Books_UnitPrice,
    max(case when Name = 'Newspaper' then Qty else 0 end) as Newspaper_Qty,
    max(case when Name = 'Newspaper' then UnitPrice else 0 end) as Newspaper_UnitPrice
from cte
group by ODate

Or dynamic, if you want to:

declare @stmt nvarchar(max)

select @stmt =
      isnull(@stmt + ', ', '') +
      'max(case when Name = ''' + name + ''' then Qty else 0 end) as ' + quotename(name + '_Qty') + ',' +
      'max(case when Name = ''' + name + ''' then UnitPrice else 0 end) as ' + quotename(name + '_UnitPrice')
from ProdType

select @stmt = '
    with cte as (
        select
            O.ODate, P.Name,
            sum(O.Qty) as Qty,
            sum(O.UnitPrice * O.Qty) / sum(O.Qty) as UnitPrice
        from Orders as O
            inner join ProdType as P on P.Id = O.Id
        group by O.ODate, P.Name
    )
    select
        ODate, ' + @stmt + ' from cte group by ODate'

exec dbo.sp_executesql
    @stmt = @stmt

sql fiddle demo

edited Oct 17, 2013 at 8:12

answered Oct 17, 2013 at 6:52

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Amy Over a year ago

Hi Roman, Thanks for your help. But the prod type may vary (may be less/may be more). So, I can't fix at case. Any advice to make it dynamic?

roman Over a year ago

@Amy actually, there're a lot of questions about it on SO already :)

Amy Over a year ago

Thanks a lot, Roman. It's working perfect. You save my time & help me out from stressful life. Really appreciate it! :)

Amy Over a year ago

Yes...Actually, I look through other questions as well..But still can't find it exactly what i need. May be I'm too new in SQL..

Panayotis · Accepted Answer · 2013-10-17 06:37:15Z

0

If you don't mind having qty and unit price in different tables, here's a sample query for the qty destination table:

;with cte as
(
SELECT ODate, Qty, UnitPrice, name FROM Orders INNER JOIN ProdType ON Orders.[Key] = ProdType.[Key]
)
SELECT ODate, [Magazines], [Journals], [Books], [Newspaper]  
FROM cte
PIVOT
(
SUM(Qty)
FOR name IN ([Magazines], [Journals], [Books], [Newspaper])
) AS QTYPivot

answered Oct 17, 2013 at 6:37

Panayotis

1,7831 gold badge18 silver badges31 bronze badges

1 Comment

Amy Over a year ago

Thanks, Panayotis. As per my project requirement, they need to be in same table & also need to be sequence.

Collectives™ on Stack Overflow

SQL Server Pivot multiple columns based on rows from another table

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related