Hi ishuhasan21,
Please refer below SQL query.
SQL
Create Table #Stock
(
ST_QTY INT
,ST_KARTID INT
,ST_KART_N VARCHAR(20)
,ST_UNITID INT
,ST_UNIT_N VARCHAR(20)
,ST_STON_WGT INT
,ST_TOT_WGT INT
,INV_TYPID INT
)
INSERT INTO #Stock VALUES (30,2,'22k',Null,'PURCHASE',NULL,350,1)
INSERT INTO #Stock VALUES (40,3,'21k',Null,'PURCHASE',NULL,450,1)
INSERT INTO #Stock VALUES (20,2,'22k',Null,'OLD',NULL,300,0)
INSERT INTO #Stock VALUES (40,3,'21k',Null,'OLD',NULL,500,0)
SELECT INV_TYPID [STOCK_TYPE], ISNULL([24K],0) [24K],ISNULL([22K],0) [22K],ISNULL([21K],0) [21K],ISNULL([20K],0) [20K],ISNULL([18K],0) [18K]
FROM
(
SELECT INV_TYPID,ST_UNIT_N, ST_KART_N ,ST_TOT_WGT
FROM #Stock )p PIVOT (SUM(ST_TOT_WGT)
FOR [ST_KART_N]
IN ([24K],[22K],[21K],[20K],[18K])
)
AS pvt
ORDER BY [STOCK_TYPE] DESC
DROP TABLE #Stock
Output
| STOCK_TYPE |
24K |
22K |
21K |
20K |
18K |
| 1 |
0 |
350 |
450 |
0 |
0 |
| 0 |
0 |
300 |
500 |
0 |
0 |
