Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2013
    Posts
    4

    Question Unanswered: Query to use Column values and column names

    Hi Everyone,
    I am new to this forum and this is my first post.
    I want to use the column values as column names.Below is the sample table and expected output.
    I am using Aqua data studio version 4.5.2 and database is Sybase ASE 11.
    Please help me with the query for the below.
    Thank you in advance.

    current table
    Country Product Amount
    US OTC 10
    US OTC 20
    US FX 50
    LN FX 30
    LN FX 40
    ---------------------
    Expected output table
    Country OTC FX
    US 30 50
    LN 70 0

  2. #2
    Join Date
    Jun 2012
    Posts
    18

    Post

    Hi,

    You can maybe have this by :
    select "Country", "Product", "Amount", 0 order_indic
    union
    select Country, convert(varchar,Product), convert(varchar, Amount), 1 order_indic
    from table
    order by order_indic

  3. #3
    Join Date
    Aug 2013
    Posts
    4
    Thank you acherrytells for the reply.
    However

    Country Product Amount <-- Column names
    US OTC 10
    US OTC 20
    US FX 50
    LN FX 30
    LN FX 40


    Expected output table
    Country OTC FX
    US 30 50
    LN 70 0

    OTC and FX are the col names ,dervied from the column value from above table.
    Sorry ,if i confused you with the explanation before.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You'll have to build a dynamic query that end up looking like this
    Code:
    SELECT Country
    ,'OTC'=sum(CASE WHEN Product='OTC' THEN Amount ELSE 0 END)
    ,'FX' =sum(CASE WHEN Product='FX'  THEN Amount ELSE 0 END)
    FROM TheTable
    GROUP BY Country
    But ASE 11 is from the previous millennium, not even sure if it supported the case statement. If not try
    Code:
    SELECT Country
    ,'OTC'=sum(charindex('.OTC.','.'+Product+'.')*Amount)
    ,'FX' =sum(charindex('.FX.','.'+Product+'.')*Amount)
    FROM TheTable
    GROUP BY Country

  5. #5
    Join Date
    Jun 2012
    Posts
    18
    In 11.9, the first query can work. No idea for 11.0.

    ASE 11 is from the previous millennium
    Yah... I get depressed about my manager refused a migration

  6. #6
    Join Date
    Aug 2013
    Posts
    4
    thanks guys for the reply. will try them.
    we are also moving from sybase to oracle soon

  7. #7
    Join Date
    Aug 2013
    Posts
    4
    hi pdreyer,
    The column names (OTC ,FX) can we get them dynamically rather then hardcoding them in the query.
    Say for example , tomorrow there is one more product added FXO ,we should be able to get one more column "FXO" without changing the query.

Posting Permissions

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