PIVOT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT

Author

Topic

Wannabe67
Starting Member

14 Posts

Posted - 2009-11-25 : 15:25:55

I have a table that holds information about people that have to take physical fitness tests. They take at least three of the six events. Each event is stored as a separate record in my table. All events have to be taken on the same day. A person can do this multiple times. So, today, they can get scored on three events, then tomorrow they can get scored on the same three events or another three. so a table of data can look like this.


Not complete structure, but hopefully shows what I am working with (complete structure below)
userId  date   event         score
1       11/24  pushups       73
1       11/24  situps        85
1       11/24  run           81
1       11/25  pushups       100
1       11/25  situps        100
1       11/25  walk          100

I need the data for this user to be made into one row. Also, a user can be listed as many as 4 times, so for example, I would need a column for pushups1, pushups2, pushups3, pushups4, if they have taken a test on 4 different occasions.

I hope this is clear enough. Below is the table and data that I have setup in my database.

Any help will be greatly appreciated, as I have no clue as to what I am doing here.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_PTRecord](
	[PTRecordId] [int] IDENTITY(1,1) NOT NULL,
	[StudentClassId] [int] NOT NULL,
	[PTRecordTypeId] [int] NOT NULL,
	[DateTaken] [smalldatetime] NOT NULL CONSTRAINT [DF_PTRecord_DateTaken]  DEFAULT (getdate()),
	[HeightWeightId] [int] NULL,
	[TotalScore] [varchar](3) NOT NULL CONSTRAINT [DF_PTRecord_TotalScore]  DEFAULT ((0)),
 CONSTRAINT [PK_PTRecord] PRIMARY KEY CLUSTERED 
(
	[PTRecordId] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_PTRecord]  WITH CHECK ADD  CONSTRAINT [FK_tbl_PTRecord_tbl_PTRecord] FOREIGN KEY([PTRecordId])
REFERENCES [dbo].[tbl_PTRecord] ([PTRecordId])
GO
ALTER TABLE [dbo].[tbl_PTRecord] CHECK CONSTRAINT [FK_tbl_PTRecord_tbl_PTRecord]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_PTScores](
	[PTScoreId] [int] IDENTITY(1,1) NOT NULL,
	[PTRecordId] [int] NOT NULL,
	[PTEventId] [int] NOT NULL,
	[Score] [int] NOT NULL CONSTRAINT [DF_PTScores_Score]  DEFAULT ((0)),
	[PointsEarned] [int] NULL,
	[Grader] [varchar](3) NULL,
	[PhysicalProfile] [bit] NOT NULL CONSTRAINT [DF_tbl_PTScores_Profile]  DEFAULT ((0)),
 CONSTRAINT [PK_PTScores] PRIMARY KEY CLUSTERED 
(
	[PTScoreId] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_PTScores]  WITH CHECK ADD  CONSTRAINT [FK_tbl_PTScores_tbl_PTRecord] FOREIGN KEY([PTRecordId])
REFERENCES [dbo].[tbl_PTRecord] ([PTRecordId])
GO
ALTER TABLE [dbo].[tbl_PTScores] CHECK CONSTRAINT [FK_tbl_PTScores_tbl_PTRecord]

GO
/****** Object:  Table [dbo].[tbl_PTEvents]    Script Date: 11/25/2009 13:18:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_PTEvents](
	[PTEventId] [int] IDENTITY(1,1) NOT NULL,
	[Event] [varchar](10) NOT NULL,
	[EventType] [varchar](50) NOT NULL,
 CONSTRAINT [PK_PTEvents] PRIMARY KEY CLUSTERED 
(
	[PTEventId] 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
SET ANSI_PADDING OFF

INSERT tbl_PTEvents(PTEventId,Event,EventType) VALUES('1','Push-Ups','Repitions')
INSERT tbl_PTEvents(PTEventId,Event,EventType) VALUES('2','Sit-Ups','Repitions')
INSERT tbl_PTEvents(PTEventId,Event,EventType) VALUES('3','Run','Time')
INSERT tbl_PTEvents(PTEventId,Event,EventType) VALUES('4','Walk','Time')
INSERT tbl_PTEvents(PTEventId,Event,EventType) VALUES('5','Swim','Time')
INSERT tbl_PTEvents(PTEventId,Event,EventType) VALUES('6','Bike','Time')

INSERT INTO tbl_PTRecord (StudentClassId, PTRecordTypeId, DateTaken, HeightWeightId, TotalScore) VALUES (1, 1, '11/24/09', 1, 180)
INSERT INTO tbl_PTRecord (StudentClassId, PTRecordTypeId, DateTaken, HeightWeightId, TotalScore) VALUES (1, 2, '11/25/09', 1, 300)
INSERT INTO tbl_PTRecord (StudentClassId, PTRecordTypeId, DateTaken, HeightWeightId, TotalScore) VALUES (2, 1, '11/24/09', 1, 279)


INSERT tbl_PTScores(PTScoreId,PTRecordId,PTEventId,Score,PointsEarned,Grader,PhysicalProfile) VALUES('415','1','1','71','94','mc','0')
INSERT tbl_PTScores(PTScoreId,PTRecordId,PTEventId,Score,PointsEarned,Grader,PhysicalProfile) VALUES('416','1','4','671','100','mc','0')
INSERT tbl_PTScores(PTScoreId,PTRecordId,PTEventId,Score,PointsEarned,Grader,PhysicalProfile) VALUES('417','1','2','71','88','mc','0')
INSERT tbl_PTScores(PTScoreId,PTRecordId,PTEventId,Score,PointsEarned,Grader,PhysicalProfile) VALUES('418','2','1','0','0','mc','1')
INSERT tbl_PTScores(PTScoreId,PTRecordId,PTEventId,Score,PointsEarned,Grader,PhysicalProfile) VALUES('419','2','4','61','100','mc','0')
INSERT tbl_PTScores(PTScoreId,PTRecordId,PTEventId,Score,PointsEarned,Grader,PhysicalProfile) VALUES('420','2','2','0','0','mc','1')
INSERT tbl_PTScores(PTScoreId,PTRecordId,PTEventId,Score,PointsEarned,Grader,PhysicalProfile) VALUES('421','3','1','78','100','mc','0')
INSERT tbl_PTScores(PTScoreId,PTRecordId,PTEventId,Score,PointsEarned,Grader,PhysicalProfile) VALUES('422','3','4','756','100','mc','0')
INSERT tbl_PTScores(PTScoreId,PTRecordId,PTEventId,Score,PointsEarned,Grader,PhysicalProfile) VALUES('423','3','2','92','100','mc','0')

Wannabe67
Starting Member

14 Posts

Posted - 2009-11-25 : 15:51:30

To follow up to my earlier post, I have figured out how to do part of what I want with a pivot, but it only pivots for one group of data (one date). I need to be able to pivot for up to four sets of data (up to four different dates). Is there a way to do that with a pivot?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 01:29:06

Can you post the expected result?

Madhivanan

Failing to plan is Planning to fail

Wannabe67
Starting Member

14 Posts

Posted - 2009-11-26 : 10:22:09

My expected result would look like below. A person can take this test up to four times, but may only take it once.


lastname  firstname    Pushups1   Situps1  Run1  Pushups2  Situps2 Run2 etc.
Smith     John         77         81       686

So, if a person took a test only once, then I would just have pushups1, situps1, and run1 filled out and the other three entries would be blank. if the next person took it twice, then pushups1, situps1, run1, pushups2, situps2, run2 would be filled out and the rest blank.

Thanks for looking into this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 01:40:16

See if this helps
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources