CODEWITHSUNDEEP
sqldatabaseoracle-databasepivot
alexD
alexD

Reputation: 2364

Can't get SQL query to turn values in column for same ID into a single row with their own columns

Here is an example of the table data

ID attribute_name  attribute_value
1  attr1           val1
1  attr2           val2
1  attr3           val3
2  attr1           val4
2  attr2           val5
2  attr3           val6

Here is what I want the output of the query to be

ID  attr1 attr2 attr3
1   val1  val2  val3
2   val4  val5  val6

I've tried doing something like this: Merge multiple rows with same ID into one row, but am unable to get the results I need for my specific case.

I don't really have decent SQL to share here as a starting point, since I really need to start from scratch. I am hoping someone here can help me out.

Upvotes: 0

Views: 267

Answers (2)

irisk
irisk

Reputation: 111

First, commonly id - is a field that uniquely identifies row, second - just make a table that contains attr1, attr2 etc as a column.

Upvotes: 0

Chloe
Chloe

Reputation: 493

PIVOT is definitely your answer. https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Pivot takes and aggregates one value based on another.

DECLARE @Temp TABLE(ID INT, attribute_name VARCHAR(25), attribute_value VARCHAR(25))
INSERT INTO @Temp
VALUES(1, 'attr1', 'val1'), 
(1, 'attr2', 'val2'), 
(1, 'attr3', 'val3'), 
(2, 'attr1', 'val4'), 
(2, 'attr2', 'val5'), 
(2, 'attr3', 'val6')

SELECT *
    FROM (
        SELECT ID, attribute_name, attribute_value
            FROM @Temp
        ) p
        PIVOT
        (
            MAX(attribute_value)
            FOR attribute_name IN
            (attr1, attr2, attr3)
        ) AS pvt
            ORDER BY pvt.ID

Upvotes: 2

Related Questions