How to merge 2 rows of a table into 1 according to a third column by making it as a header

Question

net mvc so as a part of my learning I came accross this situation I have output of a query in a temp table as

       Reporting To                   Manager                        UserType
----------------------------------------------------------------------------------
VEDAYyyar H ( MI124557 )    P  KUMAR ( N156787 )       ASM
VEDAYyyar H  ( MI124557 )   H MIKAHAVA  ( N24578 )     RSM
VEDAYyyar H  ( MI124557  )   VNKAR IJ ( N245788)       NSM

I want to format it as

   ReportingTo       RSM         ASM     NSM
--------------------------------------------------------------------

so the three rows can be made into a single row

Any help will be appreciated

@KannanKandasamy no he need a single row with all the datas ..:) — Binoy Kumar, May 19 '17 at 10:35
Possible duplicate of [Three column SQL PIVOT](http://stackoverflow.com/questions/8393020/three-column-sql-pivot) — iamdave, May 19 '17 at 10:37

Kannan Kandasamy · Answer 1 · 2017-05-19T10:49:34.163

you can use pivot

select * from yourtable
pivot (max(Manager) for UserType in ([ASM],[RSM],[NSM])) p

If you have more columns and looking for dynamic columns in UserType then you can use dynamic query as below

declare @cols varchar(max)
declare @query nvarchar(max)

select @cols = stuff((select ','+QuoteName(UserType) from #yourtable group by UserType for xml path('')),1,1,'')

SET @query = 'select [Reporting To], ' + @cols + ' from ( ' + ' select [Reporting To], [Manager], [UserType] from Yourtable ) a ' 
SET @Query = @query + ' pivot (max(Manager) for UserType in (' + @cols + ')) p '

exec sp_executesql @query

Output as below:

+-------------------------------+--------------------------------+------------------------------------+----------------------------------+
|         Reporting To          |              ASM               |                NSM                 |               RSM                |
+-------------------------------+--------------------------------+------------------------------------+----------------------------------+
| VEDAMURTHY H C ( HI00007989 ) | P M ASHOK KUMAR ( HI00004297 ) | VIJAY SHANKAR IYER J ( HI00006779) | H S KESHAVA KUMAR ( HI00004056 ) |
+-------------------------------+--------------------------------+------------------------------------+----------------------------------+

score 0 · Answer 2 · answered May 19 '17 at 10:41

Use PIVOT table :

CREATE TABLE [dbo].[Table1]
 (
   [ReportingTo] [nvarchar](50) NULL,
   [Manager] [nvarchar](50) NULL,
   [UserType] [nvarchar](30) NULL
 ) 

 INSERT INTO Table1 (ReportingTo,Manager,UserType)
 SELECT 'VEDAMURTHY H C ( HI00007989 )','P M ASHOK KUMAR ( HI00004297 
        )','ASM' UNION ALL
 SELECT 'VEDAMURTHY H C ( HI00007989 )','H S KESHAVA KUMAR ( HI00004056 
        )','RSM' UNION ALL
 SELECT 'VEDAMURTHY H C ( HI00007989 )','VIJAY SHANKAR IYER J ( 
        HI00006779)','NSM' 


 SELECT *
 FROM 
 (
    SELECT * 
    FROM Table1
 ) A 
 PIVOT
 (
    MAX(Manager) FOR UserType IN ([ASM],[RSM],[NSM])
 )pvt

score 0 · Answer 3 · 2017-05-19T10:52:32.090

IF OBJECT_ID('Tempdb..#temp') IS NOT NULL
    DROP TABLE #TEMP

DECLARE @Temp TABLE (
    ReportingTo NVARCHAR(max)
    ,Manager NVARCHAR(max)
    ,UserType VARCHAR(20)
    )

INSERT INTO @Temp

SELECT 'VEDAYyyar H ( MI124557 )',   'P  KUMAR ( N156787 )'    ,     'ASM' Union all
SELECT 'VEDAYyyar H ( MI124557 )',   'H MIKAHAVA  ( N24578 )'  ,     'RSM' Union all
SELECT 'VEDAYyyar H ( MI124557 )',   ' VNKAR IJ ( N245788)'    ,     'NSM' 

SELECT *
INTO #TEMP
FROM @Temp

DECLARE @Sql NVARCHAR(max)
    ,@dynamicCol NVARCHAR(max)

SELECT @dynamicCol = STUFF((
            SELECT DISTINCT ', ' + QUOTENAME(UserType)
            FROM #TEMP
            FOR XML PATH('')
            ), 1, 1, '')


--Getting the column names Dynamically
SELECT @dynamicCol

SET @Sql = '
SELECT ReportingTo,' + @dynamicCol + ' From
(
SELECT ReportingTo,Manager,UserType From
#TEMP
)AS Src
PIVOT 
(
MAX(Manager) For UserType IN (' + @dynamicCol + ')
)
AS Pvt'

PRINT @Sql

EXEC (@Sql)

OutPut

  ReportingTo                   ASM                      NSM                    RSM
-------------------------------------------------------------------------------------------------
VEDAYyyar H ( MI124557 )    P  KUMAR ( N156787 )     VNKAR IJ ( N245788)    H MIKAHAVA  ( N24578 )

How to merge 2 rows of a table into 1 according to a third column by making it as a header

3 Answers3