This is an old question but I'm posting this solution to counter Solution 1 which uses a CURSOR - not a recommended approach (see
SQL Server DO's and DONT's[
^])
The following solution uses constructs available from SQL 2005 - Common Table Expressions and Pivot. Some CP articles that may be useful on those topics:
Common Table Expressions(CTE) in SQL SERVER 2008[
^]
Simple Way To Use Pivot In SQL Query[
^]
My sample data:
create table t1
(
code int,
dt date
)
INSERT t1 values
(101,'12-FEB-2014'),
(101,'13-FEB-2014'),
(101,'17-FEB-2014'),
(102,'5-FEB-2014'),
(102,'11-FEB-2014'),
(110,'2-FEB-2014'),
(110,'5-FEB-2014'),
(110,'25-FEB-2014');
The query
WITH CTE1(code, dt, datenumber)
AS
(
SELECT [code],dt,
'dt' + CAST(ROW_NUMBER() over(PARTITION BY code
ORDER BY code, dt ASC) AS VARCHAR) datenumber
FROM T1
)
select code, dt, datenumber
into ##t2 from CTE1
SELECT *
FROM (
SELECT
code,
dt,
datenumber
FROM ##t2
) as s
PIVOT
(
MAX(dt)
FOR [datenumber] IN ([dt1], [dt2], [dt3], [dt4])
)AS p
Results:
Code dt1 dt2 dt3 dt4
101 2014-02-12 2014-02-13 2014-02-17 NULL
102 2014-02-05 2014-02-11 NULL NULL
110 2014-02-02 2014-02-05 2014-02-25 NULL