T-SQL：跨表将列转换为列的查询？

SQL Server 2012.

SQL Server 2012。

Straight to the point, this is what I'm trying to do:

直截了当，这就是我要做的事情：

User table

用户表

id   username fullname
---- -------- ----------------------
1    test0001 Test User #1
2    test0002 Test User #2
3    test0003 Test User #3
4    test0004 Test User #4

Flags table

标志表

id  name       description
--- ---------- -------------------------------------------
1   isActive   true if user is currently active
2   isAdmin    true if user can do Admin things
3   canEdit    true if user can can edit

UserFlags table

UserFlags表

user flag
---- ----
1    1
1    2
1    3
2    1
2    3
3    1

(user = FK to user.id, flag = FK to flag.id)

Desired result

期望的结果

userId username isActive isAdmin canEdit
------ -------- -------- ------- -------
1      test0001 1        1       1
2      test0002 1        0       1
3      test0003 1        0       0
4      test0004 0        0       0

In short I want to convert each flag in the flags table into a column with the name field used as the column header. Then I want a row for each user, with a boolean in each column indicating whether they have that particular flag.

简而言之，我想将flags表中的每个标志转换为一个列，其中name字段用作列标题。然后我想为每个用户添加一行，每列中都有一个布尔值，表示它们是否具有该特定标志。

This needs to be able to adapt - e.g. if another flag is added, the result of the query should have another column with that flag's name as its title.

这需要能够适应 - 例如如果添加了另一个标志，则查询结果应该有另一列，该标志的名称作为标题。

I'd prefer to do this in a view, but I'd be OK with a table-valued function.

我更喜欢在视图中执行此操作，但我可以使用表值函数。

I haven't done anything like this before so I'm not even sure where to start - I can do a full join on the tables and end up with a row per user per flag, but I then want to fold that all down into a single row per user.

我之前没有做过这样的事情，所以我甚至不知道从哪里开始 - 我可以在桌子上进行完全连接，最后每个用户每个标志有一行，但我想把它全部折叠成每个用户一行。

EDIT One of the key points is "able to adapt" - the best scenario would be a query that automatically pulls in all currently defined flags from the flags table when building the response. Having to edit the query isn't necessarily bad, but consider the instance where an admin is allowed to add a new flag to the system. It's easy to INSERT a new flag, it's much harder to autonomously edit a stored query to reflect that. If that's simply not possible to do, then an explanation as to why would be helpful. Thanks!

编辑其中一个关键点是“能够适应” - 最佳方案是在构建响应时自动从flags表中提取所有当前定义的标志的查询。必须编辑查询并不一定是坏事，但考虑允许管理员向系统添加新标志的实例。插入新标志很容易，自动编辑存储的查询以反映这一点更加困难。如果这根本不可能做到，那么解释为什么会有所帮助。谢谢！

3 个解决方案

#1

You can use pivot as below:

你可以使用pivot如下：

Select * from (
    Select u.id, u.username, f.[name] from #user u
    left join  #userflags uf
    on uf.[user] = u.id
    left join #flags f
    on uf.flag = f.id
) a
pivot (count([name]) for [name] in ([isActive],[isAdmin],[canEdit])) p

Output as below:

输出如下：

+----+----------+----------+---------+---------+
| id | username | isActive | isAdmin | canEdit |
+----+----------+----------+---------+---------+
|  1 | test0001 |        1 |       1 |       1 |
|  2 | test0002 |        1 |       0 |       1 |
|  3 | test0003 |        1 |       0 |       0 |
|  4 | test0004 |        0 |       0 |       0 |
+----+----------+----------+---------+---------+

Demo

演示

Updated my query if you have dynamic list of flags as below:

如果您有动态的标志列表，请更新我的查询，如下所示：

Declare @cols1 varchar(max)
Declare @query nvarchar(max)

Select @cols1 = stuff((select distinct ','+QuoteName([name]) from #flags for xml path('')),1,1,'')

Select @query = '   Select * from (
        Select u.id, u.username, f.[name] from #user u
        left join  #userflags uf
        on uf.[user] = u.id
        left join #flags f
        on uf.flag = f.id
    ) a
    pivot (count([name]) for [name] in (' + @cols1 + ')) p '

Exec sp_executesql @query

#2

If you dont like pivot function like me; you can use the SUM IIF method like this

如果你不喜欢像我这样的枢轴功能;你可以像这样使用SUM IIF方法

SELECT u.id
    , username
    , SUM(IIF(flag = 1, 1, 0)) AS isActive
    , SUM(IIF(flag = 2, 1, 0)) AS isAdmin
    , SUM(IIF(flag = 3, 1, 0)) AS canEdit
FROM User u
LEFT JOIN UserFlags uf ON uf.[user] = u.id
GROUP BY u.id
    , username

#3

You can do this with a case statement and subquery or with a pivot. They both essentially do the same thing. This includes the DDL for the tables I made for testing.

您可以使用case语句和子查询或使用pivot来执行此操作。他们基本上都做同样的事情。这包括我为测试而制作的表的DDL。

declare @user_tbl table (
id int,
username nvarchar(max)
)

INSERT @user_tbl VALUES
(1,'test0001'),
(2,'test0002'),
(3,'test0003'),
(4,'test0004')


declare @userflags_tbl table(
userid int,
flag int
)

INSERT @userflags_tbl VALUES
(1,1),
(1,2),
(1,3),
(2,1),
(2,3),
(3,1)

declare @flags_tbl table(
id int
)

INSERT @flags_tbl VALUES
(1),
(2),
(3)

SELECT
    userid,
    username,
    MAX(isActive) AS isActive,
    MAX(isAdmin) AS isAdmin,
    MAX(canEdit) AS canEdit
FROM (
    SELECT
        user_tbl.id AS userid,
        user_tbl.username,
        CASE
            WHEN userflags_tbl.flag = 1 THEN 1
            ELSE 0
            END AS isActive,
        CASE
            WHEN userflags_tbl.flag = 2 THEN 1
            ELSE 0
            END AS isAdmin,
        CASE
            WHEN userflags_tbl.flag = 3 THEN 1
            ELSE 0
            END AS canEdit
    FROM @user_tbl user_tbl
    LEFT JOIN @userflags_tbl userflags_tbl ON
        user_tbl.id = userflags_tbl.userid
    LEFT JOIN @flags_tbl flags_tbl ON
        userflags_tbl.flag = flags_tbl.id
    )tbl
GROUP BY
    userid,
    username


SELECT
    userid,
    username,
    ISNULL([1],0) AS isActive,
    REPLACE(ISNULL([2],0),2,1) AS isAdmin,
    REPLACE(ISNULL([3],0),3,1) AS canEdit
FROM (
    SELECT
        user_tbl.id AS userid,
        user_tbl.username,
        userflags_tbl.flag
    FROM @user_tbl user_tbl
    JOIN @userflags_tbl userflags_tbl ON
        user_tbl.id = userflags_tbl.userid
    JOIN @flags_tbl flags_tbl ON
        userflags_tbl.flag = flags_tbl.id
    ) tbl
PIVOT (
    MAX(flag)
    FOR flag IN ([1],[2],[3])
    ) AS PivotTable

#1