kazisami kazisami - 5 months ago 19
SQL Question

2 X-Axis row, 1 Y-Axis Column and 2 Z-Axis Value Pivot Table SQL Server 2008 query

I have a SQL Server database table like below-

id | consignee | shipper | lss | cm1 | month
1 | hnm | A | 101 | 25 | Jan
2 | hnm | B | 102 | 35 | Jan
3 | hnm | A | 103 | 45 | Feb


I want to have an output like below-

consignee | shipper | Jan | Feb |
| cm1 | lss | cm1 | lss|
hnm | A | 25 | 1 | 45 | 1 |
hnm | B | 35 | 1 | 0 | 0 |


Here COUNT function is applied on lss and SUM applied on cm1. I don't even know if this is possible in SQL Server query. From what I have seen by searching is people converting rows into columns or 1 X-Axis, 1 Y-Axis and 1 Z-Axis queries, but nothing like this. I can do this easily on Excel using pivot, but I need to get this data from a database like this format and export to a Excel file.

Note: I am working on a ASP .NET application where I am using C# and OleDB.

Answer

yes. it is possible.

SELECT  consignee , shipper ,
        jan_cm1 = MAX (CASE WHEN month = 'Jan' THEN cm1 END),
        jan_lss = MAX (CASE WHEN month = 'Jan' THEN lss END),
        feb_cm1 = MAX (CASE WHEN month = 'Feb' THEN cm1 END),
        feb_lss = MAX (CASE WHEN month = 'Feb' THEN lss END)
FROM    yourtable
GROUP BY consignee , shipper