Fonction de pivot sur plusieurs tables et colonnes dynamics dans SQL

Il y a quelques problèmes que vous devez résoudre afin d'get le résultat que vous désirez. Mais avant d'essayer une version SQL dynamic d'une requête, je recommand toujours que vous essayiez d'get votre résultat final en écrivant d'abord une version codée en dur ou statique. Cela vous permet d'get le résultat souhaité sans bogues, puis de le convertir en SQL dynamic en tant que requête finale.

Tout d'abord, obtenons vos structures de tables et échantillons de données dans un script réutilisable. Il semble que vous ayez seulement besoin de table2 et table3 pour get votre résultat final:

 create table #table2 ( id int, ctypeid int, columnname varchar(50) ) insert into #table2 values (1, 20, 'Account Manager'), (2, 20, 'Channel'), (3, 20, 'Start Date'), (4, 20, 'End Date'), (5, 20, 'Gross Annual'), (6, 6, 'Account Manager'), (7, 6, 'Channel'), (8, 6, 'Start Date'), (9, 6, 'End Date'), (10, 6, 'Gross Annual'); create table #table3 ( id int, table2id int, value varchar(50) ) insert into #table3 values (1, 1, 'Jack / Kate'), (2, 2, 'PS'), (3, 3, '06/03/2017'), (4, 4, '07/03/2017'), (5, 5, '2500'), (6, 6, 'Ollie'), (7, 7, 'D2D'), (8, 8, '06/03/2017'), (9, 9, '06/03/2017'), (10, 10, '5232'), (11, 1, 'Jack'), (12, 2, 'PSP'), (13, 3, '06/03/2017'), (14, 4, '07/03/2017'), (15, 5, '7000'), (16, 1, 'Jack Sparrow'), (17, 2, 'PS Sparrow'), (1, 3, '06/03/2017'), (19, 4, '07/03/2017'), (20, 5, '3000'), (21, 6, 'John'), (22, 7, 'JEDF'), (23, 8, '06/03/2017'), (24, 9, '06/03/2017'), (25, 10, '5232'); 

Ensuite, vous devez écrire votre requête PIVOT . Votre résultat final inclut uniquement les valeurs de 3 colonnes CTypeId , Value et ColumnName , de sorte que le début de votre requête PIVOT serait:

 select CTypeId, [Account Manager], [Channel], [Start Date], [End Date], [Gross Annual] from ( select ci.CTypeId, cd.Value, ci.ColumnName from #Table3 cd Inner Join #Table2 ci on ci.Id = cd.Table2Id ) d pivot ( max(Value) for ColumnName in ([Account Manager], [Channel], [Start Date], [End Date], [Gross Annual]) ) piv 

Démo Mais puisque vous agrégez des valeurs de string dans la colonne Value , vous ne CTypeId qu'une ligne pour chaque CTypeId :

 +---------+-----------------+---------+------------+------------+---------------+ | CTypeId | Account Manager | Channel | Start Date | End Date | Gross Annual | +---------+-----------------+---------+------------+------------+---------------+ | 6 | Ollie | JEDF | 06/03/2017 | 06/03/2017 | 5232 | | 20 | Jack Sparrow | PSP | 06/03/2017 | 07/03/2017 | 7000 | +---------+-----------------+---------+------------+------------+---------------+ 

ce qui n'est pas ce que vous voulez, donc vous devez faire quelque chose pour permettre plusieurs lignes. Si vous consultez un exemple de données renvoyées par la sous-requête:

 +---------+-------------+------------------+ | CTypeId | Value | ColumnName | +---------+-------------+------------------+ | 20 | Jack / Kate | Account Manager | | 20 | PS | Channel | | 20 | 06/03/2017 | Start Date | | 20 | 07/03/2017 | End Date | | 20 | 2500 | Gross Annual | | 6 | Ollie | Account Manager | | 6 | D2D | Channel | | 6 | 06/03/2017 | Start Date | | 6 | 06/03/2017 | End Date | | 6 | 5232 | Gross Annual | +---------+-------------+------------------+ 

Vous verrez que vous avez des données uniques sur une combinaison de valeurs CTypeId et ColumnName , ainsi vous pouvez créer un numéro de ligne unique en utilisant la fonction de fenêtrage row_number dans votre sous-requête qui peut être utilisée pour regrouper datatables pour un pivot. En PIVOT code PIVOT ci-dessus par:

 select CTypeId, [Account Manager], [Channel], [Start Date], [End Date], [Gross Annual] from ( select ci.CTypeId, cd.Value, ci.ColumnName, rn = row_number() over(partition by ci.CTypeId, ci.ColumnName order by cd.Value) from #Table3 cd Inner Join #Table2 ci on ci.Id = cd.Table2Id ) d pivot ( max(Value) for ColumnName in ([Account Manager], [Channel], [Start Date], [End Date], [Gross Annual]) ) piv order by CTypeId 

Voir la démo , vous obtenez le résultat souhaité:

 +---------+-----------------+------------+------------+------------+---------------+ | CTypeId | Account Manager | Channel | Start Date | End Date | Gross Annual | +---------+-----------------+------------+------------+------------+---------------+ | 6 | John | D2D | 06/03/2017 | 06/03/2017 | 5232 | | 6 | Ollie | JEDF | 06/03/2017 | 06/03/2017 | 5232 | | 20 | Jack | PS | 06/03/2017 | 07/03/2017 | 2500 | | 20 | Jack / Kate | PS Sparrow | 06/03/2017 | 07/03/2017 | 3000 | | 20 | Jack Sparrow | PSP | 06/03/2017 | 07/03/2017 | 7000 | +---------+-----------------+------------+------------+------------+---------------+ 

Une fois que vous avez obtenu le résultat final souhaité, il est facile de convertir la requête en SQL dynamic:

 Declare @Columns nvarchar(max) Declare @a nvarchar(max) Set @Columns = stuff((select distinct ',' + quotename(ColumnName) from #table2 for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, ''); Declare @sql nvarchar(max) = 'Select CTypeId, '+@Columns+' from ( select ci.CTypeId, cd.Value, ci.ColumnName, rn = row_number() over(partition by ci.CTypeId, ci.ColumnName order by cd.Value) from #Table3 cd Inner Join #Table2 ci on ci.Id = cd.Table2Id ) as s Pivot(MAX(Value) For ColumnName IN ('+@columns+')) as pvt order by CTypeId' execute(@sql); 

Voir la démo . Cela donne le même résultat que la version codée en dur avec la flexibilité de SQL dynamic.