#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    Crosstab / transposition


    Hello,

    I would like to know whether it is possible to use the crosstab function or other methods to get from this table structure

    id | 1970 | 1971 | 1972 | ...
    1 | a | b | c | ...
    2 | d | e | f | ...
    ... | ... | ... | ... | ...

    to that table structure

    id | year | value
    1 | 1970 | a
    1 | 1971 | b
    1 | 1972 | c
    1 | ... | ...
    2 | 1970 | d
    2 | 1971 | e
    2 | 1972 | f
    2 | ... | ...

    I tried a lot of things, but I have not really made much progress yet. So now I would like to know wheater it is possible to achieve this goal? I will be very grateful for any help!

    Thanks in advance,
    D. Stroeer
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0
    This worked:

    SELECT DISTINCT ID, '1972' AS year, 1972 AS value FROM OrigTable
    UNION ALL
    SELECT DISTINCT ID, '1973' AS year, 1973 AS value FROM OrigTable
    UNION ALL
    ...
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    I doubt that this "works", because 1972, 1973 etc. are simply numbers. If you want the columns, you need to use double quotes to distinguish them from numbers. Those are nonstandard names.

    How on earth did you even get to the first table structure? Is this some kind of prank from an evil coworker?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    What a horrible schema. It looks like the output of crosstab(), not a base table that is supposed to carry data. Yuk.

    Anyway, if this is a recurring requirement and you have to deal with a variable number of column and there is some pattern to the way the columns are labeled (like they are always integer values) then you could create a function that returns a table the way you want based column data in the information_schema:
    sql Code:
    CREATE TABLE stupid_table
      ( id    SERIAL PRIMARY KEY,
       "1970" CHAR,
       "1971" CHAR,
       "1972" CHAR);
     
    SELECT column_name, data_type
      FROM information_schema.COLUMNS
      WHERE   TABLE_NAME = 'stupid_table'
          AND column_name ~ '[\d]+';
    gives you what you need to write a function that receives the target table name and writes a new function that returns the output you want (or it could construct and execute a single query -- which is better depends on what you need).

    If you're not used to metaprogramming, this might sound complicated (but don't worry, its not, really). If this is a regular problem you face then it is a far superior solution to hand-writing a new UNION clause for each column on an arbitrarily large table (what if it were every year from 1970 to now?).

IMN logo majestic logo threadwatch logo seochat tools logo