Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: combine text field based on value

    I have one table with text column(varchar50) and 2 value columns(INT)

    it looks like this:
    c1, c2, c3
    1, null, text1
    2, null, text2
    3, null, text3
    4, 3, text4

    What i want to do is to combine c3 on row 3 and 4 so the output would look like this:
    c1, c2, c3
    1, null, text1
    2, null, text2
    3, null, text3
    4, 3, text3\text4

    I have tried to use CASE with no luck.

    edit:
    output like this is also fine
    c1, c2, c3
    1, null, text1
    2, null, text2
    3, null, text3
    4, 3, text4
    5, null, text3\text4


    Thanks in advance //Mr
    Last edited by mrpcguy; 02-27-14 at 10:29.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How "recursive" is this?
    For example, if row 3 itself referenced row 2, would you want the text from rows 2, 3, and 4 on row 4?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    i want rows where value in C1 and C2 is equal combined by C3 and only the combined result should be shown, not the rows where the combined result is taken from.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is not what your example shows.
    The row from which the data was copied (3) still shows up in the final results.

    This will end up being a very complex recursive Common Table Expression query.
    You need to seriously nail down the business requirements before diving into it. Then read up on CTEs in Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by mrpcguy View Post
    What i want to do is to combine c3 on row 3 and 4 so the output would look like this:
    c1, c2, c3
    1, null, text1
    2, null, text2
    3, null, text3
    4, 3, text3\text4
    Here is the simplistic approach that does NOT account for recursive possibilities, but gives you what you said you wanted as output:

    Code:
    declare @t1 table
    (
       c1 int
      ,c2 int
      ,c3 varchar(20)
    )
    Insert @t1 (c1, c2, c3) values
       (1, null, 'text1')
      ,(2, null, 'text2')
      ,(3, null, 'text3')
      ,(4, 3, 'text4')
    
    update t1 set
       t1.c3 = t2.c3 + '\' + t1.c3
    from @t1 t1
    inner join @t1 t2 on t2.c1 = t1.c2
    
    
    select * from @t1
    or if you are not wanting to update the actual data and just want to display it, then the following would be more appropriate:

    Code:
    select t1.c1, t1.c2, t2.c3 + '\' + t1.c3
      from @t1 t1
     inner join @t1 t2 on t2.c1 = t1.c2
    Last edited by LinksUp; 03-05-14 at 01:37.

  6. #6
    Join Date
    Mar 2004
    Posts
    162
    works like a charm, thanks LinksUp

  7. #7
    Join Date
    Mar 2004
    Posts
    162
    This seems to be given the result i was looking for.
    SELECT
    CASE
    WHEN t1.c2 IS NOT NULL THEN ltrim(rtrim(t1.c3)) + '\' + t2.c3 ELSE t1.c3
    END AS customer
    FROM t1
    LEFT JOIN t1 t2 ON t2.C1 = t1.C2

Posting Permissions

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