Avatar of tmajor99
tmajor99
 asked on

SQL - separating columns into rows

I need consolidate columns from a table.  I have a table will 3 group id and attribute id columns.  I need to create a separate record for each group of  attribute groups and attribute ids.  Basically, i need a table with just one column for the attribute group and ID.  

Table A:

RecID        GroupID     AttributeID        GroupID1     AttributeID1    GroupID2     AttributeID 2
-------      ---------      -----------         ----------      -------------    ----------     --------------
1                Cars             100                    Doors            134                   Sinks            499
2                Filters           450                    Paint             344                            



Expected Results:

RecID        GroupID     AttributeID        
-------      ---------      -----------  
1                Cars             100                    
1                Doors           134
1                Sinks            499
2                Filters           450                  
2                Paint             344
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Arana (G.P.)

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 a.RecID, ca1.GroupID, ca1.AttributeID
FROM dbo.table_A a
CROSS APPLY (
    SELECT * FROM (
    VALUES (1,GroupID,AttributeID), (2,GroupID1,AttributeID1), (3,GroupID2,AttributeID2)
    ) AS x(Group#,GroupID, AttributeID)
    WHERE x.GroupID <> ''
) AS ca1
ORDER BY RecID, Group#



slightwv (䄆 Netminder)

Is this not the same basic question you had before?


https://www.experts-exchange.com/questions/29172601/SQL-Select-to-change-record-orientation-from-horizontal-to-vertical.html


The accepted solution should also work here.

arnold

Ms sql and others have the pivot and unpivot.
In your case, you want to unpivot the columns into rows

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

>> In your case, you want to unpivot the columns into rows


Suggested in the duplicate question as well.