We help IT Professionals succeed at work.

SQL - separating columns into rows

tmajor99
tmajor99 asked
on
5 Comments
1 solution
Medium Priority
53 Views
Last Modified: 2020-03-02
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
Comment
Watch Question

View Solution Only

CERTIFIED EXPERT
Commented:
maybe something quick and dirty like this:
select recid, groupid, attributeid from tableA a1
union
select recid, groupid1, attributeid1 from tableA a2
union
select recid, groupid2, attributeid2 from tableA a3

Open in new window

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:


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)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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
slightwv (䄆 Netminder)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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


Suggested in the duplicate question as well.