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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is 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
>> 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#