SQL-Server Creating a view with columns taken from a table -

i'm trying create view table on ms sql server 2005, using of data populate columns of view... simplified example table keeping records of stage user has unlocked, like:

userid | stage | date    1 | 1 | 1-1-2013   1 | 2 | 2-1-2013   2 | 1 | 1-1-2013   1 | 3 | 5-1-2013   2 | 2 | 3-1-2013   3 | 1 | 6-1-2013   3 | 2 | 8-1-2013   1 | 4 | 10-1-2013   3 | 3 | 12-1-2013 

and i'm looking view (if there 4 stages availables):

userid | stage 1 | stage 2 | stage 3 | stage 4    1 | 1-1-2013 | 2-1-2013 | 5-1-2013 | 10-1-2013    2 | 1-1-2013 | 3-1-2013 | |    3 | 6-1-2013 | 8-1-2013 | 12-1-2013 |  

the stages columns of new view.

i've have done similar before on access, don´t know if it's possible on sql server.

there several ways can pivot data in sql server converts dats rows columns.

you can apply aggregate function case expression:

select userid,   max(case when stage=1 date end) stage1,   max(case when stage=2 date end) stage2,   max(case when stage=3 date end) stage3,   max(case when stage=4 date end) stage4 dbo.yourtable group userid; 

see sql fiddle demo

or can use pivot function:

select userid, stage1, stage2, stage3, stage4 (   select userid, 'stage'+cast(stage varchar(10)) stage, date   dbo.yourtable ) d pivot (   max(date)   stage in (stage1, stage2, stage3, stage4) ) piv; 

see sql fiddle demo. result both be:

| userid |     stage1 |     stage2 |     stage3 |     stage4 | -------------------------------------------------------------- |      1 | 2013-01-01 | 2013-02-01 | 2013-05-01 | 2013-10-01 | |      2 | 2013-01-01 | 2013-03-01 |     (null) |     (null) | |      3 | 2013-06-01 | 2013-08-01 | 2013-12-01 |     (null) |