0

I want to make a crystal report from the following view:

view

I need to make a report with the above view vConsolPrchOrd that looks like:

report

As you can see this is based on the first 3 Rows in my view vConsolPrchOrd.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • should i use `crossTab` in `crystal report` ? –  Apr 24 '14 at 11:56
  • Yes you are correct..cross tab is the best option....use crosstab – Siva Apr 24 '14 at 12:30
  • @ypercube there is no `cross post` first you read `questions` in two both site [here i asked about to making crystal report] [in dba asked about crosstab] –  Apr 25 '14 at 10:10
  • To me it seems you have (had) one problem to solve. Whether the best is to be solved in Postgres or Crystal reports or with a combined solution, it is still one problem and one question should be asked. – ypercubeᵀᴹ Apr 25 '14 at 10:36
  • @ypercube IMO getting responses `Postgres or Crystal reports` is good –  Apr 25 '14 at 10:41
  • [Cross-posted to dba.SE.](http://dba.stackexchange.com/questions/63893/rows-into-column) – Erwin Brandstetter Apr 25 '14 at 16:46

1 Answers1

0

After trying a lot i myself found a solution for this, making a crosstab crystalreport seems not good to me so i will explain what i have done is:

Installed the additional module tablefunc which provides the function crosstab(). Since i am PostgreSQL 9.1 i can use CREATE EXTENSION for that

CREATE EXTENSION tablefunc;

and converted rows into columns using the following query

select * from crosstab(' select product,branch,orderqty from vConsolPrchOrd ORDER  by product ',$$values(1::int),(3),(4),(5),(6)$$) as tbl("product" character varying,"1"  numeric,"3" numeric,"4" numeric,"5" numeric,"6" numeric) order by product asc limit 5

BEFORE enter image description here

AFTER using crosstab

enter image description here

Reference

Community
  • 1
  • 1