CODEWITHSUNDEEP
sql-server
cdrrr
cdrrr

Reputation: 1112

SQL Server - how to handle duplicate column names when using PIVOT relational operator

I have an Excel file as a source for my data which looks like this:

ColumnName  |Value
----------------------------
OrderNumber |PO-000576632
OrderDate   |2018-09-16
Delivery    |2018-09-22
Currency    |USD
TotalValue  |8006.34
Seller      |SupplierName
GLN         |000098 --this value is dynamic
Buyer       |CustomerName
GLN         |001592 --this value is dynamic
DeliverTo   |DeliveryAddress
GLN         |5940477481122 --this value is dynamic

Having the information in this structure, the only way to use further this information in SQL is by using PIVOT relational operator. I managed somehow to get to the desired output, but since there are 3 GLN columns name, I could use only the first one in that list. Is there any way to rename those columns before using them in PIVOT?

Statement

SELECT
      OrderNumber, OrderDate, Delivery,
      Currency, TotalValue, Seller, GLN,
      Buyer, DeliverTo  
FROM
(
SELECT 
      value, columnname
FROM  MyTable
) MyTable
PIVOT
(
MAX(value)
FOR ColumnName IN(    OrderNumber, OrderDate, Delivery,
      Currency, TotalValue, Seller, GLN,
      Buyer, DeliverTo))
piv

I did some research and find something promising here, but didn't helped me. Any tips would be helpful! Thanks

Upvotes: 0

Views: 350

Answers (1)

Paweł Dyl
Paweł Dyl

Reputation: 9143

If number of GLN columns is fixed (3), you can use following:

WITH Src AS
(
    SELECT * FROM (VALUES
    ('OrderNumber', 'PO-000576632'),
    ('OrderDate', '2018-09-16'),
    ('Delivery', '2018-09-22'),
    ('Currency', 'USD'),
    ('TotalValue', '8006.34'),
    ('Seller', 'SupplierName'),
    ('GLN', '000098'),
    ('Buyer', 'CustomerName'),
    ('GLN', '001592'),
    ('DeliverTo', 'DeliveryAddress'),
    ('GLN', '5940477481122')) T(ColumnName,Value)
), Renamed AS
(
    SELECT ColumnName+CASE WHEN N>1 THEN CAST(N as nvarchar(10)) ELSE '' END ColumnName, Value
    FROM (
        SELECT ColumnName,Value,ROW_NUMBER() OVER (PARTITION BY ColumnName ORDER BY (SELECT 1)) N
        FROM Src
    ) T
)
SELECT * FROM Renamed
PIVOT (MAX(value) FOR ColumnName IN(OrderNumber, OrderDate, Delivery, Currency, TotalValue, Seller, GLN, GLN2, GLN3, Buyer, DeliverTo)) piv

Result

OrderNumber     OrderDate       Delivery        Currency        TotalValue      Seller          GLN             GLN2            GLN3            Buyer           DeliverTo
--------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------
PO-000576632    2018-09-16      2018-09-22      USD             8006.34         SupplierName    5940477481122   000098          001592          CustomerName    DeliveryAddress

If there are unlimited GLN values, PIVOT clause must be dynamic.

Upvotes: 2

Related Questions