ASP Looping Summary - Strange request?

[neronikita]

Hi, all. I'm not sure I even know what to ask, so here is my situation:

I have data in a database that is for unscheduled PT. I have a sql query that will pull all of that information in Query Analyzer. However, they want it all summarized with one line for each person. So... if I have


Name Hours Date
Jane Smith 5 3/1
Jane Smith 7 3/3
Jane Smith 8 3/6
Jane Smith 2 3/9
Total 22

They want to see it this way

Jane Smith 22 3/1, 3/3, 3/6, 3/9

Does anyone know how to do this in ASP? I was originally trying to do it in Crystal and gave up on that. Here is the SQL query I am using:

SELECT TOR.exceptstarttime, TOR.timeawayval,EMP.f_name, EMP.l_name, EMP.nick_name, MGR.f_name, MGR.l_name FROM Workforce.dbo.tbl_timeoffrequest TOR inner join Workforce.dbo.tbl_employee EMP on TOR.fk_agent = EMP.pk_agent inner join Workforce.dbo.tbl_employee MGR on EMP.manageremplid = MGR.pk_agent WHERE TOR.fk_schedactivity = 23 and TOR.exceptstarttime >= '06/01/2006' and TOR.exceptstarttime < '2/21/2007' and TOR.approvestatus = 1

Thanks for any suggestions/guidance/encouragement anyone has on this....

-D-

 

[Sheco]

If you are using SQL Server then take a look this link:

http://www.sqlteam.com/item.asp?ItemID=2368

 

[neronikita]

Thanks, I'll play with that. I knew this had to be possible, but couldn't even begin to figure out where to look!

 

[gmmastros]

There is a slight problem with the code presented in that link. Depending on your data, it may return the wrong output. You can run this code snippet in Query Analyzer to see what I mean.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color]([COLOR=#FF00FF]RowId[/color] [COLOR=blue]Int[/color], Data [COLOR=blue]varchar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(Data) [COLOR=blue]Values[/color]([COLOR=red]'a'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(Data) [COLOR=blue]Values[/color]([COLOR=red]'b'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(Data) [COLOR=blue]Values[/color](NULL)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(Data) [COLOR=blue]Values[/color]([COLOR=red]'c'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(Data) [COLOR=blue]Values[/color]([COLOR=red]'d'[/color])

[COLOR=blue]Declare[/color] @Output [COLOR=blue]VarChar[/color](8000)

[COLOR=blue]Select[/color] @Output = [COLOR=#FF00FF]COALESCE[/color](@Output + [COLOR=red]', '[/color], [COLOR=red]''[/color]) + Data
[COLOR=blue]From[/color]   @Temp

[COLOR=blue]Select[/color] @Output

The output is:

[tt][blue]
--------------------
c, d
[/blue][/tt]

This happens because of the NULL value in the middle of the list which effectively re-starts the comma delimited list.

Nonetheless, I would suggest you follow Sheco's advise and pursue this output from the database side. Take a look here thread183-1159740 for another example of how this can be done. Near the bottom of that thread, I show a user defined function the you can use to get the comma delimited list.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Sheco]

Perhaps use a WHERE clause or join condition to eliminate the null values?

Declare @Temp Table(RowId Int, Data varchar(20))

Insert Into @Temp(Data) Values('a')
Insert Into @Temp(Data) Values('b')
Insert Into @Temp(Data) Values(NULL)
Insert Into @Temp(Data) Values('c')
Insert Into @Temp(Data) Values('d')

Declare @Output VarChar(8000)

Select @Output = COALESCE(@Output + ', ', '') + Data 
From   @Temp
[red][b]WHERE Data Is Not Null[/b][/red]

Select @Output

----------------
a, b, c, d

(1 row(s) affected)

 

[gmmastros]

Yes. Exactly. That is what the article in the link you showed was missing.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[neronikita]

THanks. I'm not really strong in SQL (yet) so it's taking me some time to get this all to work. Thanks for the suggestions!