Using ORDER BY in a crosstab() function

Question

Schema names: "C_GT_Master","C_GT_Master". My table name is items. Both schemas have the same table.

Fields: itemno, itemname, unitno.

I want to get details from items table in both schemas. Am using following code:

SELECT stckreport.* FROM public.crosstab('select itemname, ''GT'' anal,
itemno from  "C_GT_Master".items i 
union all 
select itemname, ''UnitNo'' anal,unitno from  "C_GT_Master".items i 
union all 
select itemname, ''New'' anal,itemno from  "G_New_Master".items i  
union all 
select itemname, ''UnitNo'' anal,unitno from  "G_New_Master".items i 
','select ''GT'' union all select ''New'' union all select ''UnitNo''') 
as stckreport 
(itemname text,GT text,New Text,UnitNo text)

This query returns:

 itemname  gt  new unitno
   AB           1    
   AB                1
   AB       1          
   AB                2

But I want:

itemname  gt   new  unitno
 AB             1    1
 AB       1          2

How to get that result using crosstab(). My real problem in a project is too big, so I explain it using this one table.

Am using PostgreSQL 9.1.

Please explain why you fold *some* of the `'AB'` rows, but not all. One row per schema? And it's always helpful to provide table definitions like what you get with `\d tbl` in psql. — Erwin Brandstetter, Mar 20 '14 at 08:04

score 1 · Answer 1 · answered Mar 20 '14 at 06:19

1

Try to group by result on itemname & unitno column, following is example of sql server,

With CTE as
(
SELECT stckreport.* FROM public.crosstab('select itemname, ''GT'' anal,
itemno from  "C_GT_Master".items i 
union all 
select itemname, ''UnitNo'' anal,unitno from  "C_GT_Master".items i 
union all 
select itemname, ''New'' anal,itemno from  "G_New_Master".items i  
union all 
select itemname, ''UnitNo'' anal,unitno from  "G_New_Master".items i 
','select ''GT'' union all select ''New'' union all select ''UnitNo''') 
as stckreport 
(itemname text,GT text,New Text,UnitNo text)
)
select itemname,sum(DT),SUM(New),UnitNo from CTE
group by itemname,UnitNo
order by itemname,UnitNo

answered Mar 20 '14 at 06:19

AK47

3,707
3
17
36

Gt,New is text column then how can i do sum – Sathish Mar 20 '14 at 06:23
OK. but they will have values like "1" or what? – AK47 Mar 20 '14 at 06:24
"1" its a text. because my original values looks like – Sathish Mar 20 '14 at 06:30
Even though Gt & New are text columns, will they have integer value alwas? – AK47 Mar 20 '14 at 06:37
select record convert datatable. then assign the datasource. so i want to some work after the assign datasource. so i want text column only so i take like this – Sathish Mar 20 '14 at 06:39
Then I think,I am Not clear with question, so my provided ans will not work for you. – AK47 Mar 20 '14 at 06:45
my column type is text. So i can't use sum. Thanks for your valuable time. plz check my current answer and my expecting answer. – Sathish Mar 20 '14 at 06:49
pls provide schema of you table. – AK47 Mar 20 '14 at 06:54
I Tried Like this in your query `select itemname,sum(cast(GT as double precision)),SUM(cast(New as double precision)), sum(cast(UnitNo as double precision)) from CTE group by itemname,UnitNo order by itemname,UnitNo` but tha time also it returns twice – Sathish Mar 20 '14 at 06:56
Schema means. Sorry Sir. What is schema – Sathish Mar 20 '14 at 06:56
Hey friend, don't call as Sir. Schema means you table structure.If possible pls share some dummy data also. – AK47 Mar 20 '14 at 07:01
ok but my real query have more than 10 tables and it has neary 12 union all query's. my original query is toooo big. that query take 7 page in word doc. so how can post it and explain it. – Sathish Mar 20 '14 at 07:08

score 1 · Accepted Answer · edited May 23 '17 at 11:49

If I understood correctly and you want one row per schema, this modified crosstab() query should work:

SELECT  "schema", itemname, "GT", "New", "UnitNo"
FROM    public.crosstab($$
    SELECT 'c' || itemname, "C_GT_Master" AS sch, itemname
        , 'GT'::text AS anal, itemno FROM "C_GT_Master".items
    UNION ALL 
    SELECT 'c' || itemname, "C_GT_Master",  itemname
       , 'UnitNo', unitno FROM  "C_GT_Master".items
    UNION ALL 
    SELECT 'g' || itemname, "G_New_Master", itemname
        , 'New'   , itemno FROM  "G_New_Master".items
    UNION ALL 
    SELECT 'g' || itemname, "G_New_Master", itemname
        , 'UnitNo', unitno FROM  "G_New_Master".items
   ORDER BY 1$$

    ,$$VALUES ('GT'::text), ('New'), ('UnitNo')$$
   ) 
AS stckreport (
   item text, "schema" text, itemname text
 , "GT" text, "New" text, "UnitNo" text);

Major points

Add ORDER BY 1 in the first query string. That was your primary error.
I concatenate a hash for the schema to the itemname to a value to group by resulting in one row per (schema, itemname). In addition I add schema and itemname separately to have both in the result. Only select those in the outer SELECT for display. More details about "additional columns":
Pivot on Multiple Columns using Tablefunc
And about concatenating a row name:
crosstab with 2 (or more) row names
Use dollar-quoting to make your life easier.
Use a VALUES expression for the second query string.
More details in my basic go-to answer for crosstab():
PostgreSQL Crosstab Query

Using ORDER BY in a crosstab() function

2 Answers2

Major points