CODEWITHSUNDEEP
sqlsql-serverpivot
DAN
DAN

Reputation: 37

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

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.

Upvotes: 0

Views: 825

Answers (1)

Kiran Hegde
Kiran Hegde

Reputation: 3681

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

Upvotes: 2

Related Questions