x

Moving multiple values from one column into multiple columns in a single row

So I have a table (SQL 2008) with exam dates for members of an organization. However, we need to allow for multiple exams per year for each member. I will need to be able to map the current data to a structure like this:

Current Data:
MemberID ExamDate
A123 1/1/12
A123 6/6/12
B234 2/2/12
B234 7/7/12
B234 9/9/12
C345 3/3/12
D456 4/4/12
D456 8/8/12

Need to display like this:
MemberID ExamDate1 ExamDate2 ExamDate3
A123 1/1/12 6/6/12 NULL
B234 2/2/12 7/7/12 9/9/12
C345 3/3/12 NULL NULL
D456 4/4/12 8/8/12 NULL

My problem is there is no maximum number of exams that can be taken, so one member could have a dozen dates, while another could have only 1. Is there a way to select these dates so that the columns (ExamDateX) are named dynamically? Seems like it should be pretty simple, but it's not a straightforward Pivot, as far as I can tell.

Any help?

more ▼

asked Jan 29, 2013 at 05:28 PM in Default

avatar image

CrashAdder
30 1 1 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can do this but you have to use something nasty - a dynamic pivot....

 if object_id('MemberExams','U') is not null drop table MemberExams
 create table  MemberExams  ( MemberID varchar(10), ExamDate date)
 
 insert into MemberExams select 'A123', '1/1/12'
 insert into MemberExams select 'A123', '6/6/12'
 insert into MemberExams select 'B234', '2/2/12'
 insert into MemberExams select 'B234', '7/7/12'
 insert into MemberExams select 'B234', '9/9/12'
 insert into MemberExams select 'C345', '3/3/12'
 insert into MemberExams select 'D456', '4/4/12'
 insert into MemberExams select 'D456', '8/8/12'
 
 
 DECLARE @cols NVARCHAR(2000)
 SELECT  @cols = STUFF(( SELECT DISTINCT
                                 '],[ExamDate' + cast(row_number()over(partition by MemberID order by MemberID, ExamDate) as varchar)
                         FROM    MemberExams
                         FOR XML PATH('')
                       ), 1, 2, '') + ']'
 
 DECLARE @query NVARCHAR(4000)
 SET @query = N'SELECT MemberID, '+
 @cols +'
 FROM
 (SELECT  MemberID
  , ''ExamDate'' + cast(row_number()over(partition by MemberID order by MemberID, ExamDate) as varchar) as ColName
  , ExamDate
 FROM    MemberExams 
         ) p
 PIVOT
 (
 MAX(ExamDate)
 FOR ColName IN
 ( '+
 @cols +' )
 ) AS pvt
 ORDER BY MemberID;'
 
 execute (@query)


more ▼

answered Jan 29, 2013 at 09:01 PM

avatar image

Kev Riley ♦♦
66k 48 63 81

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Jan 31, 2013 at 10:42 PM

avatar image

sunil2711
276 9 11 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2176
x1089

asked: Jan 29, 2013 at 05:28 PM

Seen: 2301 times

Last Updated: Jan 31, 2013 at 10:42 PM

Copyright 2017 Redgate Software. Privacy Policy