Post reply

Add " + " to add column values dynamically

  • July 8, 2009 at 7:42 am

    #211895

    All,

    I have one scenario.

    Create table country

    (

    country_id int,

    country_name varchar(255)

    )

    insert into country

    select 1,'Malaysia'

    union all

    select 1,'Philippines'

    union all

    select 1,'Taiwan'

    union all

    select 1,'Thailand'

    union all

    select 2,'Austria'

    union all

    select 2,'Belgium'

    union all

    select 2,'Denmark'

    union all

    select 2,'Finland'

    I want the output like

    1 Malaysia + Philippines + Taiwan + Thailand

    2 Austria + Belgium + Denmark + Finland

    Does Number or Tally table work out here? I don't think so.

    Why i need like this?

    I have a table called country_exposure

    create table country_exposure

    (

    Fund_Id int,

    Malaysia decimal(16,8),

    Philippines decimal(16,8),

    Taiwan decimal(16,8),

    Thailand decimal(16,8),

    India decimal(16,8),

    Austria decimal(16,8),

    Belgium decimal(16,8),

    Denmark decimal(16,8),

    Finland decimal(16,8)

    )

    insert into country_exposure

    select FZKLM,6.5,6.8,4.2,2.1,5.8,7.7,12.6,10.2,11.2

    union all

    select XYQSR,18.5,16.2,24.4,26.8,15.2,1.7,2.6,11.2,1.8

    From UI, user may choose ny country for country_code. say for example for country_code 1

    if any user choose Malaysia, Philippines ,Taiwan ,Thailand then

    we ned to add all those columns values for all the fund_id.

    which is equivalent to

    select fund_id, Malaysia + Philippines + Taiwan + Thailand as Total_Amt

    from country_exposure

    But the problem is we don't know how many countries they choose?

    Inputs are welcome!

    karthik

  • July 8, 2009 at 7:49 am

    #1021663

    Hi ,

    this link[/url] has all you need to know about row concatenation

    :edit. Sorry on further reading , this is more of a PIVOT , have a look in Books on Line.

    But, i will say that you are going down the wrong path design-wise and i would urge you to find another way to achieve your goals


    Clear Sky SQL
    My Blog[/url]

  • July 9, 2009 at 4:27 am

    #1022245

    any idea about my requirement #2.

    karthik

  • July 9, 2009 at 4:27 am

    #1022246

    any idea about my requirement #2.

    karthik

  • July 9, 2009 at 4:29 am

    #1022247

    I think there was some problem while posting the thread. I have received some error message like

    "contact board administrator". Thats why some threads are repeating more number of times in this section.

    karthik

  • July 9, 2009 at 7:14 am

    #1022338

    Karthik, how is the country choice passed to your database? String? What does it look like?

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • July 9, 2009 at 9:32 am

    #1022489

    Greg,

    They will pass it as STRING.

    karthik

  • July 19, 2009 at 10:54 pm

    #1026730

    karthikeyan (7/9/2009)

    Greg,

    They will pass it as STRING.

    Why i need like this?

    I have a table called country_exposure

    create table country_exposure

    (

    Fund_Id int,

    Malaysia decimal(16,8),

    Philippines decimal(16,8),

    Taiwan decimal(16,8),

    Thailand decimal(16,8),

    India decimal(16,8),

    Austria decimal(16,8),

    Belgium decimal(16,8),

    Denmark decimal(16,8),

    Finland decimal(16,8)

    )

    Really, really bad form on that table. Can you normalize it? It would make future maintenance virtually not required and would make you queries so much easier...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 23, 2009 at 2:57 pm

    #1029292

    Really, really bad form on that table. Can you normalize it?

    Karthik,

    In other words, think about what you'd have to do if another country were added to the mix. Would you really want to have to add a column to the country_exposure table every time that happened? If you're really stuck with this horribly denormalized table, you have my sympathies.

Viewing 9 posts - 1 through 8 (of 8 total)

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