Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of Arana (G.P.)
Arana (G.P.)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial


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#



Avatar of slightwv (䄆 Netminder)
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.

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


Suggested in the duplicate question as well.