Combine multiple rows into multiple columns dynamically in SQL Server

I would do it using dynamic sql, but this is (http://sqlfiddle.com/#!6/a63a6/1/0) the PIVOT solution:

SELECT badge, name, [AP_KDa], [AP_Match], [ADC_KDA],[ADC_Match],[TOP_KDA],[TOP_Match] FROM(SELECT badge, name, col, val FROM( SELECT *, Job+'_KDA' as Col, KDA as Val FROM @T  UNION SELECT *, Job+'_Match' as Col,Match as Val  FROM @T) t) ttPIVOT ( max(val) for Col in ([AP_KDa], [AP_Match], [ADC_KDA],[ADC_Match],[TOP_KDA],[TOP_Match]) ) AS pvt

Bonus: This how PIVOT could be combined with dynamic SQL (http://sqlfiddle.com/#!6/a63a6/7/0), again I would prefer to do it simpler, without PIVOT, but this is just good exercising for me :

SELECT badge, name, cast(Job+'_KDA' as nvarchar(128)) as Col, KDA as Val INTO #Temp1 FROM Temp INSERT INTO #Temp1 SELECT badge, name, Job+'_Match' as Col, Match as Val FROM TempDECLARE @columns nvarchar(max)SELECT @columns = COALESCE(@columns + ', ', '') + Col FROM #Temp1 GROUP BY ColDECLARE @sql nvarchar(max) = 'SELECT badge, name, '+@columns+' FROM #Temp1 PIVOT ( max(val) for Col in ('+@columns+') ) AS pvt'exec (@sql)DROP TABLE #Temp1

Combine multiple rows and columns in a row and group by ID

IF OBJECT_ID('usr_CUSTOMER') IS NOT NULL DROP TABLE usr_CUSTOMER--------------------------CRATE TABLE---------------------------------------------------GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[usr_CUSTOMER](    [Last_Name] [nvarchar](50) NULL,    [First_Name] [nvarchar](50) NULL,    [Middle_Name] [nvarchar](50) NOT NULL,    [ID] [int] NULL) ON [PRIMARY]GOINSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'gal', N'ornon', N'gili', 111)GOINSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'porat', N'Yahel', N'LILl', 44444)GOINSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'Shabtai', N'Or', N'Orya', 2222)GOINSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'alex', N'levi', N'dolev', 33)GOINSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'oren', N'cohen', N'ornini', 44444)GOINSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'ron', N'ziyon', N'amir', 2222)GO----------------------------script---------------------------------------------IF OBJECT_ID('tempdb..#TempString') IS NOT NULL DROP TABLE #TempStringIF OBJECT_ID('tempdb..#tempcount') IS NOT NULL DROP TABLE #tempcountIF OBJECT_ID('tempdb..#tempcmbnition') IS NOT NULL         DROP TABLE #tempcmbnition----------------------------------------------------------------------------------------------------------------------------------------------------------------SELECT  ID,    [Last_Name] + '#' + [First_Name] + '#' + ISNULL([Middle_Name], '')  as StringRow     INTO #TempString  FROM [dbo].[usr_CUSTOMER]  ORDER BY StringRowselect distinct id into #tempcountfrom usr_CUSTOMERCREATE TABLE [dbo].[#tempcmbnition](        [ID] [int] NULL,        [combinedString] [nvarchar](max) NULL) ----------------------------------------------------------------------------------------------------------------------------------------------------------------DECLARE @tableID table(ID int)  insert into @tableID(ID) (select distinct Id from #tempcount)DECLARE @CNT intSET @CNT = (select count(*) from @tableID)declare @lastRow int----------------------------------------------------------------------------------------------------------------------------------------------------------------WHILE (@CNT  >=1 )    BEGIN               SET @lastRow = (SELECT TOP 1 id FROM #tempcount ORDER BY id DESC)        DECLARE @combinedString VARCHAR(MAX)         set @combinedString = ''        SELECT  @combinedString = COALESCE(@combinedString + '^ ', '') + StringRow        from #TempString        where ID = @lastRow        insert into #tempcmbnition (ID, [combinedString]) values(@lastRow ,@combinedString)        SET @CNT = @CNT-1        DELETE #tempcount where ID = @lastRow    END------------------------------------------------------------------------------------------------------------------------------------------------------------------ if you what remove first char--  UPDATE #tempcmbnition --  SET combinedString = RIGHT(combinedString, LEN(combinedString) - 1)----------------------------------------------------------------------------------------------------------------------------------------------------------------select *from #TempStringselect * from #tempcmbnition