DB2:field in column name

Posted on 2011-05-05
Last Modified: 2012-05-11
How to have a cross fields table in db2 sql?
I have a table with 3 field: code, week, value.
I want to have starting from this table a second table
with fields: code, week(n), week(n+1).... , week(m)
and in field week I want to put field value of the first table.
A cross fields table.
How to do it?
Question by:bobdylan75
    LVL 45

    Assisted Solution


    Hi Bob,

    DB2 doesn't have a cross-tab function like SQL Server does so you'll have to "roll your own" on this.  That is, if you really need it at all.

    You can quite easily just write SQL to put as few or as many columns as you want on the result.

    If you can explain what you're trying to do a bit more perhaps we can work out the SQL.


    Accepted Solution

    Infact, but with the "case wehn then else end" statment I can do what I need...
    in the link below you can say an example,
    LVL 2

    Expert Comment

    select *
    table1 inner join table2
    on table1.code=table2.code
    and table1.week= (case table1.value when 1 then table2.week1
    when 2 then table2.week2
    when 3 then table2.week3
    when 4 then table2.week4

    be success


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now