Avatar of tmajor99
tmajor99
 asked on

MS SQL Select to change orientation from Horizontal to Vertical

Need some help with MS SQL select that can transform the orientation of a table from a horizontal alignment to a vertical alignment.  I currently have target ID columns listed horizontally but i would like each Target ID column to represent a row (Vertical orientation).   


For example


Item ID   Type    SourceID  TargetID1    TargetID2  TargetID3   TargetID4

-------   -----     ----------  ----------    -----------  -----------  ------------

1000      A          ECO_123  ABC            DDD          

1000      A          ECO_999                                           CFDD

1000      A          ECO_834  VVV            FGFF            CVVV          

1003      B           ECO_139  BI                                                          NMF

Expected results:


ItemID   Type     SourceID    TargetID

-------   ------   -----------  --------------

1000      A          ECO_123      ABC              

1000      A          ECO_123      DDD

1000      A          ECO_999      CFDD                                  
1000      A          ECO_834      VVV

1000      A          ECO_834      FGFF 

1000      A          ECO_834      CVVV  
1003      B           ECO_139     BI

1003      B           ECO_139     NMF 


             

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
tmajor99

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Pletcher


SELECT ca1.ItemID, ca1.Type, ca1.SourceID, ca1.TargetID
FROM dbo.main_table_name
CROSS APPLY (
    SELECT ItemID, Type, SourceID, 1 AS Target#, TargetID1 AS TargetID
    WHERE TargetID1 <> ''
    UNION ALL
    SELECT ItemID, Type, SourceID, 2 AS Target#, TargetID2
    WHERE TargetID2 <> ''
    UNION ALL
    SELECT ItemID, Type, SourceID, 3 AS Target#, TargetID3
    WHERE TargetID3 <> ''
    UNION ALL
    SELECT ItemID, Type, SourceID, 4 AS Target#, TargetID4
    WHERE TargetID4 <> ''
) AS ca1
ORDER BY ItemID, Type, Target#

tmajor99

ASKER
Excellent solution!!!!!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23