dcsimg
Results 1 to 10 of 10

Thread: [RESOLVED] Concatenating....Stuff

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,504

    Resolved [RESOLVED] Concatenating....Stuff

    I have a table that has a child table in a one to many relationship. There is one particular string field in the child table. Some users would like a query (actually a view, but either one works) with fields from the parent table, plus an additional field that has that one string field from the child concatenated together into one big ball of...stuff.

    Is there any standard, reasonably performant, way to accomplish this? I already hate the idea, because these strings won't look good when concatenated, no matter what I do, but I can see how this field will be useful if it can work in some reasonable amount of time for a relatively modest number of records. I don't believe that I have ever done this via a query before, so I'm looking for a solution.
    My usual boring signature: Nothing

  2. #2
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    900

    Re: Concatenating....Stuff

    I've always concatenated within the SELECT column parameter. Each db vendor has their own syntax. For example, Oracle offers a CONCAT function or || operator so it was SELECT t1.col1 || t1.col2 || ' some other string ' || t2.col1 .... Of course, you can add in needed spaces, commas, etc.

  3. #3

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,504

    Re: Concatenating....Stuff

    Ah yes, I should have mentioned that this is SQL Server.
    My usual boring signature: Nothing

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,466

    Re: Concatenating....Stuff

    What's the result supposed to look like?

    ParentPK, ParentField, ChildField1
    ParentPK, ParentField, ChildField2
    ParentPK, ParentField, ChildField3
    .
    .

    Or
    ParentPK, ParentField, ChildField1+ChildField2+ChildField3....

    EDIT: Found something
    https://www.mytecbits.com/microsoft/...-single-string
    https://stackoverflow.com/questions/...ng-in-sql-serv
    Last edited by Zvoni; Sep 27th, 2018 at 01:55 AM.
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    Re: Concatenating....Stuff

    2012 onwards offers a Concat function but, honestly, it's hard to see what it offers that an old school + doesn't. There's also Concat_WS which lets you pass in a separator which makes things a little more elegant.

    If you're looking to concatenate multiple rows together, I.e. into a comma separated list, the best way I know is to abuse ForXML. See answer 2 here . (Answer 1 is also good and probably easier to understand but relies on undocumented behaviour and is slightly less performant).


    Edit>My edit that added the bit about Concat_WS crossed over with JM so I kinda stole his thunder. I'd like to assure everyone that JM is mighty. Handsome too.
    Last edited by FunkyDexter; Sep 27th, 2018 at 02:27 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,048

    Re: Concatenating....Stuff

    Quote Originally Posted by FunkyDexter View Post
    2012 onwards offers a Concat function but, honestly, it's hard to see what it offers that an old school + doesn't.
    If you use a concatenation operator like '+' then you need to provide each value specifically. I'm guessing that CONCAT could take a list containing an arbitrary number of values, e.g. the result of a subquery. In that case, it would be very useful in this case. In this case though, you'd probably use the CONCAT_WS function, which will concatenate with a separator in between each pair. CONCAT and CONCAT_WS seem to be much like String.Concat and String.Join in .NET. If I'm wrong about their accepting arbitrary lists then they probably aren't all that useful here.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,466

    Re: Concatenating....Stuff

    FD,

    MsSQL 2017 has the STRING_AGG-Function
    but you're right: Depending on the Server-Version, the FOR XML-Solution was the one mentioned the most (since it exists since 2005)
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    Re: Concatenating....Stuff

    a list containing an arbitrary number of values
    I guess so. I'm not sure what the query would look like, though. I guess you'd have to somehow pivot the rows into parms. I'm struggling to picture that.

    MsSQL 2017 has the STRING_AGG-Function
    I didn't know that had been introduced. That is frickin' awesome... and long overdue.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,466

    Re: Concatenating....Stuff

    Quote Originally Posted by FunkyDexter View Post
    I didn't know that had been introduced. That is frickin' awesome... and long overdue.
    https://docs.microsoft.com/en-us/sql...ql-server-2017
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  10. #10

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,504

    Re: Concatenating....Stuff

    Yeah, that totally worked. I was hoping that I had dodged this bullet. Some simpler things had distracted people away from adding in this item, but it floated back to the surface. The STRING_AGG function did the job pretty nicely.
    My usual boring signature: Nothing

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width