根据其他表中的值添加列

crjunk 发表于 Dev

垃圾

我有一个查询拉用户信息。

SELECT UserID, CompanyID, FName, LName FROM tblUsers  Where OrgType = 'business'

我有另一个查询拉用户信息。

SELECT u.UserID, u.AccessID, t.AccessName FROM tblUserAccess as u Inner Join tblAccessType as t on u.AccessID = t.AccessID

最后，还有第三个查询可以提取acces类型。

SELECT AccessID, AccessName, OrgType FROM tblAccessType WHERE OrgType = 'business'

tblAccessType中可以有X个不同的访问类型。不同的访问类型将随着时间的流逝而增长。同样，用户可以具有X个访问类型。

如何编写查询，该查询将以以下格式返回数据？

UserID CompanyID FName LName SALES MARKET ADMIN FIN

100    200       Jane  Doe     *                 *

101    200       John  Doe                   *

120    205       Mary  Smith   *             *

121    205       Mark  Smith   *                  *

他是下面的示例数据。

tblUsers

UserID CompanyID   FName LName  OrgType

100     200        Jane  Doe    business

101     200        John  Doe    business

120     205        Mary  Smith  business 

121     205        Mark  Smith  business

122     259        Fred  Wilson charity

tblUserAccess

UserID AccessID 

100    1

100    4

101    3

120    1

120    3

121    1

121    4 


tblAccessType

AccessID AccessName OrgType

1        SALES      business

2        MARKET     business

3        ADMIN      business 

4        FIN        business 

5        NOTAX      charity

6        SECURITY   government

戴夫·卡鲁姆（Dave Cullum）

您需要的是PIVOT...我具有动态功能，PIVOT因此如果您添加new AccessTypes，它们将被自动添加。

如果您希望省略NULL数据，则将更改''*'' AS AccessVal为1 AS AccessVal，然后将更MAX([AccessVal])改为COUNT([AccessVal])-这将为您提供1/0而不是* / NULL

IF OBJECT_ID('tempdb..#tblUsers') IS NOT NULL DROP TABLE #tblUsers
IF OBJECT_ID('tempdb..#tblUserAccess') IS NOT NULL DROP TABLE #tblUserAccess 
IF OBJECT_ID('tempdb..#tblAccessType') IS NOT NULL DROP TABLE #tblAccessType 
IF OBJECT_ID('tempdb..##tblAccessPivot') IS NOT NULL DROP TABLE ##tblAccessPivot 

CREATE TABLE #tblUsers (UserID INT, CompanyID INT, FName VARCHAR(255), LName VARCHAR(255), OrgType VARCHAR(255))
CREATE TABLE #tblUserAccess (UserID INT, AccessID INT)
CREATE TABLE #tblAccessType (AccessID INT, AccessName VARCHAR(255), OrgType VARCHAR(255))

INSERT INTO #tblUsers (UserID, CompanyID, FName, LName, OrgType) 
VALUES (100, 200, 'Jane', 'Doe', 'Business'), 
       (101, 200, 'John', 'Doe', 'Business'), 
       (120, 205, 'Mary', 'Smith', 'Business'),
       (121, 205, 'Mark', 'Smith', 'Business'),
       (122, 259, 'Fred', 'Wilson', 'Charity')

INSERT INTO #tblAccessType (AccessID, AccessName, OrgType)
VALUES (1, 'Sales', 'Business'), 
       (2, 'Market', 'Business'), 
       (3, 'Admin', 'Business'), 
       (4, 'Fin', 'Business'),
       (5, 'NoTax', 'Charity'),
       (6, 'Security', 'Government')

INSERT INTO #tblUserAccess (UserID, AccessID)
VALUES (100,1),(100,4), -- JANE
       (101,3), -- JOHN
       (120,1),(120,3), -- MARY
       (121,1),(121,4) -- MARK

DECLARE @cols AS NVARCHAR(MAX), 
        @sql  AS NVARCHAR(MAX)

SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT AccessName AS y FROM #tblAccessType) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');        

SET @sql = 'SELECT UserID, CompanyID, Fname, LName, '+@cols+'
            INTO ##tblAccessPivot
            FROM (SELECT U.UserID, U.CompanyID, U.Fname, U.LName, AT.AccessName, ''*'' AS AccessVal
                  FROM #tblUsers U
                  LEFT OUTER JOIN #tblUserAccess UA ON U.UserID=UA.UserID
                  LEFT OUTER JOIN #tblAccessType AT ON UA.AccessID=AT.AccessID) SUB
            PIVOT (MAX([AccessVal]) FOR AccessName IN ('+@cols+')) AS P'
PRINT @SQL
EXEC (@SQL)

SELECT *
FROM ##tblAccessPivot