I should have tidied that up a bit...

select systemID, locations = substring((select ',' + Locations from #Address WHERE systemID = A1.systemID FOR XML PATH('')),2,1024)

from #Address A1

Roger, Is this the sort of thing you are after?

create table #Address (systemID int, Locations varchar(16))

insert #Address select 1001, 'CA'

union all select 1001, 'ML'

union all select 1001, 'IL'

union...

You need a function to split your comma separated variable into a table variable.

CREATE FUNCTION [dbo].[SplitList]

(

@List varchar(8000),

@splitter varchar(2)

)

RETURNS

@ParsedList table

(

num int identity(1,1),

item varchar(1024)

)

AS

BEGIN

SET @List = @splitter + LTRIM(RTRIM(@List)) + @splitter

INSERT...

By the way, access has a built in query builder. In the CREATE menu in access 07 click on QUERY WIZARD.

Bevan

Thanks.... Now can you post your attempted SQL and an explanation of what is not working (or any error codes that were returned)

Bevan

Hi ,

You can do this by joining to a cte. Note that you need to join on both reportName and UserID otherwise you will run into trouble...

Firstly run the...

Hi ,

You will need to post some more information before we can help you. Please provide your table design and some sample data. It is best to provide...

hi,

Here is an example you can hopefully build a script from. just change the table names and columns.

insert names2 (fname, lname) select fname, lname from names

truncate table names

You will...

hi,

Using rollup may help. Just add with rollup after your GROUP BY

http://msdn.microsoft.com/en-us/library/ms189305(SQL.90).aspx

Bevan

Hmmm, I'm afraid you've exhausted my brain! Does anyone else have any solutions?

Try adding

option (maxrecursion 1000)

to the very end

Bevan

The trick is that you cannot have any other select statements before a CTE. So, although the code is correct, it cannot be run all together.

Run the first part...

Hi Rog,

I had this written up and forgot to post it before going off to lunch!

Anyway, I have a working example for you. It uses a recursive CTE. ...

I see Noel and I posted almost identical answers on the first question!

Heres one way to update the values...

--- Have a look at new and old data side by...

Hi,

you need to use the row_number function to identify which specific rows you want returned. Change the order by clause if you want the oldest record.

create table # (SKU# varchar(10),...