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

Question

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

Ultimately you're going to need different column names. Could you put the results into a temp table and replace GLN with SellerGLN, BuyerGLN and DeliverToGLN? — Rich Benner, Sep 19 '18 at 09:29
If those values are "dynamic" and you need all of them as separate columns then you need a dynamic pivot script. To avoid duplicate names you may append postfix to such names with `ROW_NUMBER`, for example, before pivoting. If those values are linked to "Seller", "Buyer" and so on as Rich said, then you have a problem - given data does not have strict row numbering, so it's gonna be a problem to determine which GLN belongs whom. — Ivan Starostin, Sep 19 '18 at 09:31
also, is this a one-time import from excel into sql server or is this a regular thing? — Rich Benner, Sep 19 '18 at 09:38
@RichBenner - 2016. It's not one-time import, but regular and the information within Excel file will always be in the structure I had presented. — cdrrr, Sep 19 '18 at 09:48

score 2 · Accepted Answer · answered Sep 19 '18 at 09:42

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.

This would've worked if it was one-time import, but the values will always be different. — cdrrr, Sep 19 '18 at 10:35
Do you have problem with SQL query or Excel import? Since I don't have your Excel file, I used `Src` as source table. Just remove first CTE section (`Src`), and replace `Src` occurences with `MyTable` used in your example. — Paweł Dyl, Sep 19 '18 at 10:57
my source table will contain the exact information in my example. So, you are saying to remove `SRC` which also creates `Renamed` as a `CTE` and then use `MyTable`? — cdrrr, Sep 24 '18 at 07:06

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

1 Answers1