How to write a SQL query for the following result?

Question

This is my current table data:

Godown_Column	Product_Column	Quantity
Godown 1	Product 1	10
Godown 1	Product 2	20
Godown 2	Product 3	30
Godown 3	Product 3	40

Here, Godowns_Columns has unlimited number of rows with different godowns.

How do I write a SQL query to get this result:

Product_Col	Godown 1	Godown 2	Godown 3
Product 1	10
Product 2	20
Product 3		30	40

Consider using pivot which transform Rows -> Columns https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — hoangnh, Apr 05 '21 at 06:58

score 0 · Answer 1 · answered Apr 05 '21 at 07:01

0

WITH GODOWNS(Godown_Column,     Product_Column,     Quantity) AS
(
    SELECT 'Godown 1',  'Product 1',    10 UNION ALL
    SELECT 'Godown 1',  'Product 2',    20 UNION ALL
    SELECT 'Godown 2',  'Product 3',    30 UNION ALL
    SELECT 'Godown 3',  'Product 3',    40
)
SELECT D.Product_Column,
  MAX(CASE WHEN Godown_Column='Godown 1' THEN QUANTITY ELSE 0 END)AS GODOWN_1,
  MAX(CASE WHEN Godown_Column='Godown 2' THEN QUANTITY ELSE 0 END)AS GODOWN_2,
  MAX(CASE WHEN Godown_Column='Godown 3' THEN QUANTITY ELSE 0 END)AS GODOWN_3
FROM GODOWNS AS D
GROUP BY D.Product_Column

GODOWNS is yours data example. Please replace reference to it with the reference to the actual table

answered Apr 05 '21 at 07:01

Sergey

4,719
1
6
11

Godowns are not fixed, they are unlimited, even don't know the name too, I have just mention the example there as godown 1, .... ? – Prabin Siwakoti Apr 05 '21 at 07:03
So you can have Godown1....Gowndown100 ? If yes, then I guess you can try to google "dynamic pivot". As far as I remember, there is an article from ItzikBen-Gan about its implementation – Sergey Apr 05 '21 at 07:05
But dynamic pivot example in googling also need to pass the name of columns, I didn't find the exact solution, I have followed this link https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/ – Prabin Siwakoti Apr 05 '21 at 07:19
Please take a look https://www.interfacett.com/blogs/dynamic-pivot-by-sql-server-instructor-jeff-jones/ – Sergey Apr 05 '21 at 07:32

score 0 · Accepted Answer · answered Apr 05 '21 at 07:15

You can use dynamic pivot since number of godowns is unknown.

Schema:

 create table mytable(Godown_Column varchar(50),    Product_Column  varchar(50), Quantity int)
 insert into mytable values('Godown 1', 'Product 1' ,10);
 insert into mytable values('Godown 1', 'Product 2' ,20);
 insert into mytable values('Godown 2', 'Product 3' ,30);
 insert into mytable values('Godown 3', 'Product 3' ,40);

Query:

 DECLARE @cols  AS NVARCHAR(MAX)='';
 DECLARE @query AS NVARCHAR(MAX)='';
 
 
 SET @cols = STUFF((SELECT distinct ',' + quotename(Godown_Column)
             FROM mytable
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
         ,1,1,'')
         
 
 set @query = 'SELECT Product_Column,' + @cols  + '
              from 
              (
 select * from mytable
              ) x
             pivot 
             (
                 sum(quantity)
                 for Godown_Column in (' + @cols + ')
             ) p'
 
 execute(@query)

Output:

Product_Column	Godown 1	Godown 2	Godown 3
Product 1	10	null	null
Product 2	20	null	null
Product 3	null	30	40

db<fiddle here

How to write a SQL query for the following result?

2 Answers2