PigsIncorporated PigsIncorporated - 2 months ago 12
SQL Question

SQL query with pivot tables?

I'm trying to wrap by brain around how to use pivot tables for a query I need. I have 3 database tables. Showing relevant columns:

TableA: Columns = pName
TableB: Columns = GroupName, GroupID
TableC: Columns = pName, GroupID

TableA contains a list of names (John, Joe, Jack, Jane)

TableB contains a list of groups with an ID#. (Soccer|1, Hockey|2, Basketball|3)

TableC contains a list of the names and the group they belong to (John|1, John|3, Joe|2, Jack|1, Jack|2, Jack|3, Jane|3)


I need to create a matrix like grid view using a SQL query that would return a list of all the names from TableA (Y-axis) and a list of all the possible groups (X-axis). The cell values would be either true or false if they belong to the group.

Any help would be appreciated. I couldn't quite find an existing answer that helped.

Answer

You might try it like this

Here I set up a MCVE, please try to create this in your next question yourself...

DECLARE @Name TABLE (pName VARCHAR(100));
INSERT INTO @Name VALUES('John'),('Joe'),('Jack'),('Jane');
DECLARE @Group TABLE(gName VARCHAR(100),gID INT);
INSERT INTO @Group VALUES ('Soccer',1),('Hockey',2),('Basketball',3);
DECLARE @map TABLE(pName VARCHAR(100),gID INT);
INSERT INTO @map VALUES
  ('John',1),('John',3)
 ,('Joe',2)
 ,('Jack',1),('Jack',2),('Jack',3)
 ,('Jane',3);

This quer will collect the values and perform PIVOT

 SELECT p.*
 FROM
 (
     SELECT n.pName
           ,g.gName
           ,'x' AS IsInGroup
     FROM @map AS m
     INNER JOIN @Name AS n ON m.pName=n.pName
     INNER JOIN @Group AS g ON m.gID=g.gID
 ) AS x
 PIVOT
 (
    MAX(IsInGroup) FOR gName IN(Soccer,Hockey,Basketball)
 ) as p 

This is the result.

pName   Soccer  Hockey  Basketball
Jack    x       x       x
Jane    NULL    NULL    x
Joe     NULL    x       NULL
John    x       NULL    x

Some hints:

  • You might use 1 and 0 instead of x as SQL Server does not know a real boolean
  • You should add a pID to your names. Never join tables on real data (unless it is something unique and unchangeable [which means never acutally!!!])

UPDATE dynamic SQL (thx to @djlauk)

If you want a query which deals with any amount of groups you have to to this dynamically. But please be aware, that you loose the chance to use this in ad-hoc-SQL like in VIEW or inline TVF, which is quite a big backdraw...

CREATE TABLE #Name(pName VARCHAR(100));
INSERT INTO #Name VALUES('John'),('Joe'),('Jack'),('Jane');
CREATE TABLE #Group(gName VARCHAR(100),gID INT);
INSERT INTO #Group VALUES ('Soccer',1),('Hockey',2),('Basketball',3);
CREATE TABLE #map(pName VARCHAR(100),gID INT);
INSERT INTO #map VALUES
  ('John',1),('John',3)
 ,('Joe',2)
 ,('Jack',1),('Jack',2),('Jack',3)
 ,('Jane',3);

DECLARE @ListOfGroups VARCHAR(MAX)=
(
    STUFF
    (
        (
        SELECT DISTINCT ',' + QUOTENAME(gName) 
        FROM #Group
        FOR XML PATH('')
        ),1,1,''
    )
);

DECLARE @sql VARCHAR(MAX)=
(
 'SELECT p.*
 FROM
 (
     SELECT n.pName
           ,g.gName
           ,''x'' AS IsInGroup
     FROM #map AS m
     INNER JOIN #Name AS n ON m.pName=n.pName
     INNER JOIN #Group AS g ON m.gID=g.gID
 ) AS x
 PIVOT
 (
    MAX(IsInGroup) FOR gName IN(' +  @ListOfGroups + ')
 ) as p');

 EXEC(@sql); 
 GO

 DROP TABLE #map;
 DROP TABLE #Group;
 DROP TABLE #Name;