SQL SELECT statement, column names as values from another table

Question

I'm working on a database which has the following table:

id  location
1   Singapore
2   Vancouver
3   Egypt
4   Tibet
5   Crete
6   Monaco

My question is, how can I produce a query from this which would result in column names like the following without writing them into the query:

Query result:

Singapore , Vancouver, Egypt, Tibet, ...
<                 values               >

This is not possible, and IMHO it's also an indication that you are trying to do something the wrong way. What's your *real* issue? — Jon, Mar 18 '12 at 23:29
I need to produce a count of the number of references to each location in another table, in the output of my query. The column names need to be headed by the location name. — user1277546, Mar 18 '12 at 23:33
So you want to query another database? If so, how is what you have in *this* database relevant? — Jon, Mar 18 '12 at 23:34
Is a crosstab query what you're looking for? See http://stackoverflow.com/questions/3002499/postgresql-crosstab-query — Jeremy Wiggins, Mar 18 '12 at 23:41
Is this to be part of a larger query? Otherwise I think you'd have one row, and only ever one row. — dwerner, Mar 19 '12 at 00:56

score 3 · Answer 1 · edited May 23 '17 at 12:24

how can I produce a query which would result in column names like the following without writing them into the query:

Even with crosstab() (from the tablefunc extension), you have to spell out the column names.

Except, if you create a dedicated C function for your query. The tablefunc extension provides a framework for this, output columns (the list of countries) have to be stable, though. I wrote up a "tutorial" for a similar case a few days ago:

PostgreSQL row to columns

The alternative is to use CASE statements like this:

SELECT sum(CASE WHEN t.id = 1 THEN o.ct END) AS "Singapore"
     , sum(CASE WHEN t.id = 2 THEN o.ct END) AS "Vancouver"
     , sum(CASE WHEN t.id = 3 THEN o.ct END) AS "Egypt"
       -- more?
FROM   tbl t
JOIN  (
    SELECT id, count(*) AS ct
    FROM   other_tbl
    GROUP  BY id
    ) o USING (id);

ELSE NULL is optional in a CASE expression. The manual:

If the ELSE clause is omitted and no condition is true, the result is null.

Basics for both techniques:

PostgreSQL Crosstab Query

score 0 · Answer 2 · edited Mar 11 '16 at 16:28

Script for SelectTopNRows command from SSMS

drop table #yourtable;

create table #yourtable(id int, location varchar(25));

insert into #yourtable values
('1','Singapore'),
('2','Vancouver'),
('3','Egypt'),
('4','Tibet'),
('5','Crete'),
('6','Monaco');


drop table #temp;

create table #temp( col1 int );

Declare @Script as Varchar(8000);

Declare @Script_prepare as Varchar(8000);

Set @Script_prepare = 'Alter table #temp Add [?] varchar(100);'
Set @Script = ''

Select
  @Script = @Script + Replace(@Script_prepare, '?', [location])
From
  #yourtable 
Where
  [id] is not null

Exec (@Script);
ALTER TABLE #temp DROP COLUMN col1 ;

select * from #temp;

this will do exactly what you are looking for. – Hazell B Mar 11 '16 at 15:55 — Hazell B, Mar 11 '16 at 15:55

score 0 · Answer 3 · answered Mar 18 '12 at 23:45

0

You could do this with some really messing dynamic sql but I wouldn't recommend it.

However you could produce something like below, let me know if that stucture is acceptable and I will post some sql.

Location | Count
---------+------
Singapore| 1
Vancouver| 0
Egypt    | 2  
Tibet    | 1
Crete    | 3
Monaco   | 0

answered Mar 18 '12 at 23:45

Ash Burlaczenko

24,778
15
68
99

Unfortunately not, I do need something that produces the location names as headings and their respective counts as values. – user1277546 Mar 18 '12 at 23:55

SQL SELECT statement, column names as values from another table

3 Answers3