Pivot table in mysql

Status
Niet open voor verdere reacties.

hobielasseel

Gebruiker
Lid geworden
10 apr 2011
Berichten
142
Hallo allemaal

Momenteel probeer ik een inlogsysteem op te zetten, op basis van een bestaand systeem heb ik hiervoor onderstaande tabellenstructuur op poten gezet.
Opzet is vrij eenvoudig.

tblUsergroup is de centrale tabel: hier worden de verschillende usergroepen gedefinieerd
Via tblUserGroupMember kan een groep toegewezen worden aan meerdere users, en een user aan meerdere groepen (veel op veel relatie dus)​
Via tblUserGrant kan een privilege toegewezen worden aan meerdere groepen, en een groep aan meerdere priviliges (opnieuw veel op veel dus)​
2017-11-17 14_31_19-dbadmin.tlindeken.be _ localhost _ tlindeken_be _ phpMyAdmin 4.7.5.png

Wat ik wil bekomen is onderstaande resultaat
2017-11-17 14_52_56-TS06 (BEVMXTS06) - Remote Desktop Connection Manager v2.7.png

Met wat opzoekwerk kom ik al snel bij draaitabellen (pivot) uit. Momenteel geraak ik echter niet verder dan onderstaande query waarin nog geen pivot table is opgenomen
Code:
SELECT USE_VoorNaam, GROUP_CONCAT(UGR_Description), UPR_Description FROM `tblUser`
INNER JOIN tblUserGroupMember ON tblUser.USE_ID = tblUserGroupMember.UGM_UserId
INNER JOIN tblUserGroup ON tblUserGroupMember.UGM_UserGroupId = tblUserGroup.UGR_ID
LEFT JOIN tblUserGrant ON tblUserGroup.UGR_ID = tblUserGrant.GRA_UserGroupId
LEFT JOIN tblUserPrivilege ON tblUserGrant.GRA_UserPrivilegeId = tblUserPrivilege.UPR_ID
GROUP BY UPR_Description

Is er iemand die een beter alternatief ziet ? Dit is nog maar de select voor het opbouwen van het gebruikersbeheer. Er moet ook een updatescript komen.
Ter info: mijn applicatie wordt gemaakt met php en mysql, de voorbeeld applicatie is geschreven in C# met microsoft sql

Alvast bedankt
Groeten
Hobie
 
Jammer dat er niemand heeft geantwoord, toch geef ik hieronder mijn oplossing die ik na 5 dagen proberen en opnieuw proberen heb gevonden.

Code:
SET @sql_dynamic = (SELECT
	GROUP_CONCAT( DISTINCT
		CONCAT(
			'SUM( IF(UGR_ID = '
			, UGR_ID
			, ', 1, 0) ) AS '
			, UGR_Description
		)
	)
	FROM int__tblUserGroup
);

SET @sql = CONCAT('
SELECT 	
UPR_Description AS Privilege,' ,@sql_dynamic, '
	 
FROM int__tblUserGrant

LEFT JOIN int__tblUserGroup ON int__tblUserGrant.GRA_UserGroupId = int__tblUserGroup.UGR_ID
LEFT JOIN int__tblUserPrivilege ON int__tblUserGrant.GRA_UserPrivilegeId = int__tblUserPrivilege.UPR_ID

GROUP BY Privilege

-- Zelfde query maar om alle waarden te kunnen opvragen uit tblUserPrivilige, 
-- aangezien een full outer join niet beschikbaar is wordt de query een tweede keer uitgevoerd met een right join 
-- dan de gegevens van beide selects samen voegen met Union

UNION

SELECT 
    UPR_Description AS Privilege, 
    ',@sql_dynamic,'
FROM int__tblUserGrant

RIGHT JOIN int__tblUserGroup ON int__tblUserGrant.GRA_UserGroupId = int__tblUserGroup.UGR_ID
RIGHT JOIN int__tblUserPrivilege ON int__tblUserGrant.GRA_UserPrivilegeId = int__tblUserPrivilege.UPR_ID

GROUP BY Privilege

ORDER BY Privilege
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan