Convert a row data to columns in SQL Server

Question

I have a scenario in which I have three columns with multiple rows, I need to select them in single row. Below I am attaching temporary table scenario.

CREATE TABLE #Temp
(
  PersonID INT ,
  PhoneType VARCHAR(20) ,
  PhoneNumber VARCHAR(20)
)

INSERT  INTO #Temp
    ( PersonID ,
      PhoneType ,
      PhoneNumber 
    )
    SELECT  1212 ,
            'Business' ,
            '123456789'
    UNION ALL
    SELECT  1212 ,
            'Cell' ,
            '741852963'
    UNION ALL
    SELECT  1212 ,
            'Other' ,
            '987654321'
    UNION ALL
    SELECT  1212 ,
            'Home' ,
            '951357852'

SELECT  *
FROM    #Temp

DROP TABLE #Temp

I need to Display,

PersonID|Business|123456789|Cell|741852963|Other|987654321|Home|951357852

Can anybody help?

I have to combine this result with another Select Query,How may I do?

Answer 1

You can use conditional aggregation:

SQL Fiddle

SELECT
    PersonID,
    Business    = MAX(CASE WHEN PhoneType = 'Business' THEN PhoneNumber END),
    Cell        = MAX(CASE WHEN PhoneType = 'Cell' THEN PhoneNumber END),
    Other       = MAX(CASE WHEN PhoneType = 'Other' THEN PhoneNumber END),
    Home        = MAX(CASE WHEN PhoneType = 'Home' THEN PhoneNumber END)
FROM #Temp
GROUP BY PersonID

---

If you have unknown number of PhoneTypes, do a dynamic crosstab:

SQL Fiddle

DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = 
'SELECT
    PersonID' + CHAR(10)

SELECT @sql = @sql +
'   , MAX(CASE WHEN PhoneType = ''' + PhoneType + ''' THEN PhoneNumber END) AS' + QUOTENAME(PhoneType) + CHAR(10)
FROM (SELECT DISTINCT PhoneType FROM #Temp) t

SELECT @sql = @sql +
'FROM #Temp
GROUP BY PersonID'

EXEC sp_executesql @sql

Answer 2

You can use PIVOT like below,

SELECT  *
FROM    #Temp
PIVOT(MAX(PhoneNumber) 
          FOR PhoneType IN ([Business],[Cell],[Other],[Home])) AS PVTTable

OR I think you want something like below,

SELECT PersonID = STUFF((SELECT  PhoneType + ' | ' + PhoneNumber + ' | '
    FROM #Temp 
     ORDER BY PersonID
     FOR XML PATH('')), 1, 0, '')
FROM #Temp AS x
GROUP BY PersonID, PhoneType
ORDER BY PersonID;

Last one,

DECLARE @MainColumn AS NVARCHAR(MAX) = ''
SET @MainColumn = (SELECT  PersonID =  STUFF((SELECT PhoneType + ' | ' + PhoneNumber + ' | '
    FROM #Temp 
     ORDER BY PersonID
     FOR XML PATH('')), 1, 0, '')
FROM #Temp AS x
GROUP BY PersonID)

SELECT 'PersonID | ' + @MainColumn  AS FinalResult