MS SQL Server
  1. MS SQL Server
  3. Combiner plusieurs lignes dans plusieurs colonnes de manière dynamic dans SQL Server
Intereting Posts
Générateur de rapports SQL Server Combinaison de tables multilignes Puis-je installer SQL Server avec SQL Server Express? Comment puis-je convertir une list arbitraire de strings en un set de lignes SQL? Champs calculés qui améliorent les performances mais doivent être conservés (EF) Requête nestede SQL dans le server SQL Comment puis-je get des valeurs distinctes dans COALESCE () Récupération du paramètre de sortie de procédure stockée via ADO Instruction SQL pour find des mots entiers Créer ou modifier le triggersur s'il existe Erreur SQL Server 2008 233 Puis-je mettre à niveau Reporting Services 2008 vers Reporting Service 2008 R2 sans mettre à niveau SQL Server? pyodbc ne jette pas sur l'erreur SQL Server @output qui n'a pas été fourni dans le server sql Sélectionnez un nombre spécifique de lignes à partir de la requête Entity Framework – Plusieurs relations 1 à 0..1 utilisant la même key

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

J'ai une grande table de database sur laquelle j'ai besoin d'effectuer l'action ci-dessous dynamicment en utilisant Microsoft SQL Server.

D'un résultat comme celui-ci: 

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

Pour un résultat comme celui-ci en utilisant 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 s'il y a 30 lignes, il sera également combiné en une seule ligne avec 60 colonnes.

Je suis actuellement capable de le faire par encoding dur (voir l'exemple ci-dessous), mais pas dynamicment. 

 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'une instruction MSSQL qui me permet de combiner plusieurs lignes en une seule ligne. Le contenu de la colonne 3 ( Job ) se combinera avec les en-têtes des colonnes 4 et 5 ( KDA et Match ) et deviendra une nouvelle colonne.

Donc, s'il y a 6 valeurs distinctes pour Job (par exemple Job1 à Job6 ), alors le résultat aura 12 colonnes, par exemple: Job1_KDA , Job1_Match , Job2_KDA , Job2_Match , etc., groupés par badge et nom.

J'ai besoin d'une déclaration qui peut parcourir datatables de la colonne 3 de sorte que je n'ai pas besoin de coder en dur (répéter la requête pour chaque valeur de Job possible) ou utiliser une table temporaire.

Je le ferais en utilisant SQL dynamic, mais c'est ( http://sqlfiddle.com/#!6/a63a6/1/0 ) la solution PIVOT: 

 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: Comment PIVOT pourrait être combiné avec SQL dynamic ( http://sqlfiddle.com/#!6/a63a6/7/0 ), encore une fois je préférerais le faire plus simple, sans PIVOT, mais c'est juste un 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

Combiner plusieurs rangées et colonnes dans une rangée et grouper par 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..#TempSsortingng') IS NOT NULL DROP TABLE #TempSsortingng 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 SsortingngRow INTO #TempSsortingng FROM [dbo].[usr_CUSTOMER] ORDER BY SsortingngRow select distinct id into #tempcount from usr_CUSTOMER CREATE TABLE [dbo].[#tempcmbnition]( [ID] [int] NULL, [combinedSsortingng] [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 @combinedSsortingng VARCHAR(MAX) set @combinedSsortingng = '' SELECT @combinedSsortingng = COALESCE(@combinedSsortingng + '^ ', '') + SsortingngRow from #TempSsortingng where ID = @lastRow insert into #tempcmbnition (ID, [combinedSsortingng]) values(@lastRow ,@combinedSsortingng) SET @CNT = @CNT-1 DELETE #tempcount where ID = @lastRow END -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- if you what remove first char -- UPDATE #tempcmbnition -- SET combinedSsortingng = RIGHT(combinedSsortingng, LEN(combinedSsortingng) - 1) -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- select *from #TempSsortingng select * from #tempcmbnition