I am stuck in a situation while creating SQL for following scenario

Question

enter image description here

I have tried using Case-End but nothing worked.

Diff types of phone number are :

Home
Cell
Main
OTPR
Busn

score 1 · Answer 1 · answered Dec 21 '13 at 06:52

1

select emplid,
max(case when phone_type='home' then phone_number else -1 end) Home,
max(case when phone_type='cell' then phone_number else -1 end) Cell,
.....
from phone_data
group by emplid;

You can't mix character and numeric in one field, if you require n/a in the output you need another step which makes the phone number columns characters.

answered Dec 21 '13 at 06:52

James King

6,229
3
25
40

The above code is not working, returning -1 (N/A - data type for phone number is Char in actual case) in every case. Instead of returning phone numbers. – Anit Singh Dec 23 '13 at 04:23

score 0 · Answer 2 · answered Dec 21 '13 at 06:58

0

select t.empid as empid, cell.phone_number as cell, home.phone_number as Home
from telephones t 
inner join telephones cell on t.empid = cell.empid
inner join telephones home on t.empid = home.empid
where cell.Phone_type = "cell"
and home.Phone_type = "home"
group by t.empid

and a sql fiddle : http://sqlfiddle.com/#!2/6bb17/11

answered Dec 21 '13 at 06:58

NimChimpsky

46,453
60
198
311

returns a set of data only for employees who have both phone_type i.e., Cell and Home. In any case where a person have only one phone_type then will not return that particular row. – Anit Singh Dec 23 '13 at 04:51

score -1 · Answer 3 · answered Dec 21 '13 at 06:55

Try this SQL query!

DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.phone_type) 
            FROM youtablename c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')  

set @query = 'SELECT empid, ' + @cols + ' from 
            (
                select empid
                    , phone_number
                    , phone_type
                from youtablename
           ) x
            pivot 
            (
                 max(phone_number)
                for phone_type in (' + @cols + ')
            ) p '

execute(@query)

I am stuck in a situation while creating SQL for following scenario

3 Answers3