如何使用PIVOT在SQL查询中生成数据驱动列[重复]

This question already has an answer here:

这个问题在这里已有答案:

• SQL Server dynamic PIVOT query? 6 answers

SQL Server动态PIVOT查询? 6个答案

I am trying to understand how to use PIVOT in SQL. Ideally I'd like a query with dynamic rows and dynamic columns. I would post a code sample of what I've got so far, but I'm afraid I don't have anything working.

我试图了解如何在SQL中使用PIVOT。理想情况下,我想要一个包含动态行和动态列的查询。我会发布到目前为止我所得到的代码示例,但我担心我没有任何工作。

I have 2 primary entities and an M:N map such as this.

我有2个主要实体和一个M:N地图,例如这个。

DECLARE @people TABLE(id INT NOT NULL PRIMARY KEY IDENTITY(1,1),name VARCHAR(MAX) NOT NULL)
INSERT INTO @people (name) VALUES ('Joe'),('Sally'),('Mary')

DECLARE @group TABLE(id INT NOT NULL PRIMARY KEY IDENTITY(1,1),name VARCHAR(MAX) NOT NULL)
INSERT INTO @group (name) VALUES ('Cool Kids'),('Band Camp'),('Chess Club')

DECLARE @map TABLE(peopleId INT, groupId INT)
INSERT INTO @map (peopleId,groupId) VALUES (1,1),(1,3),(2,3),(3,2)

Ideally I'd like a result that puts one entity as rows, another as columns, and some piece of data (a simple boolean or sum) at the intersection.

理想情况下,我想要一个结果,将一个实体作为行,另一个作为列,并在交集处放置一些数据(一个简单的布尔或求和)。

       Cool Kids  Band Camp   Chess Club
Joe    TRUE       FALSE       TRUE
Sally  FALSE      FALSE       TRUE
Mary   FALSE      TRUE        TRUE

Most examples I've seen seem to suggest you require knowing which @groups you are querying for in advance, which I don't.

我见过的大多数例子似乎都暗示你需要提前知道你要查询哪些@groups,我不知道。

1 个解决方案

#1

This probably gets asked a few times a day, and the answer is always dynamic pivot.

这可能每天被问到几次,答案总是动态的。

DECLARE @sql VARCHAR(MAX),
        @cols VARCHAR(MAX)

-- define the columns that will be dynamic
SELECT @cols = COALESCE(@cols + ',', '') + QUOTENAME(name)
FROM [group] g

-- build the sql and insert the dynamic column names in your pivot statement
SET @sql = '
    SELECT *
    FROM (
        SELECT  p.name personName, g.name groupName
        FROM  people p 
        LEFT JOIN map m on p.id = m.peopleId
        LEFT JOIN [group] g on m.groupId = g.id
    ) t
    PIVOT 
    (
        COUNT(groupName)
        FOR groupName IN (' + @cols + ')
    ) p
'
EXEC(@sql)

DEMO