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. [stackoverflow.com/questions/8833679/… — MatBailie
– MatBailie, Commented Oct 9, 2012 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
– NakaBr, Commented Oct 9, 2012 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
– MatBailie, Commented Oct 9, 2012 at 13:31
Thanks for your help. I will read. Maybe i will try adapt this situation in Model layer. — NakaBr
– NakaBr, Commented Oct 9, 2012 at 13:32

Chris Travers · Accepted Answer · 2013-04-10 14:22:39Z

0

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.

answered Apr 10, 2013 at 14:22

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql - CrossTab or Pivot with Variable number column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related