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

Link to home
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
    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?

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

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

Suggested in the duplicate question as well.