Combiner plusieurs lignes dans plusieurs colonnes de manière dynamique dans SQL Server

J'ai une grande table de base de données sur laquelle j'ai besoin d'effectuer l'action ci-dessous de manière dynamique à l'aide de Microsoft SQL Server.

À partir d'un résultat de ce genre:

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

À un résultat de ce genre à l'aide de 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

Même si il y a 30 lignes, il sera également combiner en une seule ligne avec 60 colonnes.

Je suis actuellement en mesure de le faire par le codage en dur (voir l'exemple ci-dessous), mais pas de façon dynamique.

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

J'ai besoin d'un MSSQL déclaration qui me permet de combiner plusieurs lignes en une seule ligne. La colonne 3 (Job) contenu combiner avec la colonne 4 et 5 en-têtes (KDA et Match) et de devenir une nouvelle colonne.

Donc, si il y a 6 valeurs distinctes pour Job (dire Job1 par Job6), alors le résultat sera de 12 colonnes, par exemple: Job1_KDAJob1_MatchJob2_KDAJob2_Matchetc., regroupés par badge et le nom.

J'ai besoin d'une instruction qui peut faire une boucle à travers la colonne 3 de données, donc je n'ai pas besoin de coder en dur (répétition de la requête pour chaque Job valeur) ou l'utilisation d'une table temporaire.

source d'informationauteur chabu | 2014-02-11

  1. 10

    Je voudrais faire à l'aide de sql dynamique, mais c'est (http://sqlfiddle.com/#!6/a63a6/1/0) le PIVOT de la 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: Cette façon de PIVOT pourrait être combiné avec le SQL dynamique (http://sqlfiddle.com/#!6/a63a6/7/0), encore une fois, je préfère faire plus simple, sans PIVOT, mais c'est tout bon exercice pour moi :

    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
  2. 1

    Combiner plusieurs des lignes et des colonnes dans une rangée et 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