Is it possible to have multiple where conditions in sql pivot query

Question

WITH Tablename(date, saletype, sales) AS
(
SELECT date1, sale_type, sales1 from tbl1 Where ...some conditions
UNION ALL
SELECT date2, saletype, sales2 from tbl2 Where ...some conditions
)
Select date from Tablename PIVOT (Sum(sales) FOR date in ([2013],[2014])) As Pvt

I understand this query brings an output of date, sales2013, sales2014.... that works fine.

I would like to know whether there is a way to have an extra condition in the pivot query - such as, I would like to separate the sales2014 into two -

1. sales2014 (saletype = 1). 
2. sales2014 (saletype = 2).

Hence, listing all sales2013 records in one column, listing sales2014 (saletype1) in one column and listing sales2014(saletype2) in separate column So, the output columns will be

date, sales2014, sales2014(saletype1), sales2014(saletype2)...

Is it possible? Experts please help...Thanks in advance.

Kiran Hegde · Accepted Answer · 2014-07-10 07:06:01Z

I think you can try the following query. The idea is that, since you need additional columns, you need to produce them as rows in your main query. To produce them in the main query i have used addtional UNION ALL

WITH Tablename(date, saletype, sales) AS
(
SELECT date1, sale_type, sales1 from tbl1 Where ...some conditions
UNION ALL
SELECT CAST(date1 AS VARCHAR(30)) + '(saletype' + CAST(sale_type AS VARCHAR(5)) + ')' , sale_type, sales1 from tbl1 Where  ...other conditions
UNION ALL
SELECT date2, saletype, sales2 from tbl2 Where ...some conditions
UNION ALL
SELECT CAST(date2 AS VARCHAR(30)) + '(saletype' + CAST(sale_type AS VARCHAR(5)) + ')' , sale_type, sales2 from tbl2 Where  ...other conditions

)
Select date from Tablename PIVOT (Sum(sales) FOR date in ([2013],[2013(saletype1)],[2013(saletype2)],[2014],[2014(saletype1)],[2014(saletype2)])) As Pvt

Collectives™ on Stack Overflow

Is it possible to have multiple where conditions in sql pivot query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related