CODEWITHSUNDEEP
sqlpostgresql
HakimuddinHussain
HakimuddinHussain

Reputation: 69

Creating two colums from rows of a table

I have this table along with few other columns: 

months    amount
-----------------
 Jan       100
 Feb       200
 Jan       400

I want to create a view with this table so that it be like this:

Jan    Feb
--------------------
100    200
400    0

I searched for it and got PL/SQL solutions cant it be done with only SQL queries ? Thank You

Upvotes: 0

Views: 122

Answers (2)

Thomas
Thomas

Reputation: 1066

First make sure, you have Crosstab available

CREATE EXTENSION tablefunc;

To get the crosstable you want you need at least three columns

eg:

name    months    amount
-------------------------
Helga    Jan       100
Helga    Feb       200
Bernd    Jan       400

Then you create your Crosstable like this:

SELECT * FROM 
   crosstab('SELECT name, month, amount from temp')
 AS ct(name varchar, jan int, feb int);

name       jan       feb
--------------------------
Helga     100        200
Bernd     400

If you are going to have columns for each month, stick to the example as described in the manual

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 rows)

Upvotes: 1

GarethD
GarethD

Reputation: 69749

There is a problem with your logic, how would SQL know that it should be 

Jan     Feb
100     200
400     0

and not 

Jan     Feb
400     200
100     0

I have taken this to mean that the columns should be in ascending order, (Except for where the value is 0 because there is not enough data):

WITH CTE AS
(   SELECT  Month, Amount, ROW_NUMBER() OVER(PARTITION BY Month ORDER BY Amount) AS RowNumber
    FROM    T
)
SELECT  COALESCE(Jan.Amount, 0) AS Jan,
        COALESCE(Feb.Amount, 0) AS Feb,
        COALESCE(Mar.Amount, 0) AS Mar
FROM    (   SELECT  RowNumber, Amount
            FROM    CTE
            WHERE   Month = 'Jan'
        ) jan
        FULL JOIN 
        (   SELECT  RowNumber, Amount
            FROM    CTE
            WHERE   Month = 'Feb'
        ) feb
            ON feb.RowNumber = Jan.RowNumber
        FULL JOIN 
        (   SELECT  RowNumber, Amount
            FROM    CTE
            WHERE   Month = 'Mar'
        ) Mar
            ON Mar.RowNumber = Jan.RowNumber

This logic has been tested in SQL Fiddle, however I am fairly sure you can apply the use of Rownumber to CROSSTAB to get around the problems you are currently having with CROSSTAB. RowNumber becomes your rowvalue, I have never used Crosstab in postgres but I think you would end up with something like this:

SELECT  *
FROM    CROSSTAB('SELECT ROW_NUMBER() OVER(PARTITION BY Month ORDER BY Amount) AS RowNumber, Month, Amount FROM T',
                 'SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY Month ORDER BY Amount) AS RowNumber FROM  T') 
        AS (RowNumber INT, Jan INT, Feb INT, Mar INT);

I can't get this working on SQL Fiddle, but I think it is because CROSSTAB isn't enabled as even the example in the Postgres docs doesn't work on SQL Fiddle.

Upvotes: 1

Related Questions