August 8, 2018 at 11:35 am
The current query
SELECT AgentID, PhoneNumber, Description
FROM Agents
produces the first result-set in the attached screenshot (multi-row)
What should be the syntax to obtain the second (Desired Resultset) resultset in the attached screenshot (SINGLE-row) ?
Likes to play Chess
August 8, 2018 at 12:28 pm
It's always helpful to provide DDL and data
drop table if exists #temp
create table #Temp (AgentID int,
PhoneNumber varchar(20),
Description varchar(20))
insert into #Temp Values
(33,'6105307076','Mobile'),
(33,'6107356054','Work'),
(33,'6104561212','Home')
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Description)
FROM #temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT AgentID, ' + @cols + ' from
(
select AgentID
, PhoneNumber
, Description
from #temp
) x
pivot
(
max(PhoneNumber)
for Description in (' + @cols + ')
) p '
execute(@query)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 8, 2018 at 12:36 pm
If the resultant columns are fixed, you can just do:Select
AgentID,
Home As HomePhone,
Work As WorkPhone,
Mobile As MobilePhone
From #Temp T
Pivot
(
Max(PhoneNumber) For Description In ([Home],[Work],[Mobile])
) P
August 8, 2018 at 1:00 pm
Thanks!
Likes to play Chess
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply