How to generate data driven columns in a SQL query using PIVOT

Question

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.

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

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.

Through use of dynamic SQL: Here's a [prior SO question](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) showing how Just be sure your groups are sanatized inputs or you could allow SQL injection! — xQbert, Dec 29 '15 at 18:57
Ahhh, another dynamic pivot question. When is Microsoft going to add this to SQL? There really are no good solutions, since they all require dynamic SQL, so may God have mercy on your soul. — Stan Shaw, Dec 29 '15 at 19:13

score 1 · Accepted Answer · answered Dec 29 '15 at 19:30

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

How to generate data driven columns in a SQL query using PIVOT

1 Answers1