asked on

Transform query on two columns in SQL 2008

The table I am working with looks like

Date | Category | Product | Qty | Amt

06/01/15 | A | X | 2 | 5
06/07/15 | A | Y | 1 | 4
06/14/15 | A | Y | 2 | 6

I want both Qty and Amt as the Pivot columns grouped by week number, Category and product. Below example is only for week 23 and 24. I need a table that could start from week1 and go upto week52

Category | Product | Week23Qty | Week23Amt | Week24Qty | Week24Amt
A | X | 5 | 4 | 3 | 5
A | Y | 1 | 4 | 2 | 6

I have written the following query

Select Category, product,
CASE (datepart(wk,[DATE])) WHEN '23' THEN AVG(Amt) END AS WEEK23Amt,
CASE (datepart(wk,[DATE])) WHEN '23' THEN SUM(Qty) END AS WEEK23Qty,
CASE (datepart(wk,[DATE])) WHEN '24' THEN AVG(Amt) END AS WEEK24Amt,
CASE (datepart(wk,[DATE])) WHEN '24' THEN SUM(Qty) END AS WEEK24Qty
From mySalesTable
WHERE Category = 'A'
GROUP BY datepart(wk,[DATE]),Category,Product
ORDER BY Product

But because of the grouping I am instead getting

Category | Product | Week23Qty | Week23Amt | Week24Qty | Week24Amt
A | X | 5 | 4 | NULL | NULL
A | X | NULL | NULL | 3 | 5
A | Y | 1 | 4 | NULL | NULL
A | Y | NULL | NULL | 2 | 6

Also, the query I have written will get clumsy when I include all week (1-52). I am not sure how to use a transform query if I need 2 pivot columns Week#Qty and Week#Amt.

Can you please help?

sameer2010

select *
from 
(
  select category+'||'+product, datepart(wk,[DATE])) week, amt
  from mySalesTable
) src
pivot
(
  avg(amt)
  for week in ([1], [2], [3],[4],....[52])
) piv

Open in new window

Do a similar one for Quantity and then do an inner join for overall result

Angel02

ASKER

Thank you! This is how I have written the query for certain weeks. I needed each weeks Amt and Qty side by side, so I mentioned the column names explicitly. Is there a better way to do this?
Also I needed the sum of all the "Qty" for a given product in all weeks. So I manually added the column values like below. Please let me know what you think.

Select tblAmt.Category, tblAmt.product, (ISNULL(tblQty.[1],0)+ ISNULL(tblQty.[2],0)+ ISNULL(tblQty.[3],0)+ ISNULL(tblQty.[4],0)+ ISNULL(tblQty.[23],0)+ ISNULL(tblQty.[24],0)) as TotalSold,
tblAmt.[1],tblQty.[1], tblAmt.[2],tblQty.[2], tblAmt.[3], tblQty.[3],
tblAmt.[4],tblQty.[4],tblAmt.[22],tblQty.[22],tblAmt.[23],tblQty.[23],tblAmt.[24],tblQty.[24],tblAmt.[25],tblQty.[25] from (select *
from
(
select Category, product, datepart(wk,[DATE]) [week], Amt from myTable
where (TYPE ='N' OR Type = 'W' OR Type='O') AND ([DATE] BETWEEN '6/1/2014' AND '6/14/2014')
) src1
pivot
(
avg(Amt)
for week in ([1], [2], [3],[4],[22],[23],[24],[25])
) piv) tblAmt
INNER JOIN
(select *
from
(
select Category, product, datepart(wk,[DATE]) [week], Qty from myTable
where (TYPE ='N' OR Type = 'W' OR Type='O') AND ([DATE] BETWEEN '6/1/2014' AND '6/14/2014')
) src2
pivot
(
SUM(Qty)
for week in ([1], [2], [3],[4],[22],[23],[24],[25])
) piv) tblQty
ON tblAmt.product = tblQty.product
where tblAmt.Category =’ A’
order by tblAmt.Category, tblAmt.product

sameer2010

The only way you could do this is using dynamic SQL. Let me know if that's ok. The above query would remain the same, just the columns would be renamed using dynamic SQL.

ASKER CERTIFIED SOLUTION

sameer2010

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Angel02

ASKER

Works like a charm!

I just added this to obtain the total Qty and it's perfect! Thank you!

select @str='select t1.Category,'+ 't1.product, (0' +
(
select '+ ISNULL(t2.['+cast([number] as varchar(2))+'],0) '
from master.dbo.spt_values where type='P' and number between 1 and 52
for xml path('')
) + ')As TotalSold' +