Crosstab in multiple Columns

Question

Here am explain my problem with sample.

Its My Sample query.

SELECT * FROM public.crosstab(
$$VALUES ('na','sales', 1, 100) 
,('na','sales1', 1, 1000)
, ('ba','sales', 2, 300) $$
,$$VALUES ('1'), ('2')$$
) AS pivot (na text,n text, co11 int, col2 int)

It Produce Result like this

 Na N     Col1   Col2
 na sales  1000
 ba sales        300

But I want to get like this

Na   N    Col1   Col2
na  sales  100
na  sales1 1000
ba  sales         300

How to get that?. Why the above query produce that result?

am using postgresql 9.3

Sathish · Answer 1 · 2014-10-20T13:16:39.670

I tried like this it produce the correct result

SELECT * FROM public.crosstab(
   $$VALUES ('na-sales', 'na','sales', 1, 100) 
   ,('na-sales1', 'na','sales1', 1, 1000)
   , ('ba-sales', 'ba','sales', 2, 300) order by 1,2 $$
,$$VALUES ('1'), ('2')$$
   ) AS pivot (t text, n text,na text, co11 int, col2 int)

Result

  t         N   Na    col1   col2
  na-sales  na  sales  100     
  na-sales1 na  sales1 1000
  ba-sales  ba  sales         300

score 1 · Accepted Answer · edited May 23 '17 at 10:26

You found the answer: the missing ORDER BY clause. While using the two-parameter form of crosstab(), it's ok to use just ORDER BY 1. For the one-parameter form it would need to be

ORDER BY 1,2

Which never hurts, even with the two-parameter form. The manual instructs:

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered

Details:

PostgreSQL Crosstab Query

Crosstab in multiple Columns

2 Answers2