Search code examples
sql-servert-sqlsql-server-2012unpivot

Unpivot multiple columns and group into new columns

I am working with a client data which has multiple parameters like Projected units, Projected value, Quantity units, Quantity value, Parm Units, Parm Value for each month in columns.

Each parameter has been prefixed with the month name like JAN_Projected units, JAN_ Projected value, JAN_Quantity units, JAN_ Quantity value, JAN_ Parm Units, JAN_ Parm Value.

Here is the schema of my table:

CREATE TABLE [dbo].[tbl_forum_data](
    [Sno] [float] NULL,
    [JAN_Projected units] [float] NULL,
    [JAN_Projected value] [float] NULL,
    [JAN_Quantity units] [float] NULL,
    [JAN_Quantity value] [float] NULL,
    [JAN_Parm Units  ] [float] NULL,
    [JAN_Parm Value  ] [float] NULL,
    [FEB_Projected units] [float] NULL,
    [FEB_Projected value] [float] NULL,
    [FEB_Quantity units] [float] NULL,
    [FEB_Quantity value] [float] NULL,
    [FEB_Parm Units  ] [float] NULL,
    [FEB_Parm Value  ] [float] NULL,
    [MAR_Projected units] [float] NULL,
    [MAR_Projected value] [float] NULL,
    [MAR_Quantity units] [float] NULL,
    [MAR_Quantity value] [float] NULL,
    [MAR_Parm Units  ] [float] NULL,
    [MAR_Parm Value  ] [float] NULL
) ON [PRIMARY]

I want to group each of the projected units, projected value, quantity units, quantity value, parm units and param value based on each month like the below screenshot

Sample Output

If that is not possible, at least I want to change the columns into rows and group the units into separate column and values to separate column like the below one.

Output2

I tried to do the cross join however I am able to get the values into rows. Below is the code I used:

select Sno
       ,G.EventName
       ,G.EventDate
from [db_Temp].[dbo].[tbl_forum_data] as T
  cross apply (values ([JAN_Projected units], 'JAN_Projected units'),
([JAN_ Projected value], 'JAN_ Projected value'),
([JAN_Quantity units], 'JAN_Quantity units'),
([JAN_ Quantity value], 'JAN_ Quantity value'),
([JAN_ Parm Units  ], 'JAN_ Parm Units'),
([JAN_ Parm Value  ], 'JAN_ Parm Value'),
([FEB_ Projected units], 'FEB_ Projected units'),
([FEB_ Projected value], 'FEB_ Projected value'),
([FEB_Quantity units], 'FEB_Quantity units'),
([FEB_ Quantity value], 'FEB_ Quantity value'),
([FEB_ Parm Units  ], 'FEB_ Parm Units'),
([FEB_ Parm Value  ], 'FEB_ Parm Value'),
([MAR_ Projected units], 'MAR_ Projected units'),
([MAR_ Projected value], 'MAR_ Projected value'),
([MAR_ Quantity units], 'MAR_ Quantity units'),
([MAR_ Quantity value], 'MAR_ Quantity value'),
([MAR_ Parm Units  ], 'MAR_ Parm Units'),
([MAR_ Parm Value  ], 'MAR_ Parm Value')) as G(EventDate, EventName);

Any help is highly appreciated. Link for the fiddler SQL Schema.

Solution

  • This script shows two ways to do this.

    1. Using CROSS APPLY to produce the rows with required columns
    2. Using UNPIVOT + pivot using MAX(CASE ...)
    CREATE TABLE #tbl_forum_data(
    [Sno] [float] NULL,
    [JAN_Projected units] [float] NULL, [JAN_Projected value] [float] NULL,[JAN_Quantity units] [float] NULL,
    [JAN_Quantity value] [float] NULL, [JAN_Parm Units  ] [float] NULL,[JAN_Parm Value  ] [float] NULL,
    [FEB_Projected units] [float] NULL, [FEB_Projected value] [float] NULL, [FEB_Quantity units] [float] NULL,
    [FEB_Quantity value] [float] NULL, [FEB_Parm Units  ] [float] NULL, [FEB_Parm Value  ] [float] NULL,
    [MAR_Projected units] [float] NULL, [MAR_Projected value] [float] NULL, [MAR_Quantity units] [float] NULL,
    [MAR_Quantity value] [float] NULL, [MAR_Parm Units  ] [float] NULL, [MAR_Parm Value  ] [float] NULL
);
INSERT INTO #tbl_forum_data([Sno],
    [JAN_Projected units],[JAN_Projected value],[JAN_Quantity units],[JAN_Quantity value],[JAN_Parm Units  ],[JAN_Parm Value  ],
    [FEB_Projected units],[FEB_Projected value],[FEB_Quantity units],[FEB_Quantity value],[FEB_Parm Units  ],[FEB_Parm Value  ],
    [MAR_Projected units],[MAR_Projected value],[MAR_Quantity units],[MAR_Quantity value],[MAR_Parm Units  ],[MAR_Parm Value  ]
)
VALUES(1,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18);

