MS SQL Server
  1. MS SQL Server
  3. TSQL créer un rapport dynamic à partir de deux tables, une table contient les en-têtes, l'autre, datatables
Intereting Posts
L'index non clusterisé ne fonctionne pas SSIS Problème de lecture à partir d'un file plat Comment incrémenter une key primaire dans une instruction d'insertion dans SQL Server 2005 Résultats lents de cfstoredproc Comment convertir des lignes en colonnes dans Sql Vérifier que SQL a été correctement exécuté dans WebMasortingx / Razor? Requête SQL – Comprendre la syntaxe DROP … CRÉER vs ALTER Accéder au nom de la database dans Up in DbMigration Ajouter deux lignes avec une key étrangère – SQL Quelle est la meilleure structure de database pour ce scénario? find des lignes dupliquées dans une table lors de l'insertion SQL: mise à jour d'une séquence d'éléments ordonnée par l'user dans un groupe SQL Server: vouloir insert des données dans une colonne, rest nulls: ne pas me laisser Créer des données pour la colonne Image dans SQL Server

TSQL créer un rapport dynamic à partir de deux tables, une table contient les en-têtes, l'autre, datatables

Imaginez un scénario dans lequel je souhaite get un rapport dynamic à partir de [FormValues] sous forme de données, en fonction de la colonne [Title] de [ReportItems] tant qu'en-tête.

Je suis vraiment confus comment le faire et essayé de nombreuses façons, mais aucun d'entre eux fonctionne bien.

Je devrais pouvoir donner une procédure à [ReportID] et get le résultat.

[FormID] et [FieldID] sont des keys relationnelles entre deux tables.

Toute aide aimable serait très appréciée. 

 CREATE TABLE #ReportItems( ReportItemID [uniqueidentifier] NOT NULL primary key, ReportID [uniqueidentifier] NOT NULL, FormID [uniqueidentifier] NOT NULL, FieldID [uniqueidentifier] NOT NULL, Title nvarchar(100) NOT NULL ) GO insert into #ReportItems select '5674d274-b146-4251-be0d-a15000e7cefa', '597d37c0-563b-42f0-99be-a15000dc7a65', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7a', 'First Name' insert into #ReportItems select '5674d274-b146-4252-be0d-a15000e7cefa', '597d37c0-563b-42f0-99be-a15000dc7a65', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7b', 'Last Name' insert into #ReportItems select '5674d274-b146-4253-be0d-a15000e7cefa', '597d37c0-563b-42f0-99be-a15000dc7a65', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7c', 'Age' GO CREATE TABLE #FormValues( ValueID uniqueidentifier NOT NULL primary key, FormID uniqueidentifier NULL, FieldID uniqueidentifier NOT NULL, UserName nvarchar(100) NOT NULL, Value nvarchar(max) null ) GO insert into #FormValues select 'af6dc400-3972-49ff-9711-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7a', 'user 1', 'Mike' insert into #FormValues select 'af6dc400-3972-49ff-9721-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7b', 'user 1', 'Oscar' insert into #FormValues select 'af6dc400-3972-49ff-9731-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7c', 'user 1', '20' insert into #FormValues select 'af6dc400-3972-49ff-9741-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7a', 'user 2', 'Merry' insert into #FormValues select 'af6dc400-3972-49ff-9761-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7c', 'user 2', '23' insert into #FormValues select 'af6dc400-3972-49ff-9771-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7a', 'user 3', 'Alen' insert into #FormValues select 'af6dc400-3972-49ff-9781-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7b', 'user 3', 'Escott' insert into #FormValues select 'af6dc400-3972-49ff-9791-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7c', 'user 3', '28' GO Select * from #ReportItems Select * from #FormValues GO

Et je veux un tel rapport comme résultat: 

 User Name | First Name | Last Name | Age User 1 | Mike | Oscar | 20 User 2 | Merry | | 23 User 3 | Alen | Escott | 28 User n | ... | ... | ... drop table #ReportItems drop table #FormValues

Pour get le résultat souhaité, vous devez utiliser la fonction PIVOT .

Si toutes vos valeurs ( title ) sont connues à l'avance, vous pouvez coder en dur une requête statique: 

 select * from ( select r.Title, f.UserName, f.Value from ReportItems r left join FormValues f on r.FormID = f.FormID and r.FieldID = f.FieldID ) src pivot ( max(value) for title in ([First Name], [Last Name], Age) ) piv;

Voir SQL Fiddle avec démo .

Mais il semble que vous aurez un nombre inconnu de titles que vous voulez transformer en colonnes. Si c'est le cas, alors vous voudrez utiliser SQL dynamic: 

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Title) from ReportItems FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT UserName,' + @cols + ' from ( select r.Title, f.UserName, f.Value from ReportItems r left join FormValues f on r.FormID = f.FormID and r.FieldID = f.FieldID ) x pivot ( max(value) for Title in (' + @cols + ') ) p ' execute(@query)

Voir SQL Fiddle avec démo

Le résultat pour les deux serait: 

 | USERNAME | FIRST NAME | LAST NAME | AGE | ------------------------------------------- | user 1 | Mike | Oscar | 20 | | user 2 | Merry | (null) | 23 | | user 3 | Alen | Escott | 28 |

Si vous avez un SortOrder spécifique dont vous avez besoin et que vous l'avez stocké dans une table, alors lorsque vous obtenez votre list de colonnes, vous utiliserez ce qui suit et il returnnera les colonnes dans le bon ordre: 

 select @cols = STUFF((SELECT ',' + QUOTENAME(Title) from ReportItems group by Title, sortorder order by sortorder FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

Voir SQL Fiddle avec démo 

 DECLARE @SQL NVARCHAR(MAX) SET @SQL='SELECT F.UserName' SELECT @SQL = @SQL+', MAX(CASE WHEN FieldID='''+CONVERT(VARCHAR(50), FieldID)+''' THEN F.Value END) AS ['+Title+'] ' FROM #ReportItems SET @SQL = @SQL+' FROM #FormValues F GROUP BY F.UserName ORDER BY 1' --select @sql EXEC sp_ExecuteSQL @SQL

Modification ultérieure: procédure de rapport basée sur l'ID de rapport et les colonnes de sorting 

 CREATE PROCEDURE spReport @ReportID uniqueidentifier, @SortColumns NVARCHAR(MAX) --shoud be a comma separated list of ReportItems.Title AS BEGIN DECLARE @SQL NVARCHAR(MAX) SET @SQL='SELECT F.UserName' SELECT @SQL = @SQL+', MAX(CASE WHEN F.FieldID='''+CONVERT(VARCHAR(50), FieldID)+''' THEN F.Value END) AS ['+Title+'] ' FROM ReportItems WHERE ReportID=@ReportID --create the dynamic sql only for the items in your report SET @SQL = @SQL+' FROM FormValues F JOIN ReportItems R ON F.FormID=R.FormID WHERE R.ReportID = @ReportID GROUP BY F.UserName ' IF @SortColumns<>'' SET @SQL = @SQL + 'ORDER BY '+@SortColumns -- beware of SQL injection. select @sql EXEC sp_ExecuteSQL @SQL, N'@ReportID uniqueidentifier', @ReportID=@ReportID END

Mais, et je ne peux pas le souligner assez, vous devez accorder une attention particulière au paramètre @SortColumns, car vous vous ouvrez à SQL Injection.