0

I would like to create below pivot data in a more dynamic way based on what is in the TMP table without hard coding it. Is there any way to do that?

The structure has to be flexible since it can change frequently.

Means I would like to create columns based on what is in the AGG field in the TMP table. Currently the table has three values --> 'XX', 'YY' and 'ZZ' hence I need to create three columns. But in the above SQL I hard coded the three columns. This has to be changed to a more dynamic implementation since the TMP table can also have more values. Hence I would need to create corresponding columns. I hope this is somehow understandable.

WITH TMP AS
    (SELECT 'XX' AS AGG,
            50 AS VALUE,
            0 AS MONTH
     UNION ALL
     SELECT 'XX' AS AGG,
            150 AS VALUE,
            1 AS MONTH
     UNION ALL
     SELECT 'XX' AS AGG,
            300 AS VALUE,
            2 AS MONTH
     UNION ALL
     SELECT 'YY' AS AGG,
            25 AS VALUE,
            0 AS MONTH
     UNION ALL
     SELECT 'YY' AS AGG,
            50 AS VALUE,
            1 AS MONTH
     UNION ALL
     SELECT 'YY' AS AGG,
            75 AS VALUE,
            2 AS MONTH
     UNION ALL
     SELECT 'ZZ' AS AGG,
            500 AS VALUE,
            0 AS MONTH
     UNION ALL
     SELECT 'ZZ' AS AGG,
            600 AS VALUE,
            1 AS MONTH
     UNION ALL
     SELECT 'ZZ' AS AGG,
            700 AS VALUE,
            2 AS MONTH),


    SELECT Month,
           MAX(CASE Agg WHEN 'XX' THEN VALUE END) AS XX,
           MAX(CASE Agg WHEN 'YY' THEN VALUE END) AS YY,
           MAX(CASE Agg WHEN 'ZZ' THEN VALUE END) AS ZZ
    FROM TMP
    GROUP BY MONTH
uet
  • 13
  • 6
  • 2
    What have you tried? Your research will have thrown up dynamic SQL. – Dale K May 25 '22 at 06:24
  • This question is the result of what I already asked yesterday. See here https://stackoverflow.com/questions/72365807/case-expressions-using-values-from-different-rows/72365897#72365897 – uet May 25 '22 at 06:26
  • 1
    what do you mean by `"more dynamic way"` ? What do you want to achieve here ? – Squirrel May 25 '22 at 06:37
  • Means I would like to create columns based on what is in the AGG field in the TMP table. Currently the table has three values --> 'XX', 'YY' and 'ZZ' hence I need to create three columns. But in the above SQL I hard coded the three columns. This has to be changed to a more dynamic implementation since the TMP table can also have more values. Hence I would need to create corresponding columns. I hope this is somehow understandable. – uet May 25 '22 at 07:13
  • See here https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Abinash May 25 '22 at 07:17
  • Is there any way to build this in pure SQL? – uet May 25 '22 at 07:44
  • If you check my initial question here --> https://stackoverflow.com/questions/72365807/case-expressions-using-values-from-different-rows/72365897#72365897 It dont has to be a pivot solution. – uet May 25 '22 at 07:46
  • 1
    Your other question isn't relevant here. What is relevant is that you demonstrate how it might change, describing it in words is not clear. So I recommend a [mre] when you show examples of how it might change. And SQL is not suited for this sort of flexibility. You would normally do something like this in your reporting app because they are designed for this sort of thing. To introduce this flexibility into SQL you most likely need dynamic SQL (as I mentioned above). You can use dynamic SQL mechanisms to build up the query you wish to execute in a flexible and dynamic manner. – Dale K May 25 '22 at 08:01

0 Answers0