CODEWITHSUNDEEP
sql-serverview
Pete
Pete

Reputation: 171

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

I'm trying to create a view from a table on MS SQL Server 2005, using some of its data to populate the columns of the view... a simplified example would be a table keeping records of the stage a user has unlocked, something 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 for a view like (if there was 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 are the columns of the new view.

I've have done something similar before on Access, but don´t know if it's possible on SQL Server.

Upvotes: 0

Views: 164

Answers (1)

Taryn
Taryn

Reputation: 247650

There are several ways that you can pivot the data in SQL Server which converts the dats from rows into columns.

You can apply an aggregate function with a CASE expression:

select userid,
  max(case when stage=1 then date end) Stage1,
  max(case when stage=2 then date end) Stage2,
  max(case when stage=3 then date end) Stage3,
  max(case when stage=4 then date end) Stage4
from dbo.yourtable
group by userid;

See SQL Fiddle with Demo

Or you can use the PIVOT function:

select userid, Stage1, Stage2, Stage3, Stage4
from
(
  select userid, 'Stage'+cast(stage as varchar(10)) Stage, date
  from dbo.yourtable
) d
pivot
(
  max(date)
  for stage in (Stage1, Stage2, Stage3, Stage4)
) piv;

See SQL Fiddle with Demo. The result from both will 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) |

Upvotes: 4

Related Questions