PIVOT table

calvinkwoo3000 · July 3, 2019, 9:20am

Hi SQL Expert Team,

I had a query which need to show all Elective as column header and assign 'Yes' value if selected.

I need to query and display result as below:

Hope expertise can help on my query.
Thanks in advanced

ScottPletcher · July 3, 2019, 1:07pm

Please provide directly usable data which is CREATE TABLE and INSERT statements. We can't write code against images.

calvinkwoo3000 · July 4, 2019, 3:56am

CREATE TABLE [dbo].[ClassMaster](
[ID] [int] NOT NULL,
[ClassName] [nvarchar](50) NULL,
CONSTRAINT [PK_ClassMaster] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ElectiveMaster](
[ID] [int] NOT NULL,
[ElectiveName] [nvarchar](50) NULL,
CONSTRAINT [PK_ElectiveMaster] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ClassElectiveTable](
	[ID] [nchar](10) NOT NULL,
	[ClassID] [int] NULL,
	[ElectiveID] [int] NULL,
 CONSTRAINT [PK_ClassElectiveTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO dbo.ElectiveMaster (ID, ElectiveName)
VALUES (666, 'Elective Name 4')
GO

INSERT INTO dbo.ElectiveMaster (ID, ElectiveName)
VALUES (777, 'Elective Name 1')
GO

INSERT INTO dbo.ElectiveMaster (ID, ElectiveName)
VALUES (888, 'Elective Name 2')
GO

INSERT INTO dbo.ElectiveMaster (ID, ElectiveName)
VALUES (999, 'Elective Name 3')
GO

INSERT INTO dbo.ClassMaster (ID, ClassName)
VALUES (1, 'Class Name 1')
GO

INSERT INTO dbo.ClassMaster (ID, ClassName)
VALUES (2, 'Class Name 2')
GO

INSERT INTO dbo.ClassMaster (ID, ClassName)
VALUES (3, 'Class Name 3')
GO

INSERT INTO dbo.ClassMaster (ID, ClassName)
VALUES (4, 'Class Name 4')
GO

INSERT INTO dbo.ClassElectiveTable (ID, ClassID, ElectiveID)
VALUES ('a', 1, 777)
GO

INSERT INTO dbo.ClassElectiveTable (ID, ClassID, ElectiveID)
VALUES ('b', 1, 888)
GO

INSERT INTO dbo.ClassElectiveTable (ID, ClassID, ElectiveID)
VALUES ('c', 2, 777)
GO

INSERT INTO dbo.ClassElectiveTable (ID, ClassID, ElectiveID)
VALUES ('d', 3, 777)
GO

INSERT INTO dbo.ClassElectiveTable (ID, ClassID, ElectiveID)
VALUES ('e', 3, 888)
GO

INSERT INTO dbo.ClassElectiveTable (ID, ClassID, ElectiveID)
VALUES ('f', 3, 666)
GO

calvinkwoo3000 · July 4, 2019, 10:44am

Hi @ScottPletcher ,

Please find the the directly usable data script as provided

jeffw8713 · July 4, 2019, 4:25pm

This is a traditional cross-tab query. I have included a working example with sample tables (your sample tables are real tables which I won't use on my systems - and is missing the class elective table).

Declare @classMaster Table (ID int, ClassName varchar(50));
Declare @electiveMaster Table (ID int, ElectiveName varchar(50));
Declare @classElective Table (ID char(1), ClassID int, ElectiveID int);

 Insert Into @classMaster (ID, ClassName)
 Values (1, 'Class Name 1')
      , (2, 'Class Name 2')
      , (3, 'Class Name 3')
      , (4, 'Class Name 4');

 Insert Into @electiveMaster (ID, ElectiveName)
 Values (777, 'Elective Name 1')
      , (888, 'Elective Name 2')
      , (999, 'Elective Name 3')
      , (666, 'Elective Name 4');

 Insert Into @classElective (ID, ClassID, ElectiveID)
 Values ('a', 1, 777)
      , ('b', 1, 888)
      , ('c', 2, 777)
      , ('d', 3, 777)
      , ('e', 3, 888)
      , ('f', 3, 666);

 Select * From @classMaster cm;
 Select * From @electiveMaster em;
 Select * From @classElective ce;

 Select cm.ClassName
      , 'Elective Name 1' = max(iif(em.ID = 777, 'Yes', 'No'))
      , 'Elective Name 2' = max(iif(em.ID = 888, 'Yes', 'No'))
      , 'Elective Name 3' = max(iif(em.ID = 999, 'Yes', 'No'))
      , 'Elective Name 4' = max(iif(em.ID = 666, 'Yes', 'No'))
   From @classMaster            cm
   Left Join @classElective     ce On ce.ClassID = cm.ID
   Left Join @electiveMaster    em On em.ID = ce.ElectiveID
  Group By
        cm.ClassName;

calvinkwoo3000 · July 5, 2019, 1:47am

Hi @jeffw8713,

My apologize, i had add on the script to missing class elective table.
Thank for your script but the Elective master data will increase where user can add more new elective.
if possible to did it by dynamic instead of hardcoded?

harishgg1 · July 5, 2019, 8:52am

hi

i am also trying to do this

in a different way

the only thing remaining is to PIVOT "ElectiveClass 1 to 4"
using dynamic SQL

dynamic SQL example ....

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

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

drop create data ...

DROP TABLE [dbo].[classmaster] 

CREATE TABLE [dbo].[classmaster] 
  ( 
     [id]        [INT] NOT NULL, 
     [classname] [NVARCHAR](50) NULL, 
     CONSTRAINT [PK_ClassMaster] PRIMARY KEY CLUSTERED ( [id] ASC )WITH ( 
     pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, 
     allow_row_locks = on, allow_page_locks = on) ON [PRIMARY] 
  ) 
ON [PRIMARY] 

DROP TABLE [dbo].[electivemaster] 

CREATE TABLE [dbo].[electivemaster] 
  ( 
     [id]           [INT] NOT NULL, 
     [electivename] [NVARCHAR](50) NULL, 
     CONSTRAINT [PK_ElectiveMaster] PRIMARY KEY CLUSTERED ( [id] ASC )WITH ( 
     pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, 
     allow_row_locks = on, allow_page_locks = on) ON [PRIMARY] 
  ) 
ON [PRIMARY] 

DROP TABLE dbo.electiveclass 

CREATE TABLE dbo.electiveclass 
  ( 
     id         VARCHAR(1), 
     classid    INT, 
     electiveid INT 
  ) 

INSERT INTO dbo.electiveclass 
SELECT 'a', 
       1, 
       777 

INSERT INTO dbo.electiveclass 
SELECT 'b', 
       1, 
       888 

INSERT INTO dbo.electiveclass 
SELECT 'c', 
       2, 
       777 

INSERT INTO dbo.electiveclass 
SELECT 'd', 
       3, 
       777 

INSERT INTO dbo.electiveclass 
SELECT 'e', 
       3, 
       888 

INSERT INTO dbo.electiveclass 
SELECT 'f', 
       3, 
       666 

INSERT INTO dbo.electivemaster 
            (id, 
             electivename) 
VALUES      (666, 
             'Elective Name 4') 

go 

INSERT INTO dbo.electivemaster 
            (id, 
             electivename) 
VALUES      (777, 
             'Elective Name 1') 

go 

INSERT INTO dbo.electivemaster 
            (id, 
             electivename) 
VALUES      (888, 
             'Elective Name 2') 

go 

INSERT INTO dbo.electivemaster 
            (id, 
             electivename) 
VALUES      (999, 
             'Elective Name 3') 

go 

INSERT INTO dbo.classmaster 
            (id, 
             classname) 
VALUES      (1, 
             'Class Name 1') 

go 

INSERT INTO dbo.classmaster 
            (id, 
             classname) 
VALUES      (2, 
             'Class Name 2') 

go 

INSERT INTO dbo.classmaster 
            (id, 
             classname) 
VALUES      (3, 
             'Class Name 3') 

go 

INSERT INTO dbo.classmaster 
            (id, 
             classname) 
VALUES      (4, 
             'Class Name 4') 

go 

SELECT * 
FROM   dbo.electivemaster 

SELECT * 
FROM   dbo.classmaster 

SELECT * 
FROM   dbo.electiveclass

SQL ...

; WITH cte 
     AS (SELECT a.* 
         FROM   (SELECT a.id AS classid, 
                        b.id AS electiveid 
                 FROM   classmaster a, 
                        dbo.electivemaster b) a 
         EXCEPT 
         SELECT classid, 
                electiveid 
         FROM   electiveclass), 
     cte1 
     AS (SELECT *, 
                'NO' AS ok 
         FROM   cte 
         UNION 
         SELECT classid, 
                electiveid, 
                'YES' 
         FROM   electiveclass) 
SELECT b.classname, 
       c.electivename, 
       a.ok 
FROM   cte1 a 
       JOIN classmaster b 
         ON a.classid = b.id 
       JOIN electivemaster c 
         ON a.electiveid = c.id 
ORDER  BY 1, 
          2 

go

result of sql

harishgg1 · July 5, 2019, 9:46am

hi

i did the dynamic SQL also
please see if every thing is OK

i hope it helps
i love any feedback thanks

dynamic SQL ...

 DROP TABLE #abc; 

WITH cte 
     AS (SELECT a.* 
         FROM   (SELECT a.id AS classid, 
                        b.id AS electiveid 
                 FROM   classmaster a, 
                        dbo.electivemaster b) a 
         EXCEPT 
         SELECT classid, 
                electiveid 
         FROM   electiveclass), 
     cte1 
     AS (SELECT *, 
                'NO' AS ok 
         FROM   cte 
         UNION 
         SELECT classid, 
                electiveid, 
                'YES' 
         FROM   electiveclass) 
SELECT b.classname, 
       c.electivename, 
       a.ok 
INTO   #abc 
FROM   cte1 a 
       JOIN classmaster b 
         ON a.classid = b.id 
       JOIN electivemaster c 
         ON a.electiveid = c.id 
ORDER  BY 1, 
          2 

go 

DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max) 

SELECT @cols = Stuff((SELECT ',' + Quotename(a.electivename) 
                      FROM   (SELECT DISTINCT electivename 
                              FROM   #abc) a 
                      ORDER  BY a.electivename 
                      FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, 
               '') 

SET @query = N'SELECT classname,' + @cols 
             + N' from               (                 select ClassName, ElectiveName ,ok                 from  #abc             ) x             pivot              (                 max(ok)                 for ElectiveName in (' 
             + @cols + N')             ) p ' 

EXEC Sp_executesql 
  @query;

harishgg1 · July 5, 2019, 10:54am

by the way

my whole SQL is dynamic

jeffw8713 · July 5, 2019, 2:59pm

You can build the query dynamically - using either cross-tab or pivot. Building the dynamic query isn't very difficult - but it does prevent you from using that query in some downstream systems.

If the result is something you want to build a report from - for example - it will not work because the report can only bind to the defined columns and changing the columns will break the report.

calvinkwoo3000 · July 5, 2019, 3:51pm

harishgg1:

DROP TABLE #abc; WITH cte AS (SELECT a.* FROM (SELECT a.id AS classid, b.id AS electiveid FROM classmaster a, dbo.electivemaster b) a EXCEPT SELECT classid, electiveid FROM electiveclass), cte1 AS (SELECT *, 'NO' AS ok FROM cte UNION SELECT classid, electiveid, 'YES' FROM electiveclass) SELECT b.classname, c.electivename, a.ok INTO #abc FROM cte1 a JOIN classmaster b ON a.classid = b.id JOIN electivemaster c ON a.electiveid = c.id ORDER BY 1, 2 go DECLARE @cols AS NVARCHAR(max), @query AS NVARCHAR(max) SELECT @cols = Stuff((SELECT ',' + Quotename(a.electivename) FROM (SELECT DISTINCT electivename FROM #abc) a ORDER BY a.electivename FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '') SET @query = N'SELECT classname,' + @cols + N' from ( select ClassName, ElectiveName ,ok from #abc ) x pivot ( max(ok) for ElectiveName in (' + @cols + N') ) p ' EXEC Sp_executesql @query;

Hi @harishgg1,
Bravo, you did a great job.
I will integrate it and try.

calvinkwoo3000 · July 5, 2019, 3:54pm

Hi @jeffw8713,

You are right, i will query the ElectiveMaster column and add new column dynamically from code behind.