Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

String concatenationon field values that exist only

Hello,
I have to update a column within a table where if a certain column(a) has a value.
ideally, if there was a value in all 4 columns, then the concatenation would be '1.1,1.2,1.3,1.4'
if column(a) has a value > 0 then column(x) = '1.1'
if column(b) has a value > 0 then column(x) = '1.1,1.2'
if column(c) has a value > 0 then column(x) = '1.1,1.2,1.3'
if column(d) has a value > 0 then column(x) = '1.1,1.2,1.3,1.4'

like in row 1 below, column b and c do not have any values, so column(x) = '1.1,1.4'
row 2 - '1.1,1.2,1.4'
row 3 - '1.4'

A      B      C      D
---      ---      ---      -----
100      0      0      8000
102      40      0      200
0      0      0      10000


this code will be in a loop that retrieves each of these 4 values in a row.

the code is something like this but this wont work as values like '1.1' might now have a value and not
needed in the concatenation.

--the @exists variables are set to 1 if there is a value > 0...
--@ITEM_TRANS11 = '1.1', @ITEM_TRANS12 = '1.3', etc...  column(a). column(b), etc...
--@ITEM_COMPAT = column(x)
            if @exists1 = 1 begin set @ITEM_COMPAT = @ITEM_TRANS11 end;
            if @exists2 = 1 and @exists1 = 0
                  begin
                        set @ITEM_COMPAT = @ITEM_COMPAT+','+@ITEM_TRANS12
                  end;
            if @exists3 = 1 and @exists1 = 0
                  begin
                        set @ITEM_COMPAT = @ITEM_COMPAT+','+@ITEM_TRANS13
                  end;

I dont want the concatenation to end up like this:
row 1 = '1.1,,,1.4'
row 2 - '1.1,1.2,,1.4'
row 3 - ',,,1.4'


I can use like many conditions to try and check for any combinations but that would be
too much code as a better solumn can be found with limited code created.

using sql server 2008

any help would be appreciated.

thanks,
MG
0
Machinegunner
Asked:
Machinegunner
1 Solution
 
PortletPaulfreelancerCommented:
This result:
A   B  C D     x           
--- -- - ----- ----------- 
100 0  0 8000  1.1,1.4     
102 40 0 200   1.1,1.2,1.4 
0   0  0 10000 1.4         

Open in new window

from:
declare @table1 table
    ([A] int, [B] int, [C] int, [D] int)
;
    
INSERT INTO @table1
    ([A], [B], [C], [D])
VALUES
    (100, 0, 0, 8000),
    (102, 40, 0, 200),
    (0, 0, 0, 10000)
;

select
        [A]
      , [B]
      , [C]
      , [D]
      , stuff(a1+b1+c1+d1, 1, 1, '') x
from @table1
outer apply (
           select
                case when [a] <> 0 then ',1.1' else '' end  a1
              , case when [b] <> 0 then ',1.2' else '' end  b1
              , case when [c] <> 0 then ',1.3' else '' end  c1
              , case when [d] <> 0 then ',1.4' else '' end  d1
      ) oa

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now