Aide à la requête SQL avec table de pont

Vous pourriez faire quelque chose comme ceci:

 select userid, username , max(case when permissionid=10 then rights end) as permission10_rights , max(case when permissionid=11 then rights end) as permission11_rights , max(case when permissionid=12 then rights end) as permission12_rights from userpermissions group by userid, username; 

Vous devez append explicitement une colonne max (…) similaire pour chaque permissionid.

Réponse courte:

Non.

Vous ne pouvez pas append dynamicment des colonnes à votre requête.

Rappelez-vous que SQL est un langage basé sur un set. Vous interrogez des sets et des sets de jointures set.

Ce que vous creusez est une list récursive et exige que la list soit attachée horizontalement plutôt que verticalement.

Vous pouvez, un peu, faire semblant, avec un set d'auto-jointures, mais pour ce faire, vous devez connaître toutes les permissions possibles avant d'écrire la requête … ce que les autres suggestions ont proposé.

Vous pouvez également ramener le jeu d'loggings dans une autre langue, puis parcourir cette étape pour générer les colonnes appropriées.

Quelque chose comme:

 SELECT Table_User.userID, userName, permissionid, rights FROM Table_User LEFT JOIN Table_UserPermissions ON Table_User.userID =Table_UserPermissions.userID ORDER BY userName 

Et puis affichez toutes les permissions pour chaque user en utilisant quelque chose comme (Python):

 userID = recordset[0][0] userName = recordset[0][1] for row in recordset: if userID != row[0]: printUserPermissions(username, user_permissions) user_permissions = [] username = row[1] userID = row[0] user_permissions.append((row[2], row[3])) printUserPermissions(username, user_permissions) 

Si vous utilisez MySQL, je vous suggère d'utiliser group_concat () comme ci-dessous.

 select UserId, UserName, group_concat(PermissionId) as PermIdList, group_concat(Rights SEPARATOR ',') as RightsList from Table_user join Table_UserPermissions on Table_User.UserId = Table_UserPermissions.UserId= GROUP BY Table_User.UserId 

Cela reviendrait

 UserId UserName PermIdList RightsList 1 John 10,11,12 1,2,3 

Une search google rapide pour 'mssql group_concat' a révélé quelques procédures stockées différentes ( I ), ( II ) pour MSSQL qui peuvent atteindre le même comportement.

Vous pouvez créer une table_flatuserpermissions temporaire de:

  Identifiant d'user
 PermissionID1
 Droits1
 PermissionID2
 Rights2
 ... etc à autant de permissions / bonnes combinaisons que nécessaire 

Insérez des loggings dans cette table à partir de Table_user avec tous les champs d'autorisation et de droits null.

Mettre à jour les loggings de cette table à partir de table_userpermissions: insertion d'un premier logging et définition de PermissionID1 & Rights1, deuxième logging pour une mise à jour d'user PermissionsID2 & Rights2, etc.

Ensuite, vous interrogez cette table pour générer votre rapport.

Personnellement, je m'en tiendrai aux colonnes UserId, UserName, PermissionID, Rights que vous avez maintenant.

Peut-être substituer dans un text pour PermissionID et droits au lieu des valeurs numériques.

Peut-être sortinger la table par PermissionID, User au lieu de User, PermissionID afin que l'auditeur puisse vérifier les users sur chaque type d'autorisation.

Si c'est acceptable, une stratégie que j'ai utilisée, à la fois pour la design et / ou la mise en œuvre, consiste à vider la requête sans la redirect vers Excel ou Access. Les deux ont des interfaces user beaucoup plus conviviales pour datatables pivotantes, et beaucoup plus de gens sont à l'aise dans cet environnement.

Une fois que vous avez un design que vous aimez, alors il est plus facile de penser à la façon de le dupliquer dans TSQL.

