Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    14

    Unanswered: how to create cross table in postgresql? urgent please.

    hi friends, i want to knw hw to create cross table. i have the following table. name and city are the columns.


    name city
    ______ _______
    ram chennai
    shyam bangalore
    bharat bangalore

    i want to retrieve values like chennai as column and it should contains all the name who belong to chennai and bangalore as another column which contains all the names of those people who belong to bangalore. see, row's values becoming column's name.
    chennai bangalore
    _________ ___________
    ram shyam
    bharat


    we can do it SQL server by using "pivot" statement. how to do in Postgresql. Please help me out.

    Thanks.

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    In pg you'll have to use the tablefunc contrib module.

    CrossTab Queries in PostgreSQL using tablefunc contrib - Postgres OnLine Journal

  3. #3
    Join Date
    Aug 2010
    Posts
    1

    how to creat cross table in postgresql

    Hello,
    A cross tab query is a transformation of rows of data to columns. It usually involves aggregation of data e.g. totals broken down by months, products etc., where the months are represented by columns.
    So you can try
    SELECT
    SUM(CASE WHEN purchase_date BETWEEN '2004-08-01' and '2004-08-31' THEN amount ELSE 0 END) As m2004_08,
    SUM(CASE WHEN purchase_date BETWEEN '2004-09-01' and '2004-09-30' THEN amount ELSE 0 END) As m2004_09,
    SUM(CASE WHEN purchase_date BETWEEN '2004-10-01' and '2004-10-31' THEN amount ELSE 0 END) As m2004_10,
    SUM(amount) As Total
    FROM purchases WHERE purchase_date BETWEEN '2004-08-01' AND '2004-10-31'
    This is one example of cross table

Posting Permissions

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