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

Question

I have following table:

ID | Type | Info
1  | Cell | 00123456789
1  | Mail | [email protected]
1  | Tel  | 0123456
2  | Mail | [email protected]
.. | ..   | ..

I want the following result:

ID | Cell        | Mail            | Tel  
1  | 00123456789 | [email protected] | 0123456
2  | NULL        | [email protected]    | 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
;

Edit your question and include the code that you have written. — Gordon Linoff
– Gordon Linoff, Commented Sep 30, 2016 at 10:50

Ullas · Accepted Answer · 2016-09-30 11:06:17Z

1

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);

edited Sep 30, 2016 at 11:06

answered Sep 30, 2016 at 10:55

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DenStudent Over a year ago

Easy and simple! Much better then all the CTE's I was messing with. Thanks

Sandip - Frontend Developer · Accepted Answer · 2016-09-30 11:01:17Z

1

Use PIVOT: If you have more then three type and not defined better to go with dynamic pivot query

SELECT Id,  [Cell],[Mail],[Tel] from 
(
    SELECT *
    FROM TableName
) x
pivot 
(
    MAX(Info)
    FOR Type in ([Cell],[Mail],[Tel])
) p

Output:

answered Sep 30, 2016 at 11:01

Sandip - Frontend Developer

14.7k5 gold badges38 silver badges62 bronze badges

2 Comments

DenStudent Over a year ago

Works great! I have chosen Ullas his answer as the "accepted answer", but for those wondering, this does the job too.

Sandip - Frontend Developer Over a year ago

any way we are here to help you not matter whose answer you accepted, thanks

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related