quote:

---

Originally posted by spirit1

take a look at:
http://www.sqlteam.com/item.asp?ItemID=2955

Go with the flow & have fun! Else fight the flow

---

set nocount on
declare @tb Table (ID int, Date_visit smalldatetime)
insert @tb
Select 101, '1/1/2001' union all
Select 101, '2/1/2003' union all
Select 101, '3/5/2004' union all
Select 102, '10/12/2003' union all
Select 102, '12/15/2004'

select	a.ID
	,b.Date_Visit Date_Visit_1st
	,a.Date_Visit
	,dateDiff(day, b.Date_Visit, a.Date_Visit) [DateVisitDiff]
From	@tb a
JOIN	(
	Select [id], min(Date_Visit) Date_Visit
	From	@tb
	Group by [id]
	) b ON a.[id] = b.[id]
		and a.Date_Visit <> b.Date_Visit
Order by 1,2,3

create proc spTranspose
@TableName varchar(100), 
@PrimaryCol varchar(100),   -- parent column to transposing columns
@SecondaryCol varchar(1000) -- CSV string of columns to transpose
as

select @SecondaryCol = replace(@SecondaryCol, ' ', '')

declare @i int, @tInc varchar(10), @tIncNext varchar(10), @Sql varchar(8000), 
	@SqlSelect varchar(8000), @tempSql nvarchar(4000), @ColNum int 

select @i=1, @Sql = '', @SqlSelect = ''

select 	@tempSql = 'select top 1 @ColNum = count(*) from ' + @TableName + ' group by ' + @PrimaryCol + ' order by 1 desc'

exec sp_executesql @tempSql, N'@ColNum int output', @ColNum output

if object_id('tempdb..##temp_transpose') is not null
 	drop table ##temp_transpose

exec 	('select identity (int, 1,1) as Transpose_Id, ' + @PrimaryCol + ', ' + @SecondaryCol + 
	' into ##temp_transpose from ' + @TableName + ' order by ' + @PrimaryCol + ', ' + @SecondaryCol)

select 	@SqlSelect = 'select distinct t1.' + @PrimaryCol, 
	@Sql = ' from ##temp_transpose t1'

while @i<=@ColNum
begin
	set @tInc = 't' + cast(@i as varchar(10))
	set @SqlSelect = @SqlSelect + ', ' + @tInc + '.'+ replace(@SecondaryCol, ',', ', ' + @tInc + '.')
	set @tIncNext = 't' + cast(@i+1 as varchar(10))
	if @i<@ColNum 
		set @Sql = @Sql + ' LEFT JOIN ##temp_transpose ' + @tIncNext + ' on t1.' + @PrimaryCol + ' = ' + @tIncNext + '.' + @PrimaryCol + ' and ' + @tInc + '.Transpose_Id = ' + @tIncNext + '.Transpose_Id-1 '
	set @i = @i + 1
end
exec(@SqlSelect + @Sql + ' WHERE (select count(*) from ##temp_transpose where t1.' + @PrimaryCol + ' = ' + @PrimaryCol + ' and t1.Transpose_Id > Transpose_Id)=0')
go

exec spTranspose 'yourTableName', 'id', 'date_visit,'

/*
use northwind
exec spTranspose 'orders', 'customerId', 'orderDate, shippedDate, employeeid'
*/