Professional Web Applications Themes

How to concatenate strings across many to one relationships - Microsoft SQL / MS SQL Server

> I know it runs against the grain of what you normally use a select statement  This would be better done at the client; however, there are unsupported and undoented ways to get it done in T-SQL. For example (if you process one row at a time): DECLARE names VARCHAR(8000) SET names = '' SELECT names = COALESCE(names, '') + '&' + name FROM table WHERE company = 10000 AND name IS NOT NULL PRINT names If you needed to do this for every row, you could use a cursor and insert the results of each iteration into a #temp ...

  1. #1

    Default Re: How to concatenate strings across many to one relationships

    > I know it runs against the grain of what you normally use a select
    statement 

    This would be better done at the client; however, there are unsupported and
    undoented ways to get it done in T-SQL. For example (if you process one
    row at a time):


    DECLARE names VARCHAR(8000)
    SET names = ''

    SELECT names =
    COALESCE(names, '') + '&' + name
    FROM table WHERE company = 10000
    AND name IS NOT NULL

    PRINT names



    If you needed to do this for every row, you could use a cursor and insert
    the results of each iteration into a #temp table. But at this point, it is
    much more efficient for a client front-end, report writer, ASP page etc. to
    handle the presentation for you.


    Aaron Guest

  2. #2

    Default Re: How to concatenate strings across many to one relationships

    Concatenation of columns over rows is easy with the
    RAC utility for S2k.No coding required.
    Check out:
    http://www.rac4sql.net/onlinehelp.asp?topic=236


    RAC v2.2 and QALite released.
    www.rac4sql.net



    Groucho Guest

  3. #3

    Default How to concatenate strings across many to one relationships

    Hi,
    Is it possible to use a select statement to return, for example, a
    userId and a single field containing a delimited string of all records that
    match in a second table. For example if we have Companies and People, is it
    possible to select coId and a sinlge field containing a single string with
    people surnames in it?

    (Ie . Comapny: 10000 | Surnames: 'Stansbury&Smith&Green')

    I know it runs against the grain of what you normally use a select statement
    for, and I just can't think of a way round it.

    Thanks,
    Nick



    Nick Guest

  4. #4

    Default Re: How to concatenate strings across many to one relationships

    CREATE TABLE People (company INTEGER NOT NULL, surname VARCHAR(20), PRIMARY
    KEY (company,surname))

    INSERT INTO People VALUES (10000,'Stansbury')
    INSERT INTO People VALUES (10000,'Smith')
    INSERT INTO People VALUES (10000,'Green')
    INSERT INTO People VALUES (20000,'Jones')

    SELECT company,
    MIN(CASE seq WHEN 1 THEN surname END)+
    COALESCE('&'+MIN(CASE seq WHEN 2 THEN surname END),'')+
    COALESCE('&'+MIN(CASE seq WHEN 3 THEN surname END),'')+
    COALESCE('&'+MIN(CASE seq WHEN 4 THEN surname END),'')+
    COALESCE('&'+MIN(CASE seq WHEN 5 THEN surname END),'')
    AS surnames
    FROM
    (SELECT P1.company, P1.surname, COUNT(*) AS seq
    FROM People AS P1
    JOIN People AS P2
    ON P1.company = P2.company AND P1.surname>=P2.surname
    GROUP BY P1.company, P1.surname) AS C
    GROUP BY company

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  5. #5

    Default Re: How to concatenate strings across many to one relationships

    I agree with Aaron, return separate rows and deal with it client side.
    You could return 2 recordsets, the first with the coID and the second with
    the surnames.
    One very bad thing could happen it you use Aaron's solution.
    If the total characters > 8000, the statement will return 8000 characters
    and NO error will be raised.

    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 
    > statement 
    >
    > This would be better done at the client; however, there are unsupported[/ref]
    and 
    one 
    is 
    to 


    raydan Guest

  6. #6

    Default Re: How to concatenate strings across many to one relationships

    True. I should have pointed out that you need to add as many CASE statements
    as required - at least as many as the maximum number of surnames per
    company.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  7. #7

    Default Re: How to concatenate strings across many to one relationships

    Another alternative if you don't feel inclined to define an upper limit on
    the number of surnames (as required by my previous post):

    CREATE TABLE Results (company INTEGER PRIMARY KEY, surnames VARCHAR(1000)
    NOT NULL)

    INSERT INTO Results (company, surnames)
    SELECT company, MAX(surname)
    FROM People
    GROUP BY company

    WHILE ROWCOUNT>0
    UPDATE Results
    SET surnames =
    (SELECT MAX(surname)
    FROM People
    WHERE People.company = Results.company AND
    People.surname<LEFT(surnames,CHARINDEX('&',surname s+'&')-1))
    + '&' + surnames
    WHERE EXISTS
    (SELECT *
    FROM People
    WHERE People.company = Results.company AND
    People.surname<LEFT(surnames,CHARINDEX('&',surname s+'&')-1))

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  8. #8

    Default Re: How to concatenate strings across many to one relationships

    Thank you. I did actually just work around the clients requirements by
    reproducing a similar effect client side. Still doesn't seem like the most
    effecient way of doing it, but temp tables aren't any better.
    Nick
    "raydan" <nospamcom> wrote in message
    news:phx.gbl... 
    > > statement 
    > >
    > > This would be better done at the client; however, there are unsupported[/ref]
    > and 
    > one [/ref]
    insert 
    > is 
    > to 
    >
    >[/ref]


    Nick Guest

  9. #9

    Default Re: How to concatenate strings across many to one relationships

    SQL server just isn't suited to do this, and it shouldn't.
    All databases should be used for data retrieval through a set of rows.
    It is up to the client to handle these rows.
    I'm curious though.
    Why would you want a comma delimited list and not a set of rows?

    "Nick Stansbury" <com> wrote in message
    news:phx.gbl... [/ref]
    with [/ref]
    characters [/ref]


    raydan Guest

Similar Threads

  1. Extracting strings delimited by other strings
    By Scott Bass in forum PERL Modules
    Replies: 2
    Last Post: May 7th, 02:26 AM
  2. Replies: 8
    Last Post: September 3rd, 02:48 PM
  3. Way to concatenate SWF?
    By Logan263 in forum Macromedia Flash
    Replies: 3
    Last Post: November 11th, 10:44 AM
  4. Replies: 0
    Last Post: July 2nd, 06:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139