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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy