Concatenate multiple rows into a single row

[arodri]

I'm not sure how (or if it's possible) to concatenate the data in the way that I need it.
Here's a sample of my data:

Service1 Serivce 2 Service 3 Code Date
NULL TS NULL 05S6 2011-01-09 00:00:00
FS NULL NULL 05S6 2011-01-09 00:00:00
Null Null AF 05S6 2011-01-09 00:00:00

Here's what I need the data to look like:
Service1 Serivce 2 Service 3 Code Date
FS TS AF 05S6 2011-01-09 00:00:00

I need only one row per DATE and per CODE, and I need all of the services for that particular Date and Code on that ONE row. How can I approach this?

Eventually I need the data to look like this (but I think I can just use "+" once I get all of the data in one row):

ServicesPerformed Code Date
FS,TS,AF 05S6 2011-01-09 00:00:00

 

[born2program]

If you were going to have only 1 value in each column you could do the following.

SELECT max(A.Service1), max(A.Service2), max(A.Service3), A.Code, A.DTE
FROM YourTable A
group by A.DTE, A.Code

 

[drlex]

If the ServicesPerformed is only one field, and you are using SQL server 2005, you might like to look at Brad Schultz's blog page:

http://bradsruminations.blogspot.co.uk/2009/10/making-list-and-checking-it-twice.html

soi là, soi carré

 

[simian336]

there are several examples with the "for xml path" and it is the best.

Simi

 

[arodri]

Thanks for your help! I had to take a different route in the query but if this comes up again I'll know