Concatenation of days from records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Concatenation of days from records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-24 : 08:00:06

Gil writes "I have two tables. The first table contains firstname & lastname. The second table contains schedules. On the second table it has datefm, dateto, hrly rates and hours columns and a foreign key that links the names on table1. The total amt is derived form the formula Hrly rates * hours. The hours is derived from the datefm and dateto difference. The SchedDays column is derived from the day of the dateto column. The users would like to have a result set that looks like this.

Name Mon SchedDays Total Amt
JPeter 2 3,4,5,6 100
JPeter 3 7,10,11,15 350
PPaul 2 7,8,10,6 1000
PPaul 3 10,12,15 2000

Any help will be highly appreciated.
Thank you!"

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-24 : 15:48:10

Without an understanding of your table structure (e.g., CREATE TABLE statements), how can anyone offer you some assistance?

HTH

=================================================================
Don't say you don't have enough time. You have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein.
-H. Jackson Brown, Jr., writer

1fred
Posting Yak Master

158 Posts

Posted - 2005-02-24 : 16:25:17

I think I have a similar question, here is my dataset:

declare @temp_table table ([id] int,[desc]varchar(50))

insert into @temp_table
select 1, 'HI '
insert into @temp_table
select 1, 'Everybody'
insert into @temp_table
select 1, 'My name is :'

insert into @temp_table
select 2, 'Hello'
insert into @temp_table
select 2, 'To'
insert into @temp_table
select 2, 'All'
insert into @temp_table
select 2, 'Of'
insert into @temp_table
select 2, 'You'

I'd like the result to be

id, desc
1, Hi Everybody My name is:
2, Hello To All of You

To do that I need to concatenate all the desc field found in my table for each id and add a space between each word. It's going to be a store proc returning the concatenated field for an ID passed by parameter.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-02-24 : 16:32:23

This article will help you out. It shows how you can use the COALESCE function to build a comma (or in your case a space) seperated string.

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

Dustin Michaels

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources