SQL Server - Transform multiple columns into multiple rows

Question

I have a wide table that contains 60+ columns for UPS/Fedex tracking numbers. I am trying to reduce the number of columns and have a parent to child relationship.

SELECT 
    [TRACKING-NO1], [TRACKING-NO2], [TRACKING-NO3], [TRACKING-NO4],
    [TRACKING-NO5], [TRACKING-NO6], [TRACKING-NO7], [TRACKING-NO8],
    [TRACKING-NO9], [TRACKING-NO10]
FROM 
    [CustomerServiceOrderEntry].[dbo].[InvoicedOrdersTempTable]
WHERE
    [TRACKING-NO2] IS NOT NULL

The output returns a list of all the tracking numbers in one row (60+) columns. If there are child tracking numbers, then columns 2 - 60 will be populated; If there are no child tracking numbers, 2 - 60 will be NULL.

What I would like to do is have a parent tracking number and the associated child tracking number, and a flag to indicate it is a child.

Current Structure

New Output

Any help you can provide would be greatly appreciated...

Serkan Arslan · Accepted Answer · 2017-09-23 18:57:23Z

1

We need to use UNPIVOTfor convert columns to row.

But UNPIVOT does not returns any rows that not have child columns.

That' why, we need to determine this kind of rows additionally.

SELECT 
    [TRACKING-NO1] TrackingNumberParent ,
    'TRUE' TrackingNumberChildren
    ,TrackingNumberChild 
FROM 
    (SELECT * FROM [CustomerServiceOrderEntry].[dbo].[InvoicedOrdersTempTable] ) SRC 
        UNPIVOT( TrackingNumberChild FOR COL IN (
            [TRACKING-NO2],
            [TRACKING-NO3],
            [TRACKING-NO4],
            [TRACKING-NO5],
            [TRACKING-NO6],
            [TRACKING-NO7],
            [TRACKING-NO8],
            [TRACKING-NO9],
            [TRACKING-NO10])) PVT
UNION ALL
SELECT 
    [TRACKING-NO1] TrackingNumberParent, 
    'FALSE' TrackingNumberChildren, 
    NULL TrackingNumberChild 
FROM 
    [CustomerServiceOrderEntry].[dbo].[InvoicedOrdersTempTable] 
WHERE 
    [TRACKING-NO2] IS NULL
ORDER BY 
    TrackingNumberParent

edited Sep 23, 2017 at 18:57

answered Sep 23, 2017 at 18:46

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Stephens Over a year ago

Thanks! This is exactly what I needed! Good job!

Jason A. Long · Accepted Answer · 2017-09-24 02:28:38Z

The following should be considerably faster as it only takes a single pass over the base table...

SELECT 
    TrackingNumberParent = iot.[TRACKING-NO1],
    TrackingNumberChildren = CASE WHEN ctn.[TRACKING-NO] IS NULL THEN 'False' ELSE 'True' END,
    TrackingNumberChild = ctn.[TRACKING-NO]
FROM
    [CustomerServiceOrderEntry].[dbo].[InvoicedOrdersTempTable] iot
    OUTER APPLY ( 
                SELECT 
                    tn.COL_NO,
                    tn.[TRACKING-NO]
                FROM ( VALUES 
                    (2, iot.[TRACKING-NO2]), (3, iot.[TRACKING-NO3]), (4, iot.[TRACKING-NO4]),
                    (5, iot.[TRACKING-NO5]), (6, iot.[TRACKING-NO6]), (7, iot.[TRACKING-NO7]), 
                    (8, iot.[TRACKING-NO8]), (9, iot.[TRACKING-NO9]), (10, iot.[TRACKING-NO10])
                    ) tn (COL_NO, [TRACKING-NO])
                WHERE   
                    (
                        tn.[TRACKING-NO] IS NOT NULL 
                        OR 
                        tn.COL_NO = 2   
                    )
                ) ctn;

Collectives™ on Stack Overflow

SQL Server - Transform multiple columns into multiple rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related