当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server pivot query - help required

问题描述:

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?

网友答案:

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+'%'
网友答案:

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: