Turning multiple rows into one row

  • 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

  • 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/

  • 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

  • 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