-- using cross apply/union all
SELECT
    Sno,[Month],[Projected units],[Projected value],[Quantity units],[Quantity value],[Parm Units],[Parm Value]
FROM
    #tbl_forum_data
    CROSS APPLY (
        SELECT 'JAN' AS [Month], 0 AS [MonthSort],
               [JAN_Projected units] AS [Projected units], [JAN_Projected value] AS [Projected value],
               [JAN_Quantity units]  AS [Quantity units] , [JAN_Quantity value]  AS [Quantity value] ,
               [JAN_Parm Units  ]    AS [Parm Units]     , [JAN_Parm Value  ]    AS [Parm Value]
        UNION ALL
        SELECT 'FEB' AS [Month], 1 AS [MonthSort],
               [FEB_Projected units] AS [Projected units], [FEB_Projected value] AS [Projected value],
               [FEB_Quantity units]  AS [Quantity units] , [FEB_Quantity value]  AS [Quantity value] ,
               [FEB_Parm Units  ]    AS [Parm Units]     , [FEB_Parm Value  ]    AS [Parm Value]
        UNION ALL
        SELECT 'MAR' AS [Month], 2 AS [MonthSort],
               [MAR_Projected units] AS [Projected units], [MAR_Projected value] AS [Projected value],
               [MAR_Quantity units]  AS [Quantity units] , [MAR_Quantity value]  AS [Quantity value] ,
               [MAR_Parm Units  ]    AS [Parm Units]     , [MAR_Parm Value  ]    AS [Parm Value]
    ) AS up
ORDER BY
    Sno,[MonthSort];

-- using unpivot / pivot (using MAX(CASE ...) for multi column pivot)
SELECT
    Sno,[Month],
    [Projected units]=MAX(CASE WHEN category='Projected units' THEN value END),
    [Projected value]=MAX(CASE WHEN category='Projected value' THEN value END),
    [Quantity units]=MAX(CASE WHEN category='Quantity units' THEN value END),
    [Quantity value]=MAX(CASE WHEN category='Quantity value' THEN value END),
    [Parm Units]=MAX(CASE WHEN category='Parm Units  ' THEN value END),
    [Parm Value]=MAX(CASE WHEN category='Parm Value  ' THEN value END)
FROM
    (
        SELECT
            Sno,[Month]=LEFT(DataPoint,3),category=SUBSTRING(DataPoint,5,LEN(DataPoint)),value
        FROM
            #tbl_forum_data
            UNPIVOT (
                value FOR DataPoint IN (
                    [JAN_Projected units],[JAN_Quantity units],[JAN_Parm Units  ],
                    [FEB_Projected units],[FEB_Quantity units],[FEB_Parm Units  ],
                    [MAR_Projected units],[MAR_Quantity units],[MAR_Parm Units  ]
                )
            ) AS up_units
        UNION ALL
        SELECT
            Sno,[Month]=LEFT(DataPoint,3),category=SUBSTRING(DataPoint,5,LEN(DataPoint)),value
        FROM
            #tbl_forum_data
            UNPIVOT (
                value FOR DataPoint IN (
                    [JAN_Projected value],[JAN_Quantity value],[JAN_Parm value  ],
                    [FEB_Projected value],[FEB_Quantity value],[FEB_Parm value  ],
                    [MAR_Projected value],[MAR_Quantity value],[MAR_Parm value  ]
                )
            ) AS up_units
    ) AS up
GROUP BY
    Sno,[Month]
ORDER BY
    Sno,CASE [Month] WHEN 'JAN' THEN 0 WHEN 'FEB' THEN 1 ELSE 2 END;

DROP TABLE #tbl_forum_data;

    Results for both 1 and 2:

    +-----+-------+-----------------+-----------------+----------------+----------------+------------+------------+
| Sno | Month | Projected units | Projected value | Quantity units | Quantity value | Parm Units | Parm Value |
+-----+-------+-----------------+-----------------+----------------+----------------+------------+------------+
|   1 | JAN   |               1 |               2 |              3 |              4 |          5 |          6 |
|   1 | FEB   |               7 |               8 |              9 |             10 |         11 |         12 |
|   1 | MAR   |              13 |              14 |             15 |             16 |         17 |         18 |
+-----+-------+-----------------+-----------------+----------------+----------------+------------+------------+