Convert Rows to columns using in SQL Server

Question

I have a table that contains users, there phone numbers, and the type of phone

Table PersonPhone:

ID      UserID      PhoneType   Phone
-----   -------     ----        ------
1     101           1           #
2     101           2           #
3     102           1           #
4     102           3           #
5     103           1           #
6     103           2           #
7     103           3           #
8     104           1           #
9     104           3           #
10    104           3           #

I am trying to create a select statement that has a result set similar to what is below:

ID         PhoneType1   PhoneType2  PhoneType3
-------     ----        ------      ------
101            #        #       #
102            #        #       #
103            #        #       #
104            #        #       #

Is there anyway to do this? I've tried:

SELECT PersonID 
, ISNULL(MAX(CASE phoneTypeId WHEN '2' THEN phone ELSE 0 END), 0) AS 'TYPE1'         
, ISNULL(MAX(CASE phoneTypeId WHEN '3' THEN phone ELSE 0 END), 0) AS 'TYPE2'     
, ISNULL(MAX(CASE phoneTypeId WHEN '5' THEN phone ELSE 0 END), 0) AS 'TYPE3' 
FROM personPhone
GROUP BY personId

Possible duplicate of [SQL Server : Transpose rows to columns](http://stackoverflow.com/questions/20111418/sql-server-transpose-rows-to-columns) — rll, Apr 27 '16 at 21:36
@rll - It's definitely a dupe. The person who posted this should take a look at the answers in that question. — Dresden, Apr 27 '16 at 21:41

score 0 · Answer 1 · edited Apr 27 '16 at 22:28

0

WITH p_cte AS (
  SELECT
    UserID
  , AggregateBy = Phone
  , [Spread] = 'PhoneType' + PhoneType
  FROM
    PersonPhone
)
SELECT
  *
FROM
  p_cte
PIVOT (MAX(AggregateBy) FOR [Spread] IN (PhoneType1, PhoneType2, PhoneType3)) pvt;

edited Apr 27 '16 at 22:28

MaxU - stand with Ukraine

205,989
36
386
419

answered Apr 27 '16 at 21:41

YS.

1,808
1
19
33

gmiley · Answer 2 · 2016-04-27T21:58:36.997

There are several ways to do this, two of which would be to use sub-queries, or alternatively you can do a join to the same table.

Subqueries:

select UserId, 
  (select Phone from PersonPhone p2 where p2.UserId = p1.UserId and p2.PhoneType = 1) as PhoneType1,
  (select Phone from PersonPhone p2 where p2.UserId = p1.UserId and p2.PhoneType = 2) as PhoneType3,
  (select Phone from PersonPhone p2 where p2.UserId = p1.UserId and p2.PhoneType = 3) as PhoneType3
from PersonPhone p1;

Or using join:

select p.UserId, p1.Phone as PhoneType1, p2.Phone as PhoneType2, p3.Phone as PhoneType3
from PersonPhone p
left join PersonPhone p1
on p1.UserId = p.UserId and p1.PhoneType = 1
left join PersonPhone p3
on p2.UserId = p.UserId and p2.PhoneType = 2
left join PersonPhone p3
on p2.UserId = p.UserId and p3.PhoneType = 3;

A third option would be to group on the UserId and use a CASE statement:

select UserId, 
  case when PhoneType = 1 then Phone end as PhoneType1,
  case when PhoneType = 2 then Phone end as PhoneType2,
  case when PhoneType = 3 then Phone end as PhoneType3
from PersonPhone
group by UserId;

I don't have access to a sql install at the moment to test those, but they should all work fine.

score 0 · Answer 3 · answered Apr 27 '16 at 21:59

SELECT 
PERSONID
,COALESCE(MAX(CASE WHEN phoneTypeId = '3' THEN SUBSTRING(Phone,1,12) END), '') as 'TYPE1'
,COALESCE(MAX(CASE WHEN phoneTypeId = '2' THEN SUBSTRING(Phone,1,12) END), '') as 'TYPE2'
,COALESCE(MAX(CASE WHEN phoneTypeId = '5' THEN SUBSTRING(Phone,1,12) END), '') as 'TYPE3'
FROM personphone
WHERE len(phone) = 12
GROUP BY personid
order by personid

Convert Rows to columns using in SQL Server

3 Answers3