Postgresql - CrossTab or Pivot with Variable number column

Question

I need to make a query using pivot or crosstab with columns variables. Is possible? I have the following table:

Data_Id ! Description_Column | Value
--------+--------------------+-------
1       ! Column1            ! value1
1       ! Column2            ! value2 
1       ! Column3            ! value3
1       ! Column4            ! value4
2       ! Column1            ! value5
2       ! Column2            ! value6
2       ! Column3            ! value7
2       ! Column4            ! value8

I will need get

Data_Id ! Column1 ! Column2 ! Column3 ! Column4
1       ! value1  ! value2  ! value3  ! value4
2       ! value5  ! value6  ! value7  ! value8

But sometimes i will have:

Data_Id ! Description_Column | Value
--------+--------------------+-------
3       ! Column1            ! value1
3       ! Column2            ! value2 
4       ! Column1            ! value5
4       ! Column2            ! value6

I will need get

Data_Id ! Column1 ! Column2 
3       ! value1  ! value2  
4       ! value5  ! value6

Obs. The rows that I'll need will be selected through a Join. So I will have the number of variable columns.

Thanks for help.

That's a SQL anti-pattern. No single statement can do that. Equally, if you seach SO for `Dynamic Pivot` you'll find many answers; code that writes SQL. *(For x columns you need different SQL than for y columns. So you need code that writes SQL for the number of appropriate columns.)* In the majority of cases you should keep you data in the original normalised form. You may pivot it in a user interface or other processing layer, but I would recommend against doing so in SQL. [http://stackoverflow.com/questions/8833679/create-dynamic-table-from-function-in-postgresql] — MatBailie, Oct 09 '12 at 13:17
If I do so I'll need about 40 different queries. Was used only a table for different types of data. Instead of creating about 40 tables used only 1 with this format. — NakaBr, Oct 09 '12 at 13:25
You may need 40 queries, but you may be able to write one query that can dynamically create those queries for you. There is no single query that can do this. I recommend reading the linked answer and other answers on SO. — MatBailie, Oct 09 '12 at 13:31
Thanks for your help. I will read. Maybe i will try adapt this situation in Model layer. — NakaBr, Oct 09 '12 at 13:32

score 0 · Answer 1 · answered Apr 10 '13 at 14:22

You have to remember that the SQL planner needs to know at plan time what a return row looks like in rough terms. So you can't do this directly.

The first is you can create a function which does what you are trying to do and returns the result in a refcursor. That's probably the easiest way to do it. Use the crosstab() function from the tablefunc contrib module/extension. You should be able to generate the SQL query to run based on querying the data but that requires in-function dynamic SQL and again you'd have to return a refcursor.

Postgresql - CrossTab or Pivot with Variable number column

1 Answers1