agréger les éléments de la masortingce rangée par rangée ou colonne par colonne

J'ai une application impliquant un tableau 2D comme structure de données qui est sauvegardée dans une table CellItem (un élément un logging)

CREATE TABLE CellItem ( ID INT, IdRow INT -- FK RowName, IdCol INT -- FK ColName, Value varchar(max))

Afin de listr les éléments de ligne et de colonne j'ai défini 2 autres tables RowItem et ColItem

 CREATE TABLE RowItem ( ID INT, IdRow INT, IDCellIem INT) CREATE TABLE ColItem ( ID INT, IdCol INT, IDCellIem INT)

où IDCellIem est un ID de reference de key étrangère dans la table CellItem et IdRow et IdCol sont des ID de reference de key étrangère respectivement dans RowName et ColName définis ci-dessous

 CREATE TABLE RowName ( ID INT, name varchar(max)) CREATE TABLE ColName ( ID INT, name varchar(max))

Je ne peux pas find une solution concise. Je suis proche de compléter quelque chose qui combine pivotant, SQL dynamic et instruction STUFF nestede dans une boucle de slider. Mais je l'ai trouvé très compliqué.

Say pour un tableau 3×2, je m'attends à une sorte de SELECT pour returnner 3 sets d'loggings avec 2 colonnes pour l'élément de ligne ou 2 sets d'loggings avec 3 colonnes pour listr les éléments col, par exemple

