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
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
Last Comment
slightwv (䄆 Netminder)
ASKER CERTIFIED SOLUTION
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
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 questionIs this not the same basic question you had before?
The accepted solution should also work here.
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
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
>> In your case, you want to unpivot the columns into rows
Suggested in the duplicate question as well.
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#