Rotation / Tableau croisé dynamic pour faire une colonne par valeur unique dans la colonne de regroupement

Le titre pourrait être un peu obtus, j'ai de la difficulté à expliquer cela de manière concise.

J'ai des données qui ressemblent à ceci ( SourceTable ):

Date Type High Low 2017-01-10 cats 21.4 10.4 2017-01-10 dogs 20.4 8.4 2017-01-20 cats 40.1 21.2 2017-01-20 dogs 20.1 1.0 

J'ai environ 300 "types" différents (comme dans la colonne Type). Le type est une key étrangère pointant vers une table de tous les types.

Type Table

• vérifier la valeur des parameters dans where clause pour appliquer une condition arbitraire
• Auto-jointure et obtention d'loggings uniques entre une date
• Comment fractionner une string dans plusieurs colonnes dans SQL Server 2012?
• Comment pivoter sur plusieurs colonnes SQL-Server?
• get la list des sortingmestres entre deux dates (SQL)

 Type cats dogs 

Tous les types ne sont pas représentés à chaque date.

Je veux que la table ci-dessus ressemble à ceci ( FlippedTable ):

 Date Cats_High Cats_Low Dogs_High Dogs_Low 2017-01-10 21.4 10.4 20.4 8.4 2017-01-20 40.1 21.2 20.1 1.0 

J'ai une solution de travail qui utilise une boucle WHILE en SQL dynamic qui construit une table temporaire en la modifiant constamment pour append de nouvelles colonnes et insert les nouveldatatables. Cela fonctionne, mais cela prend un time ridicule à exécuter. J'ai l'printing que mon approche SQL dynamic est inefficace et pourrait être améliorée avec un ou deux pivots.

Certes, nous pouvons soutenir que faire cela en premier lieu est inefficace et stupide, mais j'ai malheureusement mes raisons.

Voici mon approche SQL dynamic:

 DECLARE @type_counter INT = 0; DECLARE @date_counter INT = 0; DECLARE @all_types_total INT; DECLARE @all_dates_total INT; DECLARE @current_date DATE; DECLARE @current_type NVARCHAR(80); DECLARE @dynamic_sql NVARCHAR(max); CREATE TABLE #FlippedTable([Date] DATE PRIMARY KEY); SELECT @all_types_total=COUNT(*) FROM types; WHILE @type_counter < @all_types_total BEGIN SELECT @current_type=type FROM ( SELECT type, ROW_NUMBER() OVER(ORDER BY type) as [RowNumber] FROM SourceTable) AS subquery WHERE [RowNumber] = @type_counter; SET @dynamic_sql = 'ALTER TABLE #FlippedTable ADD [' + @current_type + '_Low] NUMERIC(7,2) NULL, ['+@current_type+'_High] NUMERIC(7,2)' EXECUTE sp_executeSQL @dynamic_sql; SELECT @all_dates_total = COUNT(*) FROM SourceTable WHERE type = @current_type; WHILE @date_counter < @all_dates_total BEGIN SELECT @current_date = [Date] FROM (SELECT [Date], ROW_NUMBER() OVER(ORDER BY [Date]) AS [RowNumber] FROM SourceTable) AS subquery WHERE [RowNumber] = @date_counter; IF EXISTS(SELECT * FROM FlippedTable WHERE [Date] = @current_date) BEGIN SET @dynamic_sql = 'UPDATE FlippedTable SET [' + @current_type + '_Low] = tb2.[Low], ['+@current_type+'_High] = tb2.[High] FROM FlippedTable AS tb1 INNER JOIN SourceTable AS tb2 ON tb1.Date = tb2.Date WHERE tb2.type = ''' + @current_type + ''' AND tb2.Date = ''' + CAST(@current_date AS nvarchar(50)) + ''''; EXECUTE sp_executeSQL @dynamic_sql; END ELSE BEGIN SET @dynamic_sql = 'INSERT INTO FlippedTable ([Date], ['+@current_type+'_Low], ['+@current_type+'_High] FROM SourceTable WHERE type = ''' + @current_type + ''' AND SourceTable.Date = ''' + CAST(@current_date AS nvarchar(50)) + ''''; END SET @date_counter = @date_counter + 1; END; SET @date_counter = 0; SET @type_counter = @type_counter + 1; END; 

Les ajouts à la table "types" sont très rares. Je connais tous les types, donc je suis d'accord avec le encoding en dur de tous les noms de colonne possibles si je dois, bien que ce ne soit pas idéal.

J'espère qu'il y aura une solution PIVOT à ce problème qui serait mieux. Je n'ai pas réussi à comprendre comment j'utiliserais cet outil dans ce cas. Quelle que soit la meilleure solution, j'ai l'printing d'utiliser un outil que je ne connais pas encore.

• Enregistrer le résultat de l'instruction select dans la table SQL Server
• SSMS Alter Table Row n'est pas accessible ailleurs
• Comment identifier des valeurs distinctes dans une colonne en fonction de duplicates dans une autre colonne
• comment mettre à jour la key étrangère qui dépend du champ 3 dans la table primaire
• Procédure stockée prenant 4 heures à courir

 Declare @SQL varchar(max) = ' Select * From ( Select A.Date ,B.* From YourTable A Cross Apply ( values (Type+''_High'',High) ,(Type+''_Low'',Low) ) B(Item,Value) ) A Pivot (max([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(Type+'_High') +','+QuoteName(Type+'_Low') From (Select Distinct Type From YourTable) A Order By 1 For XML Path('')),1,1,'') + ') ) p' Exec(@SQL); --Print @SQL 

 Date cats_High cats_Low dogs_High dogs_Low 2017-01-10 21.40 10.40 20.40 8.40 2017-01-20 40.10 21.20 20.10 1.00