Post reply

comma delimited list problem

  • March 3, 2009 at 4:05 pm

    #128120

    Hi,

    I was hoping for some help on a problem that I obviously can't solve on my own. I have not been able to find out how to create a comma separated list as in this case for the multiple locations that sharing the same Id. My intention is retrieve a result with a single systemid for multiple locations and using the true systemId and NOT a created Identity ID column to join to other tables.

    I have in the past used functions or Cursors to solve this problem but I really would like to see how to do this without it. I know that the code beneath that I posted doesn't work but perhaps you can tell what I'm after. If I uncomment the where clause it will work for just that single systemid. I expect something like this

    SystemID, Locations

    1001, CA,ML,IL

    1002, CA,FL

    etc

    DECLARE @ID varchar(4000)

    ,@Locations VARCHAR(8000)

    SELECT @ID = systemid

    ,@Locations = COALESCE(@Locations + ', ','')+ CAST(Locations AS VARCHAR(100) )

    FROM table Address

    --where systemid = '4903'

    SELECT @ID,@Locations

    Thanks for taking a look at it in advance it is really appreciated.

  • March 3, 2009 at 7:38 pm

    #952830

    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 all select 1002, 'CA'

    union all select 1002, 'FL'

    select systemID, assad = (select ',' + Locations from #Address WHERE systemID = A1.systemID FOR XML PATH('') )

    from #Address A1

    Bevan Keighley

  • March 3, 2009 at 7:41 pm

    #952831

    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

  • March 3, 2009 at 8:19 pm

    #952842

    Bevan keighley (3/3/2009)

    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

    Slightly different way of cleaning up the leading comma:

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

    insert #Address select 1001, 'CA'

    union all select 1001, 'ML'

    union all select 1001, 'IL'

    union all select 1002, 'CA'

    union all select 1002, 'FL'

    select systemID, assad = stuff((select ',' + Locations from #Address WHERE systemID = A1.systemID FOR XML PATH('')),1,1,'')

    from #Address A1

    drop table #Address

  • March 3, 2009 at 9:29 pm

    #952852

    Lynn Pettis (3/3/2009)

    Bevan keighley (3/3/2009)

    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

    Slightly different way of cleaning up the leading comma:

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

    insert #Address select 1001, 'CA'

    union all select 1001, 'ML'

    union all select 1001, 'IL'

    union all select 1002, 'CA'

    union all select 1002, 'FL'

    select systemID, assad = stuff((select ',' + Locations from #Address WHERE systemID = A1.systemID FOR XML PATH('')),1,1,'')

    from #Address A1

    drop table #Address

    And one more way is

    select systemID, assad = REPLACE((select Locations AS 'data()'

    from #Address

    WHERE systemID = A1.systemID

    FOR XML PATH('')),' ',',')

    from #Address A1

    Of course it well fail if there are spaces in between the values of Locations column.

    John Smith

  • March 4, 2009 at 11:22 am

    #953453

    Hmm, interesting I have never used "FOR xml path" before. However, It return this uggly error. Thanks for your respond.

    Msg 170, Level 15, State 1, Line 9

    Line 9: Incorrect syntax near 'XML'.

  • March 4, 2009 at 11:30 am

    #953460

    What version of SQL Server are you currently running?

  • March 4, 2009 at 11:48 am

    #953474

    Hi Lynn,

    Thanks for your help on this I really appreciate it. I'm using SQL2005. Any ideas?

  • March 4, 2009 at 11:56 am

    #953481

    My first thought was that you were using SQL Server 2000 and had posted in erronously in a SQL Server 2005 forum, but that isn't the case.

    Could you post the code you are getting the error with please. I worked with the code earlier in the thread with no problems at all.

    Edit:

    I just cut and pasted my code from above and it worked no problems. Looking at your code may help.

  • March 4, 2009 at 12:03 pm

    #953486

    I'm just using the snippet that is posted, the only difference is that I declared the table instead.

    declare @Address table

    (systemID int, Locations varchar(16))

    insert into @Address select 1001, 'CA'

    union all select 1001, 'ML'

    union all select 1001, 'IL'

    union all select 1002, 'CA'

    union all select 1002, 'FL'

    select systemID, assad = stuff((select ',' + Locations from @Address WHERE systemID = A1.systemID FOR XML PATH('')),1,1,'')

    from @Address A1

  • March 4, 2009 at 12:09 pm

    #953495

    Code ran just fine on my system. What is the compatability level of your database?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply