CODEWITHSUNDEEP
sqlsql-servermultiple-columnsrows
DareDevil
DareDevil

Reputation: 5349

Convert a row data to columns in SQL Server

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?

Upvotes: 4

Views: 258

Answers (5)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

My solution would be:

SELECT outerTbl.MyDisplayValue
FROM
(
    SELECT  DISTINCT CAST(PersonID AS varchar(MAX)) + Concatenated.Contacts
    FROM #Temp AS tbl
    OUTER APPLY
    (
        SELECT '|' + innerTbl.PhoneType + '|' + innerTbl.PhoneNumber
        FROM #Temp AS innerTbl  
        WHERE innerTbl.PersonID=tbl.PersonID
        FOR XML PATH('')
    ) AS Concatenated(Contacts)
    FOR XML PATH('')
) AS outerTbl(MyDisplayValue)

Upvotes: 2

mindbdev
mindbdev

Reputation: 444

Try this

DECLARE @String VARCHAR(MAX) --To store the result
--Make the result by appending each rows
SELECT  @String = ISNULL(@String,'PersonID')  + ' | '+ PhoneType + ' | ' + PhoneNumber
FROM    #Temp
--return the string
SELECT @String

Upvotes: 2

PRADEEP SINGH Chundawat
PRADEEP SINGH Chundawat

Reputation: 41

You can use this code. It may be Help you.

DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(PhoneType) 
                    from #Temp
                    group by PhoneType, PersonID
                    order by PersonID
             FOR XML PATH('')), 1, 1, '')

SET @query = N'SELECT ' + @cols + N' from 
             (
                select *
                from #Temp
            ) x
            pivot 
            (
                max(PhoneNumber)
                for PhoneType in (' + @cols + N')
            ) p'

EXEC SP_EXECUTESQL @QUERY;

Upvotes: 4

Pedram
Pedram

Reputation: 6498

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

Upvotes: 4

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 4

Related Questions