Il semble que la fonction de pivot a été conçue pour les situations où vous pouvez utiliser une fonction d'agrégation sur l'un des champs. Comme si je voulais savoir combien de revenus chaque vendeur faisait pour l'entreprise x. Je pourrais résumer le champ des prix d'une table des ventes. Je voudrais alors get le vendeur et combien de revenus dans les ventes qu'ils ont. Pour les permissions, cela n'a aucun sens de sumr / countr / etc le champ permissionId ou le champ Droits.

Vous pouvez regarder l'exemple suivant sur la création de requêtes de tableau croisé dans SQL:

http://www.databasejournal.com/features/mssql/article.php/3521101/Cross-Tab-reports-in-SQL-Server-2005.htm

Il semble qu'il y ait de nouvelles opérations qui ont été incluses dans le cadre de SQL Server 2005 appelé PIVOT et UNPIVOT

Pour ce type de transformation de données, vous devrez effectuer un UNPIVOT puis un PIVOT des données. Si vous connaissez les valeurs que vous souhaitez transformer, vous pouvez coder en dur la requête à l'aide d'un pivot statique, sinon vous pouvez utiliser SQL dynamic.

Créer des tables:

 CREATE TABLE Table_User ([UserId] int, [UserName] varchar(5)) ; INSERT INTO Table_User ([UserId], [UserName]) VALUES (1, 'John'), (2, 'Joe'), (3, 'James') ; CREATE TABLE Table_UserPermissions ([UserId] int, [PermissionId] int, [Rights] int) ; INSERT INTO Table_UserPermissions ([UserId], [PermissionId], [Rights]) VALUES (1, 10, 1), (1, 11, 2), (1, 12, 3), (2, 11, 2), (2, 12, 3), (3, 10, 2) ; 

PIVOT statique:

 select * from ( select userid, username, value, col + '_'+ cast(rn as varchar(10)) col from ( select u.userid, u.username, p.permissionid, p.rights, row_number() over(partition by u.userid order by p.permissionid, p.rights) rn from table_user u left join Table_UserPermissions p on u.userid = p.userid ) src unpivot ( value for col in (permissionid, rights) ) unpiv ) src pivot ( max(value) for col in (permissionid_1, rights_1, permissionid_2, rights_2, permissionid_3, rights_3) ) piv order by userid 

Voir SQL Fiddle avec démo

PIVOT dynamic:

Si vous avez un nombre inconnu de permissionid et de rights , vous pouvez utiliser dynamic sql:

 DECLARE @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) select @colsPivot = STUFF((SELECT ',' + quotename(c.name +'_'+ cast(t.rn as varchar(10))) from ( select row_number() over(partition by u.userid order by p.permissionid, p.rights) rn from table_user u left join Table_UserPermissions p on u.userid = p.userid ) t cross apply sys.columns as C where C.object_id = object_id('Table_UserPermissions') and C.name not in ('UserId') group by c.name, t.rn order by t.rn FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select * from ( select userid, username, value, col + ''_''+ cast(rn as varchar(10)) col from ( select u.userid, u.username, p.permissionid, p.rights, row_number() over(partition by u.userid order by p.permissionid, p.rights) rn from table_user u left join Table_UserPermissions p on u.userid = p.userid ) src unpivot ( value for col in (permissionid, rights) ) unpiv ) x1 pivot ( max(value) for col in ('+ @colspivot +') ) p order by userid' exec(@query) 

Voir SQL Fiddle avec démo

Le résultat pour les deux est:

 | USERID | USERNAME | PERMISSIONID_1 | RIGHTS_1 | PERMISSIONID_2 | RIGHTS_2 | PERMISSIONID_3 | RIGHTS_3 | --------------------------------------------------------------------------------------------------------- | 1 | John | 10 | 1 | 11 | 2 | 12 | 3 | | 2 | Joe | 11 | 2 | 12 | 3 | (null) | (null) | | 3 | James | 10 | 2 | (null) | (null) | (null) | (null) |