DenStudent DenStudent - 2 months ago 7
SQL Question

SQL Server - transform columns to rows based on 'type-column'

I have following table:

ID | Type | Info
1 | Cell | 00123456789
1 | Mail | id1@hotmail.com
1 | Tel | 0123456
2 | Mail | id2@mail.com
.. | .. | ..


I want the following result:

ID | Cell | Mail | Tel
1 | 00123456789 | id1@hotmail.com | 0123456
2 | NULL | id2@mail.com | NULL


I tried this with cte's and joining them on ID, but this doesn't help for rows where (for example) the Cell number is empty. Any ideas?

As requested: the code I used (I edited this because it is a lot larger in my own database)

WITH cte_cell
AS
(
SELECT ID, TYPE, INFO AS cell
FROM table1
WHERE (TYPE = 'cell')
),

cte_mail
AS
(
SELECT ID, TYPE, INFO AS mail
FROM table1
WHERE TYPE = 'mail'
),

cte_tel
AS
(
SELECT ID, TYPE, INFO AS tel
FROM table1
WHERE TYPE = 'tel'
)

SELECT cte_cell.ID AS cte_cell_ID,
cte_mail.ID AS cte_mail_ID,
cte_tel.ID AS cte_tel_ID,

cte_cell.cell,
cte_mail.mail,
cte_tel.tel

FROM cte_cell FULL JOIN cte_mail
ON cte_cell.ID = cte_mail.ID
FULL JOIN cte_tel
ON cte_mail.ID = cte_tel.ID
;

Answer

If values in the type column is limited as you mentioned. Then you can use a CASE expression. Otherwise you could use dynamic sql query.

Query

SELECT ID, 
MAX(CASE [Type] WHEN 'Cell' THEN [info] END) AS [Cell],
MAX(CASE [Type] WHEN 'Mail' THEN [info] END) AS [Mail],
MAX(CASE [Type] WHEN 'Tel' THEN [info] END) AS [Tel]
FROM [your_table_name]
GROUP BY ID;

By using dynamic sql query.

Query

DECLARE @sql AS varchar(max);

SELECT @sql = 'SELECT ID, ' + STUFF((SELECT
     ', MAX(CASE [Type] WHEN ''' + [Type] + ''' THEN [info] END) AS ' + [Type]
     FROM [your_table_name]
FOR xml PATH (''))
    , 1, 2, '') + ' FROM [your_table_name] GROUP BY [ID];';

EXEC (@sql);
Comments