Need help in framing SQL Query

Question

TABLE-1

OrderDate	CustomerNo	ProductId	Quantity
2021-03-01	1	1	10
2021-03-01	1	3	20
2021-03-02	1	2	15
2021-03-02	1	3	10
2021-03-03	1	1	10
2021-03-03	1	5	25

This table also contains data for other customers also Customers 2,3,4 ...

TABLE-2

ProductId	ProductName
1	P1
2	P2
3	P3
4	P4
5	P5

Products are not Fixed, may be added P6,P7 ...

RESULT :

OrderDate	P1	P2	P3	P5
2021-03-03	10			25
2021-03-02		15	10
2021-03-01	10		20

I need this result , is this possible using Pivot / UnPivot

Pivot would be the easiest but you can do it with inline selects — Chris Schaller, Mar 02 '21 at 13:45
Select-subqueries can be used in FROM-, JOIN- and WHERE statements but should never be used in SELECT portions as it is extremly deterimental to performance. None of those options are optimal in this case though. — Erik Blomgren, Mar 02 '21 at 14:02
Does this answer your question? [T-SQL dynamic pivot](https://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot) — Sander, Mar 02 '21 at 14:02
What is the issue with Pivot? The problem is with columns in SQL, there is no way around it, we have to define them in _some_ way, if dynamic SQL is a solution for you then I'd just dynamically construct the PIVOT query... — Chris Schaller, Mar 02 '21 at 14:29

score 0 · Answer 1 · answered Mar 02 '21 at 13:55

Yes, it is possible using Pivot but personally I do not like Pivot, I use CASE WHEN statements instead since it's simpler and less typing. If I remember correctly, there's no performance penalty for using CASE WHEN instead of Pivot either.

SELECT
    T1.OrderDate,
    [P1] = SUM(CASE WHEN T2.ProductName = 'P1' THEN Quantity END),
    [P2] = SUM(CASE WHEN T2.ProductName = 'P2' THEN Quantity END),
    [P3] = SUM(CASE WHEN T2.ProductName = 'P3' THEN Quantity END),
    [P4] = SUM(CASE WHEN T2.ProductName = 'P4' THEN Quantity END),
    [P5] = SUM(CASE WHEN T2.ProductName = 'P5' THEN Quantity END)
FROM Table1 T1
JOIN Table2 T2 ON T1.ProductId = T2.ProductID
GROUP BY T1.OrderDate

If you do not use the SUM() functions, you will get NULL values in your result, I'd recomend trying it out so you know why you need the SUM() function.

Side note: neither Pivot nor CASE WHEN enables for dynamic number of columns, you would need to utilize dynamic SQL for that.

In my case ProductNames are not fixed. Product name may be added — Sathesh Kumar, Mar 02 '21 at 13:57
Then you will need to solve that using a cursor (or possibly another looping mechanism) and Dynamic SQL together with the solution I wrote. — Erik Blomgren, Mar 02 '21 at 14:00
@Sathesh, there are many dynamic SQL pivot examples available on this site already, like [this one](https://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot). — Sander, Mar 02 '21 at 14:01

score 0 · Answer 2 · answered Mar 02 '21 at 15:02

Its not super pretty but you could generate the query from Erik Blomgren using dynamic SQL:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
    (
        SELECT 
            ','+QUOTENAME(ProductName) + '=SUM(CASE ProductId WHEN ' + CAST(ProductId as varchar) + ' THEN Quantity END)' 
        FROM Table2 FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)')
, 1, 1, '');
SELECT @query = 'SELECT OrderDate ' + @cols + ' FROM Table1 GROUP BY OrderDate';

EXEC sp_executesql @query

seeing that we're generating the SQL anyway, the actual query is matching on the Id, and not the ProductName, so we don't need to join at all for this instance

SELECT OrderDate
,[P1]=SUM(CASE ProductId WHEN 1 THEN Quantity END)
,[P2]=SUM(CASE ProductId WHEN 2 THEN Quantity END)
,[P3]=SUM(CASE ProductId WHEN 3 THEN Quantity END)
,[P4]=SUM(CASE ProductId WHEN 4 THEN Quantity END)
,[P5]=SUM(CASE ProductId WHEN 5 THEN Quantity END)
FROM Table1 GROUP BY OrderDate

You could just as easily use the same technique to generate a PIVOT / UNPIVOT query, but you specifically said you didn't want one of those ;)

Have a look at the result of `SELECT @query` before you execute it, runs fine on my tests, but your schema might be slightly different from what you have posted. As with all dynamic SQL scripts, when debugging, output the actual statement then paste it back into management studio to execute it directly. — Chris Schaller, Mar 03 '21 at 04:15

Gudwlk · Answer 3 · 2021-03-03T06:08:16.877

The result that you need to obtain can be obtained using a simple case statement or a using a Pivot and aggregation. I personally prefer PIVOT because it can do various data transformations and we can obtain data in any way as we wish. Here I added both solutions.

Solution 01: Using PIVOT and later Aggregate the result. This seems more complicated as you nee to know both PIVOT and the Aggregate functions.

SELECT  [B].[OrderDate] 
    , SUM([B].[P1]) AS [P1]
    , SUM([B].[P2]) AS [P2]
    , SUM([B].[P3]) AS [P3]
    , SUM([B].[P5]) AS [P5]            
FROM
(
SELECT [PIVOTED].[OrderDate]
, ISNULL( [PIVOTED].[P1] ,'') AS [P1]
, ISNULL( [PIVOTED].[P2], '') AS [P2]
, ISNULL( [PIVOTED].[P3], '') AS [P3]
, ISNULL( [PIVOTED].[P5], '') AS [P5]

FROM(

   SELECT 
     [T1].[OrderDate], 
     [T1].[ProductId], 
     [T2].[ProductName], 
     [T1].[Quantity] 
FROM [TABLE_1] [T1] 
INNER JOIN [TABLE_2] [T2] 
  ON [T1].[ProductId] =  [T2].[ProductId]

) P
PIVOT
(
   SUM([P].[Quantity])
   FOR [P].[ProductName] IN  ([P1],[P2],[P3],[P5])

) PIVOTED

) AS B
GROUP BY [B].[OrderDate]

Result: Result for Solution 01:

Solution 02: Using a simple case statement:

SELECT
 T1.OrderDate,
 [P1] = SUM(CASE WHEN T2.ProductName = 'P1' THEN Quantity END),
 [P2] = SUM(CASE WHEN T2.ProductName = 'P2' THEN Quantity END),
 [P3] = SUM(CASE WHEN T2.ProductName = 'P3' THEN Quantity END),     
 [P5] = SUM(CASE WHEN T2.ProductName = 'P5' THEN Quantity END)
FROM TABLE_1 T1
JOIN TABLE_2 T2 ON T1.[ProductId] = T2.[ProductID]
GROUP BY T1.[OrderDate]

Result 02: Result for Solution 02:

Note: You need to handle new products as they are added to the tables. As you can see both solutions hardcode the product names, you need to handle it. If you need a more generic solution, let me know. I will provide a dynamic PIVOT where you do not need to handle the new products as they are added.

Ok. Since you mentioned PIVOT, that also produces the output. Both need hardcoding. Anyways hope this will help. Thanks — Gudwlk, Mar 03 '21 at 06:05
If you need this written in dynamically , let me know here. Ill add the query. So that you do not need to worry about the product name. You do not need to hardcode, Let me know. If you find the answer useful, please let me know. Thanks :) — Gudwlk, Mar 03 '21 at 06:06
You can see the dynamic query I posted below as a new answer. If it is useful, please rate me. thank you — Gudwlk, Mar 03 '21 at 13:15

