How to retrieve the column value as row value?

Question

How can we retrieve information to be a row information from column values? For example, we have a table

|-----------|-------------|--------------|
|      ID   |     Field   |  FieldVlaue  |
|-----------|-------------|--------------|
|      1    |     Name    |  Jack        |
|-----------|-------------|--------------|
|      1    |     Country |  Australia   |
|-----------|-------------|--------------|
|      1    |     PostCode|  0277        |
|-----------|-------------|--------------|
|      2    |     Name    |  John        |
|-----------|-------------|--------------|
|      2    |     address |  Wyard       |
|-----------|-------------|--------------|
|      2    |  ContactNum |  1234567     |
|-----------|-------------|--------------|

Based on the above table, how can we get a table like below

|-----------|-------------|--------------|-------------|---------|-----------|
|      ID   |     Name    |  Country     | PostCode    |Address  | ContactNum|
|-----------|-------------|--------------|-------------|---------|-----------|
|      1    |     Jack    |  Australia   | 0277        |Null     | Null      |
|-----------|-------------|--------------|-------------|---------|-----------|
|      2    |     John    |  Null        | Null        | Wyard   |1234567
|-----------|-------------|--------------|-------------|---------|-----------|

Do a `GROUP BY`. Use `case` expressions to do conditional aggregation. — jarlh, Sep 18 '18 at 13:49
I am using Microsoft SQL to do this. I have tried CASE, but CASE will give duplicated IDs. — user3651247, Sep 18 '18 at 13:52

score 2 · Accepted Answer · edited Sep 18 '18 at 14:03

2

use condition aggregation

select id,
       max(case when Field='Name' then FieldVlaue end) as Name,
       max(case when Field='Country' then FieldVlaue end) as Country,
       max(case when Field='PostCode' then FieldVlaue end) as PostCode,
       max(case when Field='Address' then FieldVlaue end ) as Address,
       max(case when Field='ContactNum' then FieldVlaue end) as ContactNum 
from t
group by id

edited Sep 18 '18 at 14:03

Juan Carlos Oropeza

47,252
12
78
118

answered Sep 18 '18 at 13:51

Zaynul Abadin Tuhin

31,407
5
33
63

2

what's the max here do? – user3651247 Sep 18 '18 at 13:54
I don't understand how will this ensure correct id is joined with names, country etc. – nitzien Sep 18 '18 at 14:00
1

Every id have several field but only one `'Name'` so on the conditional aggregation everything `<> 'Name'` get the `NULL` as default result for case. you can write like this `case WHEN Field='Name' THEN FieldVlaue ELSE NULL END)` and `MAX()` ignore null values – Juan Carlos Oropeza Sep 18 '18 at 14:01
Hi Juan, just curious what if ID 1 can have more than 2 names ? for example ID1 has a name 'Jack' and another row with name 'Peter'. will this 'MAX()' still work? – user3651247 Sep 19 '18 at 05:04

How to retrieve the column value as row value?

1 Answers1