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
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image


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#

Avatar of tmajor99
tmajor99

ASKER

Excellent solution!!!!!
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo