Skip to main content
Database Administrators

Return to Question

deleted 22 characters in body
Source Link
Evan Carroll
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

How to crosstab with more than three columnsa composite "row name"

I have following table with id(intger), project (varchar), month (varchar), hours (integer)

|id |CREATE projectTABLE |foo (id,project,month | ,hours |)
|1  | A      AS |VALUES
 aug   |( 1 , 'A', 'aug', 1 |),
|1  | A       |( sep1 , 'A', |'sep', 3     |),
|1  | B       |( aug1 , 'B', |'aug', 2     |),
|1  | B       |( sep1 , 'B', |'sep', 5     |),
|2  | A       | aug   |( 2 , 'A', 'aug', 2 |),
|2  | A       |( sep2 , 'A', |'sep', 4     |);

I wanted below table

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?

How to crosstab with more than three columns

I have following table with id(intger), project (varchar), month (varchar), hours (integer)

|id | project | month | hours |
|1  | A       | aug   | 1     |
|1  | A       | sep   | 3     |
|1  | B       | aug   | 2     |
|1  | B       | sep   | 5     |
|2  | A       | aug   | 2     |
|2  | A       | sep   | 4     |

I wanted below table

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?

How to crosstab with a composite "row name"

I have following table with id(intger), project (varchar), month (varchar), hours (integer)

CREATE TABLE foo (id,project,month,hours)
    AS VALUES
    ( 1 , 'A', 'aug', 1 ),
    ( 1 , 'A', 'sep', 3 ),
    ( 1 , 'B', 'aug', 2 ),
    ( 1 , 'B', 'sep', 5 ),
    ( 2 , 'A', 'aug', 2 ),
    ( 2 , 'A', 'sep', 4 );

I wanted below table

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?

edited body
Source Link
lakshman
lakshman
  • 273
  • 2
  • 5
  • 14

I have following table with id(intger), project (varchar), month (varchar), hours (integer)

|id | project | month | hours |
|1  | A       | aug   | 1     |
|1  | A       | sep   | 3     |
|1  | B       | aug   | 2     |
|1  | B       | sep   | 5     |
|2  | A       | aug   | 2     |
|2  | A       | augsep   | 4     |

I wanted below table

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?

I have following table with id(intger), project (varchar), month (varchar), hours (integer)

|id | project | month | hours |
|1  | A       | aug   | 1     |
|1  | A       | sep   | 3     |
|1  | B       | aug   | 2     |
|1  | B       | sep   | 5     |
|2  | A       | aug   | 2     |
|2  | A       | aug   | 4     |

I wanted below table

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?

I have following table with id(intger), project (varchar), month (varchar), hours (integer)

|id | project | month | hours |
|1  | A       | aug   | 1     |
|1  | A       | sep   | 3     |
|1  | B       | aug   | 2     |
|1  | B       | sep   | 5     |
|2  | A       | aug   | 2     |
|2  | A       | sep   | 4     |

I wanted below table

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?

deleted 252 characters in body; edited tags; edited title
Source Link
lakshman
lakshman
  • 273
  • 2
  • 5
  • 14

How to select all rowscrosstab with duplicate first and second columnmore than three columns

I have following table with id(intger), project (varchar), month (varchar), hours (integer)

|id | project | month | hours |
|1  | A       | aug   | 1     |
|1  | A       | sep   | 3     |
|1  | B       | aug   | 2     |
|1  | B       | sep   | 5     |
|2  | A       | aug   | 2     |
|2  | A       | aug   | 4     |

I wanted below table

|id | project | aug | sep |
|1  | 1A   | 3   |
|1  | 21   | 53   |
|2|1  | 2B   | 4   |

I have used following crosstab sql

select * 
from crosstab (
    'select id, month, hours from test'
)
as final_result (id integer, "aug"2 integer, "sep" integer);

But it returns,

 |id | aug5 | sep |
 |1|2  | 1A   | 3   |
 |2  | 2   | 4   |

ItI read this can be possible with an array but it seems like it select only one row when duplicate id and month tuples there. In that case how do we create pivot tables when there are duplicate first and second column values for different third column valuesdocumentation is confusing. Can someone shed some light on this?

How to select all rows with duplicate first and second column

I have following table with id(intger), month (varchar), hours (integer)

|id | month | hours |
|1  | aug   | 1     |
|1  | sep   | 3     |
|1  | aug   | 2     |
|1  | sep   | 5     |
|2  | aug   | 2     |
|2  | aug   | 4     |

I wanted below table

|id | aug | sep |
|1  | 1   | 3   |
|1  | 2   | 5   |
|2  | 2   | 4   |

I have used following crosstab sql

select * 
from crosstab (
    'select id, month, hours from test'
)
as final_result (id integer, "aug" integer, "sep" integer);

But it returns,

 |id | aug | sep |
 |1  | 1   | 3   |
 |2  | 2   | 4   |

It seems like it select only one row when duplicate id and month tuples there. In that case how do we create pivot tables when there are duplicate first and second column values for different third column values.

How to crosstab with more than three columns

I have following table with id(intger), project (varchar), month (varchar), hours (integer)

|id | project | month | hours |
|1  | A       | aug   | 1     |
|1  | A       | sep   | 3     |
|1  | B       | aug   | 2     |
|1  | B       | sep   | 5     |
|2  | A       | aug   | 2     |
|2  | A       | aug   | 4     |

I wanted below table

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?

Source Link
lakshman
lakshman
  • 273
  • 2
  • 5
  • 14