postgresql dynamically name columns in case statement

Question

I'm looking for a way to dynamically or automatically name the columns in my case statement below. Scenario - I'm trying to find out how many different companies of various industries are found in each country. The countries are the rows while the categories are the columns.

I'm using postgressql so pivot won't work and I don't have a new enough version where I can use cross-tab

I want to be able to replicate this for much larger scenarios where I won't have to worry about 'hardcoding' the cat_nbr and column names like I do here.

SELECT country,
       count(CASE WHEN cat_nbr = 1 THEN company_code END) retail,
       count(CASE WHEN cat_nbr = 2 THEN company_code END) finance,
       count(CASE WHEN cat_nbr = 3 THEN company_code END) oil,
       count(CASE WHEN cat_nbr = 4 THEN company_code END) tech   
FROM global_companies  
GROUP BY country

the table structure format in case it isn't clear has these columns:

country - cat_nbr - company_code -  cat_desc.

Cat_desc is where I have hardcoded the words 'retail', 'finance', etc

Is there someway I can do this with less hardcoding in terms of what I refer to each cat_nbr/cat_desc? There are lots and lots of cat_nbrs and cat_descs.

You need to create a function and create a dinamic query similar to this But instead you will LOOP between your category table. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 8, 2017 at 19:25
@JuanCarlosOropeza. Cat_nbr and retail/finance go together. E.g, category #1 = retail, #2=finance, etc. Translates into words what is going on. Version is something in the 7s, can't check exactly atm. — runningbirds
– runningbirds, Commented May 8, 2017 at 21:49
But you have those in a table or are constant somewhere else? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 8, 2017 at 23:20

Evan Carroll · Accepted Answer · 2017-05-08 21:19:28Z

0

You can not create a query with a dynamic row size. That's impossible, even with cross-tab.

You can however

create a query that returns a SQL statement which you can execute afterward, in the client.
create something like \crosstabview with the client.

You can read more information about this in my question, "How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?".

answered May 8, 2017 at 21:19

Evan Carroll

1

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

Comments

zeroimpl · Accepted Answer · 2017-05-09 01:13:06Z

0

Instead of hardcoding category names, you could hardcode country names for the columns and let the rows be dynamic as usual.

SELECT cat_nbr, 
       COUNT(CASE WHEN Country = 'US' THEN company_code END) AS NumUS,
       COUNT(CASE WHEN Country = 'UK' THEN company_code END) AS NumUK,
       COUNT(CASE WHEN Country = 'FR' THEN company_code END) AS NumFR,
       ...
FROM global_companies
GROUP BY cat_nbr;

Another alternative is you can aggregate the data into JSON or array structures.

answered May 9, 2017 at 1:13

zeroimpl

2,86425 silver badges19 bronze badges

Collectives™ on Stack Overflow

postgresql dynamically name columns in case statement

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related