The right SQL Statement

For a start it's not grouping that would be

Name Date1 Date2
=================================
James 2008-08-31
James 2008-08-29
Jack 2008-08-31
Jack 2008-08-29
Gregory 2008-08-31
=================================

What you need to do is introduce another column

so you get

1 2008-08-31 James Date1
2 2008-08-31 Jack Date1
3 2008-08-31 Gregory Date1
4 2008-08-29 James Date2
5 2008-08-29 Jack Date2

Then you use cross tab functionality, Name on one axis ExtraColumn on the other

This might give you some ideas

Create Table #CopyMyTable
(CopyDate DateTime NOT NULL,
CopyName VarChar(??) NOT NULL)

Insert into #CopyMyTable(CopyDate,CopyName)
Select [Date],[Name] From MyTable

Create Table #TempMyTable
(TempDate DateTime NOT NULL,
TempName VarChar(??) NOT NULL,
[DateName] Char(5) NULL)

Declare @ValueCount Int
Select @ValueCount = 1
While Exists(Select * From #CopyMyTable)
Begin
Insert Into #TempMyTable
Select Max(CopyDate), CopyName From #CopyMyTable Group By CopyName
Delete From #CopyMyTable Where Exists(Select * From #TempMyTable Where DateName is Null and TempName = CopyName and TempDate = CopyDate)

Update #TempMyTable Set DateName = 'Date' + Convert(VarChar(1),@ValueCount) Where
DateName is null
Select @ValueCount = @ValueCount + 1
End

CrossTab #TempMyTable and Bob's your's mother's sister's brother.

Don't forget to tidy up and drop the temptables, or you could use normal tables and truncate at the start, depends on how 'static' this data is.

There's a lot you can do to tidy up and make the above more efficient, especially if there's a mass of data to process, but the general idea should work.

If you could get 'DateName' even in reverse order into MyTable, you would only need crosstab and could dump the longwinded waffle I wrote above.

HtHs a bit

alter FUNCTION dbo.ufn_FindReports (@sName varchar(50))
RETURNS @retFindReports TABLE
(
sdate varchar(255)
)
AS
BEGIN
declare @sdate varchar(255);
WITH DateList(sdate) AS
(
SELECT cast(date as varchar(12)) FROM mytable where sname=@sName
)
INSERT @retFindReports
SELECT sdate fROM DateList
RETURN
END
GO

declare @sdate varchar(255)
SELECT @sdate = COALESCE(@sdate + ', ', '') +
cast(sdate as varchar(12))
FROM dbo.ufn_FindReports('James')

select distinct (select @sdate),sname from mytable where sname='James'