This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing an example of SQL Query which is doing Row aggregation into Column without using PIVOT in SQL Server.
If you are preparing for the interview of database developer, you should practice this kind of query which may ask by an interviewer.
Just execute the below demonstration, and try it yourself.
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE tbl_NoPivot ( Itemcode char(1) ,ItemStatus varchar(10) ,ItemQty int ) GO INSERT INTO tbl_NoPivot VALUES ('A','New',100),('A','Old',200),('A','Current',300) ,('A','Old',250),('A','Hold',300),('A','Old',50) ,('B','Hold',320),('B','Current',450),('B','Old',150) ,('B','Current',360),('B','New',320),('B','New',220) GO |
Solution without using a PIVOT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT Itemcode ,sum(case when ItemStatus='Current' then TotalQty end) as CurrentItemStatus ,sum(case when ItemStatus='Old' then TotalQty end) as OldItemStatus ,sum(case when ItemStatus='New' then TotalQty end) as NewItemStatus ,sum(case when ItemStatus='Hold' then TotalQty end) as HoldItemStatus FROM ( SELECT Itemcode, ItemStatus,sum(ItemQty) as TotalQty FROM tbl_NoPivot GROUP BY Itemcode, ItemStatus )AS T GROUP BY Itemcode |
The solution with using a PIVOT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT Itemcode ,[Current] AS CurrentItemStatus ,Old AS OldItemStatus ,New AS NewItemStatus ,Hold AS HoldItemStatus FROM ( SELECT Itemcode,ItemQty,ItemStatus FROM tbl_NoPivot ) T PIVOT ( SUM(ItemQty) for ItemStatus in (New,Old,[Current],Hold) ) PIV; |
