TSQL/MS SQL Server 2012: SQL Query - Pivoting and grouping

Question

I have the following set of data which is a result of a query:

╔══════╦══════════╦══════════╦═════════════╦═══════════╦════════════╗
║ Year ║ ItemCode ║ ItemName ║ ItmsGrpNam  ║ GroupCode ║   OutQty   ║
╠══════╬══════════╬══════════╬═════════════╬═══════════╬════════════╣
║ 2012 ║     1118 ║ Item 1   ║ Instruments ║       104 ║ 26.000000  ║
║ 2012 ║     1118 ║ Item 1   ║ Instruments ║       100 ║ 264.000000 ║
║ 2012 ║     1119 ║ Item 2   ║ Instruments ║       104 ║ 4.000000   ║
║ 2012 ║     1119 ║ Item 2   ║ Instruments ║       100 ║ 72.000000  ║
║ 2012 ║     1120 ║ Item 3   ║ Instruments ║       104 ║ 4.000000   ║
║ 2012 ║     1120 ║ Item 3   ║ Instruments ║       100 ║ 61.000000  ║
║ ...  ║          ║          ║             ║           ║            ║
╚══════╩══════════╩══════════╩═════════════╩═══════════╩════════════╝

It shows the quantity of products sold by customer group (GroupCode) and year (Year).

I now want to transform the table to look like this:

╔══════════╦══════════╦═════════════╦═══════════════╦═══════════════╦═══════════════╦═══════════════╗
║ ItemCode ║ ItemName ║ ItmsGrpNam  ║ 2012OutQty100 ║ 2012OutQty104 ║ 2013OutQty100 ║ 2013OutQty104 ║
╠══════════╬══════════╬═════════════╬═══════════════╬═══════════════╬═══════════════╬═══════════════╣
║ 1118     ║ Item 1   ║ Instruments ║ 264.000000    ║ 26.000000     ║             0 ║             0 ║
║ 1119     ║ Item 2   ║ Instruments ║ 72.000000     ║ 4.000000      ║             0 ║             0 ║
║ 1120     ║ Item 3   ║ Instruments ║ 61.000000     ║ 4.000000      ║             0 ║             0 ║
║ ...      ║          ║             ║               ║               ║               ║               ║
╚══════════╩══════════╩═════════════╩═══════════════╩═══════════════╩═══════════════╩═══════════════╝

So a combination of Year and GroupCode becomes a column and shows the respective OutQty.

I have absolutely no idea how to achieve this with SQL.

Thanks for any help!

First create an additional column with the text of the year concatenated with the groupcode, then do a dynamic pivot against this new column. — EzLo, Jun 15 '18 at 08:01

score 1 · Accepted Answer · answered Jun 15 '18 at 08:32

It can be achived by using dynamic pivoting.

This is a way to do it:

Sample table and data

create table dbo.TestTable (
[year] int,
itemcode int,
itemname nvarchar(50),
itemGrpName nvarchar(50),
GroupCode int,
Qty numeric(18,6),
PivotColumn nvarchar(50)
)

insert into TestTable
values 

( 2012 ,     1118 ,'Item 1','Instruments',     104 ,26.000000 ,'2012OutQty104'),
( 2012 ,     1118 ,'Item 1','Instruments',     100 ,264.000000,'2012OutQty100'),
( 2012 ,     1119 ,'Item 2','Instruments',     104 ,4.000000  ,'2012OutQty104'),
( 2012 ,     1119 ,'Item 2','Instruments',     100 ,72.000000 ,'2012OutQty100'),
( 2012 ,     1120 ,'Item 3','Instruments',     104 ,4.000000  ,'2012OutQty104'),
( 2012 ,     1120 ,'Item 3','Instruments',     100 ,61.000000 ,'2012OutQty100')

SQL Script

DECLARE @Str NVARCHAR(MAX);
DECLARE @Str2 NVARCHAR(MAX);
SELECT @Str = STUFF(
                   (
                       SELECT DISTINCT
                              ','+QUOTENAME(PivotColumn)
                       FROM dbo.TestTable FOR XML PATH('')
                   ), 1, 1, '');
 --PRINT @Str
SET @str2 = N'select * from (
   select ItemCode,ItemName,ItemGrpName,PivotColumn,Qty
   from  dbo.TestTable
   )x
   PIVOT 
   (sum(Qty) FOR PivotColumn in ('+@Str+')
   ) as p';
--PRINT @Str2;
EXEC (@Str2);

score 0 · Answer 2 · answered Jun 15 '18 at 08:48

You can achieve it with a temp table and a pivot!

You can create a temp table with the new column that combines the Year and the GroupCode

SELECT *
INTO #Temp
FROM
    (select Year, ItemCode, ItemName, ItmsGrpNam, GroupCode, OutQty, concat(Year,'OutQty',GroupCode ) as NewColumn
    from MyTable)

Then with pivot you are able to have the desired result.

Depending on how many distinct values there are (in order to become new columns) you can use dynamic pivot to get all distinct values.

For dynamic pivot there is an answer here: SQL Server dynamic PIVOT query?

TSQL/MS SQL Server 2012: SQL Query - Pivoting and grouping

2 Answers2

You can achieve it with a temp table and a pivot!