CODEWITHSUNDEEP
sqlpostgresql
pushkin
pushkin

Reputation: 10219

Sql Joins - Separating a field into two based on Id

Let's say we have a table:

ID  |  ItemId  |  Value  |
1   |   201    |  Green  |
2   |   301    |  Wood   |
2   |   201    |  Red    |
4   |   201    |  Green  |
1   |   301    |  Iron   |

So {ItemId:Type} -> {201:Color}, {301:Material}

Is there a way to generate the following result set?

ID |  Color  | Material  |
1  |  Green  | Iron      |
2  |  Red    | Wood      |
4  |  Green  | (null)    |

So depending on the ItemId, put the Value in Column 1 or 2.

Upvotes: 2

Views: 53

Answers (2)

potashin
potashin

Reputation: 44581

You can use case to grab the Values according to the ItemId value and then group by ID to aggregate two rows with the same ID in one, while using max (or other aggregate function) to exclude null values :

select ID
     , max(case when ItemId = 201 then Value end) as Color
     , max(case when ItemId = 301 then Value end) as Material
from tbl
group by ID

Upvotes: 1

StuartLC
StuartLC

Reputation: 107267

You can simulate a pivot by using CASE...WHEN to selectively identify the result columns. You'll need to combine this with an aggregate (which will strip out the NULLs), and finally GROUP BY to compress the rows:

SELECT ID, 
       MIN(CASE WHEN ItemId = 201 THEN Value ELSE NULL END) AS Color,
       MIN(CASE WHEN ItemId = 301 THEN Value ELSE NULL END) AS Material
FROM MyTable
GROUP BY ID
ORDER BY ID ASC;

SqlFiddle here

Upvotes: 1

Related Questions