2

I have a table structure :

ID   |Type         |Value
1   Email            abcd@xyz.com
1   Phone           12345
2   Phone             56789
2   WorkPhone     546789

I want to transform it into

ID   |Email                |Phone   |WorkPhone
1   abcd@xyz.com   12345   Null
2   Null                     56789   546789

Can anyone please help me to solve it. Sorry if this question has been repeated before.

Sayantan Mukherjee
  • 205
  • 1
  • 4
  • 13

2 Answers2

6

using conditional aggregation: 

select 
    id
  , max(case when type = 'email' then value end) as email
  , max(case when type = 'phone' then value end) as phone
  , max(case when type = 'workphone' then value end) as workphone
from t
group by id

or pivot() (in sql-server):

select id, email, phone, workphone
from t
pivot (max(value) for type in ([email],[phone],[workphone])) p

rextester demo: http://rextester.com/UGT52844

both return: 

+----+--------------+-------+-----------+
| id |    email     | phone | workphone |
+----+--------------+-------+-----------+
|  1 | abcd@xyz.com | 12345 | NULL      |
|  2 | NULL         | 56789 | 546789    |
+----+--------------+-------+-----------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59
1

The alternative to the solution above is the Pivot command in SQL Server. The following answer will only work for SQL-Server. This syntax will not work with MYSQL. You have tagged this question with both tags so I am not sure which you are running on. If you are using MSSQL the following link covers the command in depth https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot .

The following code will achieve the result you are looking for:

   SELECT [ID] AS [ID],
       [Email] AS Email, 
       [Phone] AS Phone,
       [WorkPhone] AS WorkPhone
   FROM 
   (
        SELECT ID, 
               [TYPE], 
               [Value] 
        FROM dbo.Pivot_Ex
   ) AS FT
   PIVOT 
   (
        MAX([Value])
        FOR FT.TYPE IN ([Email], [Phone], WorkPhone)
   ) AS PivotTable1
DBAMan
  • 21
  • 3