Combine multiple rows into multiple columns dynamically in SQL Server

Question

I have a large database table on which I need to perform the action below dynamically using Microsoft SQL Server.

From a result like this:

 badge   |   name   |   Job   |   KDA   |   Match 
 - - - - - - - - - - - - - - - -
 T996    |  Darrien |   AP    |   3.0   |   20
 T996    |  Darrien |   ADC   |   2.8   |   16
 T996    |  Darrien |   TOP   |   5.0   |   120

To a result like this using SQL:

badge   |   name   |  AP_KDA | AP_Match | ADC_KDA | ADC_Match | TOP_KDA | TOP_Match 
- - - - - - - - -
T996    |  Darrien |   3.0   |   20     |  2.8    |   16      |   5.0   |  120      

Even if there are 30 rows, it also will combine into a single row with 60 columns.

I am currently able to do it by hard coding (see the example below), but not dynamically.

Select badge,name,
(
 SELECT max(KDA)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'AP')
) AP_KDA,
(
 SELECT max(Match)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'AP')
) AP_Match,
(
 SELECT max(KDA)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'ADC')
) ADC_KDA,
(
 SELECT max(Match)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'ADC')
) ADC_Match,
(
 SELECT max(KDA)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'TOP')
) TOP_KDA,
(
 SELECT max(Match)
 FROM table
 WHERE (h.badge = badge) AND (h.name = name) 
 AND (Job = 'TOP')
) TOP_Match
from table h

I need an MSSQL statement that allows me to combine multiple rows into one row. The column 3 (Job) content will combine with the column 4 and 5 headers (KDA and Match) and become a new column.

So, if there are 6 distinct values for Job (say Job1 through Job6), then the result will have 12 columns, e.g.: Job1_KDA, Job1_Match, Job2_KDA, Job2_Match, etc., grouped by badge and name.

I need a statement that that can loop through the column 3 data so I don't need to hardcode (repeat the query for each possible Job value) or use a temp table.

Polder

hi everyone, the question I've asking is a mssql statement that allows me to combine multiple rows into one row. The column 3 content will combine with column 4,5 header and become a new column. So if there is 6 different data in column 3, then it will produce 12 columns. eg: Job1_KDA, Job1_Match, Job2_KDA, Job2_Match, Job3_KDA, Job3_Match, Job4_KDA, Job4_Match – Polder

There are many related questions here. Search [sql-server] pivot for multiple examples of doing exactly what you're asking. For instance, this question performs a similar type operation (although it converts six rows into three). – Ikey

Answer 1

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
) tt
PIVOT ( 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 Temp

DECLARE @columns nvarchar(max)
SELECT @columns = COALESCE(@columns + ', ', '') + Col FROM #Temp1 GROUP BY Col

DECLARE @sql nvarchar(max) = 'SELECT badge, name, '+@columns+' FROM #Temp1 PIVOT ( max(val) for Col in ('+@columns+') ) AS pvt'
exec (@sql)

DROP TABLE #Temp1

Buttonwood

Hi Roman, thanks for suggesting a solution. But this have not solve my case, as I need the column name to create themselves dynamically and not hardcode it. How can I make the PIVOT statement not static variable like AP_KDA,AP_Match but something like Job+'_KDA',Job+'_Match'? – Polder

I thought I gave you an answer saying 'using dynamic sql'. Do you need help to write the dynamic query? PIVOT was wroted by me just because I rarely use them and this was interesting for me. – Buttonwood

It seems to be fine, but I get an error at the final line. DECLARE @sql nvarchar(max) = 'SELECT badge, name, '+@columns+' FROM #Temp1 PIVOT ( max(val) for Col in ('+@columns+') ) AS pvt' It give me this error. Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Line 6 Must declare the scalar variable "@sql". I searched the error solution, all users also mention need to convert the columns into nvarchar, so I did so. I already converted all columns to nvarchar, but still unable to solve this error. – Polder

nvm, I solved it myself, it seems to be my data inside got % which makes it goes wrong. Now it works like a charm, thanks! – Polder

Answer 2

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

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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]


GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'gal', N'ornon', N'gili', 111)
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'porat', N'Yahel', N'LILl', 44444)
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'Shabtai', N'Or', N'Orya', 2222)
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'alex', N'levi', N'dolev', 33)
GO
INSERT [dbo].[usr_CUSTOMER] ([Last_Name], [First_Name], [Middle_Name], [ID]) VALUES (N'oren', N'cohen', N'ornini', 44444)
GO
INSERT [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 #TempString

IF OBJECT_ID('tempdb..#tempcount') IS NOT NULL 
DROP TABLE #tempcount

IF 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 StringRow


select distinct id 
into #tempcount
from usr_CUSTOMER


CREATE 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 int
SET @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 #TempString
select * from #tempcmbnition

Pourparler