Je veux être en mesure de listr par colonnes avec le résultat (pas seulement un élément par jeu d'loggings)

 col name1 ; elt1,1 ; elt2,1 col name2 ; elt1,2 ; elt2,2 col name3 ; elt1,3 ; elt2,3

ou par lignes avec le résultat (pas seulement un élément par jeu d'loggings)

 row name1 ; elt1,1 ; elt1,2 ; elt1,3 row name2 ; elt2,1 ; elt2,2 ; elt2,3

Ma question est comment script une procédure stockée pour listr la totalité des éléments de ligne ou de colonne qui s'insertaient dans des sets d'loggings comme décrit ci-dessus. Il est facile d'get une seule rangée ou col mais plus compliqué pour tous les get. Le formatting de la sortie est une performance critique pour le client où j'ai 1000 lignes x 200 colonnes

La requête proche de compelte est similaire à ce que @ Leran2002 a fourni (suite), où @VariableColumns est la list des colonnes que je veux sélectionner obtaine avec une instruction STUFF () (comme je l'ai dit, mon code est trop compliqué). #Temp table a le contenu à partir duquel je peux tirer les valeurs

 SET @Query = ' SELECT ' + @VariableColumns + ' FROM ( SELECT [rowID] AS pivotVariable, [value] FROM #Temp ) tmp PIVOT ( max([value]) FOR pivotVariable in (' + @VariableColumns + ') ) piv' EXEC(@Query);

Ce genre de question request souvent ici. Par exemple – Afficher les résultats des requêtes avec les résultats des tâches horizontalement et verticalement 3 tables

Mais j'ai aussi fait des scripts pour votre cas. J'espère avoir bien compris la question. Si je me trompe, veuillez commenter. Et ce sera bien si vous nous donnez quelques données de test.

Et je veux poser une question. Pourquoi n'utilisez-vous pas la structure suivante de CellItem sans RowItem et ColItem ?

 CREATE TABLE CellItem( ID INT, IdRow INT, -- FK to RowName IdCol INT, -- FK to ColName Value varchar(max) )

Ma réponse.

Données de test

 CREATE TABLE CellItem ( ID INT, Value varchar(max)) CREATE TABLE RowItem ( ID INT, IdRow INT, IDCellIem INT) CREATE TABLE ColItem ( ID INT, IdCol INT, IDCellIem INT) CREATE TABLE RowName ( ID INT, name varchar(max)) CREATE TABLE ColName ( ID INT, name varchar(max)) INSERT RowName(ID,name)VALUES (1,'r1'), (2,'r2'), (9,'r9') INSERT ColName(ID,name)VALUES (1,'c1'), (2,'c2'), (3,'c3'), (9,'c9') INSERT CellItem(ID,value)VALUES (11,'v11'), (12,'v12'), (22,'v22'), (23,'v23') INSERT RowItem(ID,IdRow,IDCellIem)VALUES (1,1,11), (2,1,12), (3,2,22), (4,2,23) INSERT ColItem(ID,IdCol,IDCellIem)VALUES (1,1,11), (2,2,12), (3,2,22), (4,3,23)

Si vous souhaitez afficher uniquement les lignes et les colonnes remplies

 DECLARE @colIndexes varchar(MAX)='', @colNames varchar(MAX)='' SELECT @colIndexes+=CONCAT(',',QUOTENAME(ID)), @colNames+=CONCAT(',',QUOTENAME(ID),' ',QUOTENAME(name)) FROM ColName WHERE ID IN(SELECT IdCol FROM ColItem) -- only filled columns SET @colIndexes=STUFF(@colIndexes,1,1,'') PRINT @colIndexes PRINT @colNames DECLARE @query varchar(MAX)='SELECT IdRow,NameRow'+@colNames+' FROM ( SELECT r.IdRow,rn.name NameRow, c.IdCol, i.Value FROM CellItem i JOIN ColItem c ON i.ID=c.IDCellIem JOIN RowItem r ON i.ID=r.IDCellIem JOIN RowName rn ON rn.ID=r.IdRow ) q PIVOT(MAX(Value) FOR IdCol IN('+@colIndexes+')) p ' PRINT @query EXEC(@query) GO

Colonnes en tant que lignes

 DECLARE @rowIndexes varchar(MAX)='', @rowNames varchar(MAX)='' SELECT @rowIndexes+=CONCAT(',',QUOTENAME(ID)), @rowNames+=CONCAT(',',QUOTENAME(ID),' ',QUOTENAME(name)) FROM RowName WHERE ID IN(SELECT IdRow FROM RowItem) -- only filled rows SET @rowIndexes=STUFF(@rowIndexes,1,1,'') PRINT @rowIndexes PRINT @rowNames DECLARE @query varchar(MAX)='SELECT IdCol,NameCol'+@rowNames+' FROM ( SELECT r.IdRow, c.IdCol,cn.name NameCol, i.Value FROM CellItem i JOIN RowItem r ON i.ID=r.IDCellIem JOIN ColItem c ON i.ID=c.IDCellIem JOIN ColName cn ON cn.ID=c.IdCol ) q PIVOT(MAX(Value) FOR IdRow IN('+@rowIndexes+')) p ' PRINT @query EXEC(@query) GO

Si vous voulez afficher toutes les lignes et colonnes

 DECLARE @colIndexes varchar(MAX)='', @colNames varchar(MAX)='' SELECT @colIndexes+=CONCAT(',',QUOTENAME(ID)), @colNames+=CONCAT(',',QUOTENAME(ID),' ',QUOTENAME(name)) FROM ColName SET @colIndexes=STUFF(@colIndexes,1,1,'') PRINT @colIndexes PRINT @colNames DECLARE @query varchar(MAX)='SELECT n.ID IdRow,n.name NameRow'+@colNames+' FROM ( SELECT r.IdRow, c.IdCol, i.Value FROM CellItem i JOIN ColItem c ON i.ID=c.IDCellIem JOIN RowItem r ON i.ID=r.IDCellIem ) q PIVOT(MAX(Value) FOR IdCol IN('+@colIndexes+')) p RIGHT JOIN RowName n ON n.ID=p.IdRow ' PRINT @query EXEC(@query) GO

Colonnes en tant que lignes

 DECLARE @rowIndexes varchar(MAX)='', @rowNames varchar(MAX)='' SELECT @rowIndexes+=CONCAT(',',QUOTENAME(ID)), @rowNames+=CONCAT(',',QUOTENAME(ID),' ',QUOTENAME(name)) FROM RowName SET @rowIndexes=STUFF(@rowIndexes,1,1,'') PRINT @rowIndexes PRINT @rowNames DECLARE @query varchar(MAX)='SELECT n.ID IdCol,n.name NameCol'+@rowNames+' FROM ( SELECT r.IdRow, c.IdCol, i.Value FROM CellItem i JOIN RowItem r ON i.ID=r.IDCellIem JOIN ColItem c ON i.ID=c.IDCellIem ) q PIVOT(MAX(Value) FOR IdRow IN('+@rowIndexes+')) p RIGHT JOIN ColName n ON n.ID=p.IdCol ' PRINT @query EXEC(@query) GO

Si les cellules peuvent contenir plusieurs valeurs et que vous souhaitez concaténer ces valeurs à l'aide d'une virgule, vous pouvez essayer ce qui suit. Voir mes commentaires Vous pouvez exécuter tout le script car j'ai utilisé des tables temporaires pour cet exemple.

 -- test tables and values CREATE TABLE #CellItem( ID INT, IdRow INT, -- FK to RowName IdCol INT, -- FK to ColName Value varchar(max) ) CREATE TABLE #RowName ( ID INT, name varchar(max)) CREATE TABLE #ColName ( ID INT, name varchar(max)) INSERT #RowName(ID,name)VALUES (1,'r1'), (2,'r2'), (9,'r9') INSERT #ColName(ID,name)VALUES (1,'c1'), (2,'c2'), (3,'c3'), (9,'c9') INSERT #CellItem(ID,IdRow,IdCol,Value)VALUES (1,1,1,'v11-a'), (2,1,1,'v11-b'), -- cell(1,1) contains 2 values (3,1,2,'v12-a'), (4,1,3,'v13-a'), (5,2,1,'v21-a'), (6,2,1,'v21-b'), (7,2,1,'v21-с') -- cell(2,1) contains 3 values -- variant 1 - rows by vertical DECLARE @colIndexes varchar(MAX)='', @colNames varchar(MAX)='' SELECT @colIndexes+=CONCAT(',',QUOTENAME(ID)), @colNames+=CONCAT(',',QUOTENAME(ID),' ',QUOTENAME(name)) FROM ColName --WHERE ID IN(SELECT DISTINCT IdCol FROM #CellItem) -- if you want to hide empty columns SET @colIndexes=STUFF(@colIndexes,1,1,'') PRINT @colIndexes PRINT @colNames DECLARE @query1 varchar(MAX)='SELECT n.ID IdRow,n.name NameRow'+@colNames+' FROM ( SELECT k.IdRow, k.IdCol, STUFF( ( SELECT ''; ''+i.Value FROM #CellItem i WHERE i.IdCol=k.IdCol AND i.IdRow=k.IdRow ORDER BY i.ID FOR XML PATH('''') ),1,2,'''') Value FROM ( SELECT DISTINCT IdRow,IdCol FROM #CellItem ) k ) q PIVOT(MAX(Value) FOR IdCol IN('+@colIndexes+')) p JOIN #RowName n ON n.ID=p.IdRow -- use RIGHT JOIN if you want show empty rows ' PRINT @query1 EXEC(@query1) -- variant 2 - rows by horisontal DECLARE @rowIndexes varchar(MAX)='', @rowNames varchar(MAX)='' SELECT @rowIndexes+=CONCAT(',',QUOTENAME(ID)), @rowNames+=CONCAT(',',QUOTENAME(ID),' ',QUOTENAME(name)) FROM RowName --WHERE ID IN(SELECT DISTINCT IdCol FROM #CellItem) -- if you want to hide empty rows SET @rowIndexes=STUFF(@rowIndexes,1,1,'') PRINT @rowIndexes PRINT @rowNames DECLARE @query2 varchar(MAX)='SELECT n.ID IdCol,n.name NameCol'+@rowNames+' FROM ( SELECT k.IdRow, k.IdCol, STUFF( ( SELECT ''; ''+i.Value FROM #CellItem i WHERE i.IdCol=k.IdCol AND i.IdRow=k.IdRow ORDER BY i.ID FOR XML PATH('''') ),1,2,'''') Value FROM ( SELECT DISTINCT IdRow,IdCol FROM #CellItem ) k ) q PIVOT(MAX(Value) FOR IdRow IN('+@rowIndexes+')) p JOIN #ColName n ON n.ID=p.IdCol -- use RIGHT JOIN if you want show empty columns ' PRINT @query2 EXEC(@query2) DROP TABLE #CellItem DROP TABLE #RowName DROP TABLE #ColName GO

Vous pouvez utiliser une table temporaire supplémentaire (voir #TempData ) à la place de la sous-requête

 -- test tables and values CREATE TABLE #CellItem( ID INT, IdRow INT, -- FK to RowName IdCol INT, -- FK to ColName Value varchar(max) ) CREATE TABLE #RowName ( ID INT, name varchar(max)) CREATE TABLE #ColName ( ID INT, name varchar(max)) INSERT #RowName(ID,name)VALUES (1,'r1'), (2,'r2'), (9,'r9') INSERT #ColName(ID,name)VALUES (1,'c1'), (2,'c2'), (3,'c3'), (9,'c9') INSERT #CellItem(ID,IdRow,IdCol,Value)VALUES (1,1,1,'v11-a'), (2,1,1,'v11-b'), -- cell(1,1) contains 2 values (3,1,2,'v12-a'), (4,1,3,'v13-a'), (5,2,1,'v21-a'), (6,2,1,'v21-b'), (7,2,1,'v21-с') -- cell(2,1) contains 3 values -- an additional temp table SELECT k.IdRow, k.IdCol, STUFF( ( SELECT '; '+i.Value FROM #CellItem i WHERE i.IdCol=k.IdCol AND i.IdRow=k.IdRow ORDER BY i.ID FOR XML PATH('') ),1,2,'') Value INTO #TempData FROM ( SELECT DISTINCT IdRow,IdCol FROM #CellItem ) k -- variant 1 - rows by vertical DECLARE @colIndexes varchar(MAX)='', @colNames varchar(MAX)='' SELECT @colIndexes+=CONCAT(',',QUOTENAME(ID)), @colNames+=CONCAT(',',QUOTENAME(ID),' ',QUOTENAME(name)) FROM ColName --WHERE ID IN(SELECT DISTINCT IdCol FROM #CellItem) -- if you want to hide empty columns SET @colIndexes=STUFF(@colIndexes,1,1,'') PRINT @colIndexes PRINT @colNames DECLARE @query1 varchar(MAX)='SELECT n.ID IdRow,n.name NameRow'+@colNames+' FROM #TempData q PIVOT(MAX(Value) FOR IdCol IN('+@colIndexes+')) p JOIN #RowName n ON n.ID=p.IdRow -- use RIGHT JOIN if you want show empty rows ' PRINT @query1 EXEC(@query1) -- variant 2 - rows by horisontal DECLARE @rowIndexes varchar(MAX)='', @rowNames varchar(MAX)='' SELECT @rowIndexes+=CONCAT(',',QUOTENAME(ID)), @rowNames+=CONCAT(',',QUOTENAME(ID),' ',QUOTENAME(name)) FROM RowName --WHERE ID IN(SELECT DISTINCT IdCol FROM #CellItem) -- if you want to hide empty rows SET @rowIndexes=STUFF(@rowIndexes,1,1,'') PRINT @rowIndexes PRINT @rowNames DECLARE @query2 varchar(MAX)='SELECT n.ID IdCol,n.name NameCol'+@rowNames+' FROM #TempData q PIVOT(MAX(Value) FOR IdRow IN('+@rowIndexes+')) p JOIN #ColName n ON n.ID=p.IdCol -- use RIGHT JOIN if you want show empty columns ' PRINT @query2 EXEC(@query2) DROP TABLE #TempData DROP TABLE #CellItem DROP TABLE #RowName DROP TABLE #ColName GO