--Vertical table variable row table

--Vertical table variable row table
--Create a table
CREATE TABLE A (
Work number varchar (20),
Date smalldatetime,
Time varchar (20)
)
--Inserting test data
Insert INTO A select ' 01 ', ' 2004-10-01 ', ' 07:50 '
Union SELECT ' 01 ', ' 2004-10-01 ', ' 11:35 '
Union SELECT ' 01 ', ' 2004-10-01 ', ' 14:20 '
Union SELECT ' 01 ', ' 2004-10-02 ', ' 08:01 '
Union SELECT ' 01 ', ' 2004-10-02 ', ' 14:30 '
Union SELECT ' 02 ', ' 2004-10-01 ', ' 07:55 '
Union SELECT ' 02 ', ' 2004-10-02 ', ' 07:58 '
Union Select ' 03 ', ' 2004-10-01 ', ' 07:56 '
--Create a stored procedure
Create proc p_t
As
DECLARE @id varchar, @date smalldatetime, @time varchar (50)
SELECT * Into #a from an order by work number, date, time
Update #a set time =case when (work number = @id) and (date = @date) then @time else time end,
@time =case When (work number = @id) and (date = @date) then @time + ', ' + Time Else time end,
@id = Work number, @date = Date
Select work number, date, Max (time) as time from #a Group by work number, date order by work number
-Implementation
EXEC p_t
--Test results
Date and time of work number
---------------------------------------------
01 2004-10-01 00:00:00 07:50,11:35,14:20
01 2004-10-02 00:00:00 08:01,14:30
02 2004-10-01 00:00:00 07:55
02 2004-10-02 00:00:00 07:58
03 2004-10-01 00:00:00 07:56
(The number of rows affected is 5 rows)



SELECT [Empid] from [Employee]
Come to the result:
Tom
John doe
Harry
...
...

Want to merge it into this result:
John, Dick, Harry ...,...

DECLARE @s varchar (8000)
Set @s= '
Select @s=@s+ ', ' +[empid] from [Employee]
Print stuff (@s,1,1, "")




DECLARE @sql varchar (2000)
Set @sql = '
Select @sql = @sql +[empid]+ ', ' from [Employee]
Set @sql =left (@sql, Len (@sql)-1)
Print @sql