0

this is my view

I want to make the above view vConsolPrchOrd like below:

enter image description here

Database: PostgreSQL 9.2

I want to create a Crystal Report with this view (I asked about this on Stack Overflow).

  • Generally, we very much prefer text over images for data. One cannot copy / paste from images. – Erwin Brandstetter Apr 25 '14 at 16:58
2

You found the reference to the basic crosstab() solution in your own answer to the duplicate question on SO.

Here is your solution, somewhat simplified, and cleaned up:

SELECT *
FROM   crosstab(
   'SELECT product, branch, orderqty
    FROM   vconsolprchord
    ORDER  BY 1'
 , 'VALUES(1),(3),(4),(5),(6)'   -- int is default type, matches "branch"
   ) AS tbl (product  varchar
           , branch_1 numeric    -- must match the type of "orderqty"
           , branch_3 numeric
           , branch_4 numeric
           , branch_5 numeric
           , branch_6 numeric)
LIMIT  5;
| improve this answer | |
2

assuming you have a limited and known number of branches, it could probably look like this:

select product,branch,
-- branch 1
       case when branch=1 then sum(orderqty) else null end as orderqty1,
       case when branch=1 then sum(prevmthsale) else null end as prevmthsale1,
       case when branch=1 then sum(curntstk) else null end as curntstk1,
-- branch 2
       case when branch=2 then sum(orderqty) else null end as orderqty2,
       case when branch=2 then sum(prevmthsale) else null end as prevmthsale2,
       case when branch=2 then sum(curntstk) else null end as curntstk2,
-- branch 3
       case when branch=3 then sum(orderqty) else null end as orderqty3,
       case when branch=3 then sum(prevmthsale) else null end as prevmthsale3,
       case when branch=3 then sum(curntstk) else null end as curntstk3,
...
from vConsoPrchOrd
group by product, branch

Notice this design has a poor maintanability (you will have to change your sql if a new branch is created), but it might suit your needs.

There might be a better solution using sql-2003, but I'm not an expert in these matters.

| improve this answer | |
  • 1
    just see this link ,i myself found a solution for this since i have no reputation to answer my own question here – Thomas Gabriel Apr 25 '14 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.