sql server pivot query - help required

Question

I need to write SQL server query to pivot the data from the source table.

My source table looks like this -

Cust_Id  Item1_Desc_Count   Item2_Desc_Count   Item3_Desc_Count
-------  ----------------   ----------------   ------------
Cust1    10                 12                 9
Cust2    7                  1                  3
Cust3    12                 6                  0
...

Item master table looks like this -

Item_Id Item_Desc
------- ---------
1       Item1_Desc
2       Item2_Desc
3       Item3_Desc 

Please note that Item descriptions are used in column names in source table.

And I need my output to be -

Cust_Id   Item_Id   Count
--------  --------- ------
Cust1     1         10
Cust1     2         12
Cust1     3         9
Cust2     1         7
Cust2     2         1
Cust2     3         3
Cust3     1         12
... 

Can anyone help me achieve this using a SQL query?

Answer 1

If I understand, you would like to join you source table column names with content of your master table.

A pivot does not work this way. A pivot is when you want to turn your data like

Cust1 10 7 12 Cust2 12 1 6 Cust3 9 3 0

(check this exemple :https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tables-in-sql-server-a-simple-sample/)

In this case, I see this solution (but the Item_ID will be hard coded):

select Cust_Id, 1, Item1_Desc_Count as count FROM SourceTable UNION ALL select Cust_Id, 2, Item2_Desc_Count as count FROM SourceTable UNON ALL ...etc

I agree that it is not very elegant but it's simple and flexible if there is not a perfect match between the source table column names and the item_descs in the master table

Answer 2

This is a more dynamic approach. No need to specify all the Item Count columns.

The CROSS APPLY will UNPIVOT the Source table.

I should add UNPIVOT would be more performant, but I'm assuming you have many count columns.

---

Create Sample Data

Declare @Source table (Cust_Id varchar(25),Item1_Desc_Count int,Item2_Desc_Count int,Item3_Desc_Count int)
Insert Into @Source values
('Cust1',10,12,9),
('Cust2', 7, 1,3),
('Cust3',12, 6,0)

Declare @Item table (Item_Id int,Item_Desc varchar(50))
Insert Into @Item values
(1,'Item1_Desc'),
(2,'Item2_Desc'),
(3,'Item3_Desc')

Code Example

Select B.Cust_ID
      ,C.Item_ID
      ,Count = B.Value
 From  (Select XMLData = cast((Select * From @Source for XML Raw) as XML)) A
 Cross Apply (
                Select Cust_ID = r.value('@Cust_Id','varchar(50)')
                      ,Item    = attr.value('local-name(.)','varchar(100)')
                      ,Value   = attr.value('.','varchar(max)') 
                 From  A.XMLData.nodes('/row') as A(r)
                 Cross Apply A.r.nodes('./@*') AS B(attr)
                 Where attr.value('local-name(.)','varchar(100)') not in ('Cust_ID','OtherFieldsToExclude')
             ) B
 Join @Item C on B.Item Like C.Item_Desc+'%'

Returns

Cust_ID Item_ID Count
Cust1   1       10
Cust1   2       12
Cust1   3       9
Cust2   1       7
Cust2   2       1
Cust2   3       3
Cust3   1       12
Cust3   2       6
Cust3   3       0

EDIT - UnPivot Option

Select A.Cust_ID
      ,B.Item_ID
      ,Count = A.Value
 From (
        Select Cust_Id,Item,value
        From @Source 
        Unpivot ( Value for Item in (Item1_Desc_Count,Item2_Desc_Count,Item3_Desc_Count) ) u 
       ) A
Join @Item B on A.Item Like B.Item_Desc+'%'