score 0 · Accepted Answer · answered Mar 03 '21 at 13:14

Here is the dynamic PIVOT for the query
DECLARE @SQL  AS VARCHAR(MAX)
, @cols_ AS  vARCHAR(MAX) 

--Making the column list dynamically 
SELECT @cols_   = STUFF((SELECT DISTINCT ', '+QUOTENAME(  [T2].[ProductName]) 
               FROM  [TABLE_2] [T2]                
               FOR XML PATH('')), 1, 1, '')              


print @cols_
--preparing PIVOT query dynamically. 

SET @SQL = ' SELECT
       pivoted.* 
      into #Temp_data
      FROM 
      (
    SELECT 
         [T1].[OrderDate],           
         [T2].[ProductName], 
         SUM([T1].[Quantity] ) AS  [Quantity]
    FROM [TABLE_1] [T1] 
    INNER JOIN [TABLE_2] [T2] 
      ON [T1].[ProductId] =  [T2].[ProductId]
    GROUP BY  [T1].[OrderDate],              
         [T2].[ProductName] 
      ) AS [p]
      PIVOT
      (
         MIN([P].[Quantity]) 
         FOR  [P].[ProductName]  IN (' + @cols_ + ')
      ) AS pivoted;



      select *        
       from #Temp_data [B]
      -- GROUP BY [B].[OrderDate]  

      drop table #Temp_data
   ';

   PRINT( @SQL)
   EXEC (@SQL)

Result: Dynamic PIVOT

Need help in framing SQL Query

4 Answers4