I have the following tables:
ProfilRights:
ProfilRightId ProfilId AccessId Right
------------- -------- -------- --------
1 1 1 full
2 1 2 r-o
3 2 1 none
4 2 2 full
Profil table:
ProfilId ProfilName
-------- ----------
1 IT
2 Admin
Access table:
AcessId AcessName
-------- ----------
1 Employess
2 Clients
Translation table for Acess: AccessTranslation
AccessTransId AcessId TranslatedLabel Language
------------- -------- --------------- --------
1 1 Employees English
2 2 Clients English
3 1 EmployeesFR French
4 2 ClientsFR French
I want the results to be displayed in the following way:
EnglishAcess FrenchAcess IT Admin
------------- -------- ------ ------
Employees EmployeesFR full none
Clients ClientsFR r-o full
What I want is that, no matter how many profiles I have, these will be displayed as an additional column in the results set. Like if, say we add a new profil, Director, in the profil table and configure the rights in the ProfilRights table, this will display another column, on the right of Admin in the result set.
I have tried doing the below query but it does not work:
SELECT P.ProfilName, AT.TranslatedLabel, PR.Right
FROM ProfilRights PR
INNER JOIN Profil P ON PR.PofilId = P.PofilId
INNER JOIN Access A ON PR.AccessId = A.AccessId
INNER JOIN AccessTranslation [AT] ON A.AccessId = AT.AccessId;
However, this is returning me the results as below:
ProfilName TranslatedLabel Right
----------- -------------- ------
Admin EmployeesFR none
Admin ClientsFR full
Admin Employees none
Admin Clients full
IT EmployeesFR full
IT ClientsFR r-o
IT Employees full
IT Clients r-o
Please help on how to return the above results set?
CodePudding user response：
To solve your problem, i would use dynamic query PIVOT.
Here is an article that explains how to do it: Dynamic Pivot
CodePudding user response：
ANSWER
DECLARE @Counter INT;
DECLARE @totalCount INT;
-- Generate query for accessId and Language mapping temp resultSet
-- Get distinct Language values and store in a temp table
DECLARE @distinctLanguage TABLE (rn INT IDENTITY,Languages VARCHAR(20));
INSERT INTO @distinctLanguage(Languages) (SELECT DISTINCT Languages FROM Translation);
DECLARE @languageQuery NVARCHAR(MAX);
SET @totalCount = (SELECT count(*) FROM @distinctLanguage);
SET @Counter = @totalCount;
WHILE (@counter>0)
BEGIN
DECLARE @languageName NVARCHAR(20) = (SELECT Languages FROM @distinctLanguage WHERE rn=@counter);
IF @counter <> @totalCount SET @languageQuery = CONCAT(@languageQuery,' FULL OUTER JOIN ')
SET @languageQuery = CONCAT(@languageQuery,' (SELECT AccessId, TranslatedLabel AS ',@languageName,'Access FROM Translation WHERE Languages = ''',@languageName,''')','sq',@counter,' ');
IF @counter <> @totalCount SET @languageQuery = CONCAT(@languageQuery,' ON (sq',@totalCount,'.AccessId=sq',@counter,'.AccessId) ')
SET @counter = @counter - 1;
END
DECLARE @allLanguageString NVARCHAR(MAX);
SET @allLanguageString = (SELECT STRING_AGG(CONCAT(Languages,'Access'),',') FROM @distinctLanguage);
SET @languageQuery = CONCAT(' SELECT ',@allLanguageString,', sq',@totalCount,'.AccessId FROM ', @languageQuery);
-- Generate query for accessId and profileName mapping temp resultSet
-- Get distinct Profile values and store in a temp table
DECLARE @distinctProfiles TABLE (rn INT IDENTITY,ProfilName VARCHAR(20));
INSERT INTO @distinctProfiles(ProfilName) (SELECT DISTINCT ProfilName FROM Profil);
DECLARE @profileQuery NVARCHAR(MAX);
SET @totalCount = (SELECT count(*) FROM @distinctProfiles);
SET @Counter = @totalCount;
WHILE (@counter>0)
BEGIN
DECLARE @profileName NVARCHAR(20) = (SELECT ProfilName FROM @distinctProfiles WHERE rn=@counter);
IF @counter <> @totalCount SET @profileQuery = CONCAT(@profileQuery,' FULL OUTER JOIN ')
SET @profileQuery = CONCAT(@profileQuery,' (SELECT AccessId, Rights AS ',@profileName,' FROM @profilRights WHERE ProfilName = ''',@profileName,''')','sq',@counter,' ');
IF @counter <> @totalCount SET @profileQuery = CONCAT(@profileQuery,' ON (sq',@totalCount,'.AccessId=sq',@counter,'.AccessId) ')
SET @counter = @counter - 1;
END
DECLARE @allProfileString NVARCHAR(MAX);
SET @allProfileString = (SELECT STRING_AGG(ProfilName,',') FROM @distinctProfiles);
SET @profileQuery = CONCAT(' SELECT ',@allProfileString,', sq',@totalCount,'.AccessId FROM ', @profileQuery);
-- Construct final query
DECLARE @finalQuery NVARCHAR(MAX);
SET @finalQuery = CONCAT(' SELECT ' ,@allLanguageString, ', ',@allProfileString,' FROM ( ',@languageQuery,' ) a FULL OUTER JOIN ( ',@profileQuery,' ) b ON (a.AccessId=b.AccessId)');
-- Create a temp table for profile, accessId and rights mapping
DECLARE @profileRightMappingQuery NVARCHAR(MAX);
SET @profileRightMappingQuery= 'DECLARE @profilRights TABLE (ProfilName VARCHAR(20), AccessId INT, Rights VARCHAR(20)); INSERT INTO @profilRights(ProfilName, AccessId, Rights) SELECT ProfilName, AccessId,Rights FROM ProfilRights a LEFT JOIN Profil b ON(a.ProfilId=b.ProfilId);';
SET @finalQuery = CONCAT(@profileRightMappingQuery,' ',@finalQuery);
execute(@finalQuery);
EXPLANATION
DISCLAIMER
- In my opinion, following solution is sql-gymnastics. For the sake of simplicity, performance and maintenance, I would recommended to solve this problem using some code. First bring the request dataset in your application and construct the result there.
- This query is using
STRING_AGGfunction to convert rows of a table to comma-separated string. This function is available for version >=SQL Server 2017. In case you are using earlier version, you might need to do that manually. - Though I have tested the query, still some minor edge cases might have been left.
So that being said, let's start with the explanation.
Following is the static query that would give you the desired result.
DECLARE @profilRights TABLE (ProfilName VARCHAR(20), AccessId INT, Rights VARCHAR(20));
INSERT INTO @profilRights(ProfilName, AccessId, Rights)
SELECT ProfilName, AccessId,Rights
FROM ProfilRights a
LEFT JOIN Profil b ON(a.ProfilId=b.ProfilId);
SELECT EnglishAccess, FrenchAccess, IT, Admin
FROM
(
SELECT EnglishAccess,FrenchAccess,a.AccessId
FROM
(
SELECT AccessId,TranslatedLabel AS 'EnglishAccess' FROM Translation WHERE Languages='English'
)a FULL OUTER JOIN
(
SELECT AccessId,TranslatedLabel AS 'FrenchAccess' FROM Translation WHERE Languages='French'
)b ON (a.AccessId = b.AccessId)
) a FULL OUTER JOIN
(
SELECT a.AccessId, IT, Admin
FROM
(
SELECT AccessId, Rights AS 'IT' FROM @profilRights WHERE ProfilName = 'IT'
)a FULL OUTER JOIN
(
SELECT AccessId, Rights AS 'Admin' FROM @profilRights WHERE ProfilName = 'Admin'
) b ON (a.AccessId=b.AccessId)
) b ON (a.AccessId=b.AccessId)
But this query has hard-coded values, so your requirement is not completely fulfilled.
So we simply need to write code to make this query dynamic. To do that, I followed the below algorithm.
- Generate query for accessId and Language mapping temp resultSet.
- Generate query for accessId and profileName mapping temp resultSet.
- Construct final query.
- Create a temp table for profile, accessId and rights mapping.
- Execute the final query.
DDL Script
CREATE TABLE ProfilRights(ProfilRightId INT,ProfilId INT,AccessId INT, RIGHTS VARCHAR(10));
INSERT INTO ProfilRights(ProfilRightId, ProfilId ,AccessId , RIGHTS)
VALUES (1, 1,1,'full'),
(2, 1, 2,'r-o'),
(3, 2, 1,'none'),
(4, 2, 2,'full')
CREATE TABLE Profil(ProfilId INT, ProfilName VARCHAR(10));
INSERT INTO Profil(ProfilId , ProfilName)
VALUES (1, 'IT'),
(2, 'Admin')
CREATE TABLE Access(AccessId INT, AccessName VARCHAR(10));
INSERT INTO Access(AccessId , AccessName)
VALUES (1, 'Employess'),
(2, 'Clients')
CREATE TABLE Translation(AccessTransId INT, AccessId INT, TranslatedLabel VARCHAR(20), Languages VARCHAR(20));
INSERT INTO Translation(AccessTransId , AccessId , TranslatedLabel , Languages)
VALUES (1, 1,'Employess','English'),
(2, 2,'Clients','English'),
(3, 1,'EmployeesFR','French'),
(4, 2,'ClientsFR','French')