CODEWITHSUNDEEP
sqloracle-databasestring-aggregation
San Antonio Aggie
San Antonio Aggie

Reputation: 11

SQL - How To Combine Muliple Rows into a Single Result Set

Current Query - 

Select Item, Attribute, AttributeValue from table;

Item      Attribute       Attributevalue
1          Color           Brown
1          Width           24
1          Height          36
2          color           white
2          Width           10
2          height          15

I am trying to get the output to be:

Item     Color    Width    Height
1        brown    24       36
2        white    10       15

Upvotes: 0

Views: 126

Answers (2)

San
San

Reputation: 4538

In Addition to what Gordon suggested, if you are using 11g version, you can use pivot as follows

with tab(Item,Attribute,Attributevalue) as
    (select 1,'Color','Brown' from dual union all
     select 1,'Width','24' from dual union all
     select 1,'Height','36' from dual union all
     select 2,'Color','white' from dual union all
     select 2,'Width','10' from dual union all
     select 2,'Height','15' from dual)
------
--end of data preparation
------
select * 
  from tab
 pivot (max(ATTRIBUTEVALUE) for ATTRIBUTE in ('Color' as color,
                                              'Width' as width,
                                              'Height' as height));

Output:

| ITEM | COLOR | WIDTH | HEIGHT |
|------|-------|-------|--------|
|    1 | Brown |    24 |     36 |
|    2 | white |    10 |     15 |

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

This isn't string aggregation, it is pivoting. Although the most recent version of Oracle supports the pivot keyword, you can do this using aggregation as well:

select item,
       max(case when attribute = 'Color' then Attributevalue end) as color,
       max(case when attribute = 'Width' then Attributevalue end) as Width,
       max(case when attribute = 'Height' then Attributevalue end) as Height
from table t
group by item;

Upvotes: 3

Related Questions