Generating pivot table dynamically

--Create Subjects table
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Subjects]') AND type in (N'U'))
DROP TABLE [dbo].[Subjects]
GO
CREATE TABLE [dbo].[Subjects](
	[pkey] [int] IDENTITY(1,1) NOT NULL,
	[Subject] [varchar](50) NULL,
	[Day] [int] NULL,
 CONSTRAINT [PK_Subjects] PRIMARY KEY CLUSTERED
(
	[pkey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--Create Days table
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Days]') AND type in (N'U'))
DROP TABLE [dbo].[Days]
GO
CREATE TABLE [dbo].[Days](
	[pkey] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
 CONSTRAINT [PK_Days] PRIMARY KEY CLUSTERED
(
	[pkey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--Insert values into Subjects table
INSERT INTO Subjects(Subject,Day) VALUES('Litrature',1)
GO
INSERT INTO Subjects(Subject,Day) VALUES('History',3)
GO
INSERT INTO Subjects(Subject,Day) VALUES('Geography',5)
GO
INSERT INTO Subjects(Subject,Day) VALUES('Mathematics',2)
GO

--Insert values into Days table
INSERT INTO Days(Name) VALUES('Monday')
GO
INSERT INTO Days(Name) VALUES('Tuesday')
GO
INSERT INTO Days(Name) VALUES('Wednesday')
GO
INSERT INTO Days(Name) VALUES('Thursday')
GO
INSERT INTO Days(Name) VALUES('Friday')
GO
INSERT INTO Days(Name) VALUES('Saturday')
GO
INSERT INTO Days(Name) VALUES('Sunday')
GO

--Dynamically generate Pivot table
DECLARE @cols NVARCHAR(2000)
 SELECT  @cols = STUFF((  SELECT  TOP 100 PERCENT
         ', ' + cast(t1.subject AS NVARCHAR)
       FROM    Subjects AS t1
       FOR XML PATH('')
        ), 1, 2, '') + ''

 DECLARE @query NVARCHAR(4000)
 SET @query = N'SELECT Name,pkey, '+
 @cols +'
 FROM
 (SELECT t2.Name, t1.Subject,t2.pkey, 1 AS [access] FROM Days AS t2 LEFT JOIN Subjects AS t1 ON t2.PKey = t1.Day) p
 PIVOT
 (
 Sum([access])
 FOR subject IN
 ( '+
 @cols +' )
 ) AS pvt;'

EXEC(@query)

In the above query there are two tables Subjects and Days, one contains entries for subjects and other for holding days of week

The generated pivot tables is like a time table which contains value 1 for the day where lecture of subjects is there otherwise NULL. This is rather a simple query more complex pivot table are constructed as per requirements. Pivot tables are quite useful in showing data in more user-friendly way.

Advertisements

About Utkarsh Puranik

Software Engineer by Profession, Gamer by Nature, Techy by Attitude and a Good Person at Heart
This entry was posted in Technology and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s