SQL SELECT, column names as values ​​from another table

I am working on a database that has the following table:

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


My question is how can I create a query from this that will result in column names like the following without typing them into the query:

Query result:

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



source to share

3 answers

how can I create a query that will result in the column names, for example, after that without writing them to the query:

Even with (from the tablefunc extension ) you have to specify the column names. crosstab()

Also if you create a custom C function for your request. The tablefunc extension provides a framework for this, however the output columns (list of countries) must be stable. A few days ago I wrote a tutorial for a similar case:

An alternative is the use of operators CASE

such as:

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
    SELECT id, count(*) AS ct
    FROM   other_tbl
    GROUP  BY id
    ) o USING (id);



is optional in the expression CASE

. Manual:

If the clause is ELSE

omitted and no condition is true, the result is zero.

Basics for both methods:



You can do this with some really messy dynamic sql, but I wouldn't recommend it.

However, you can create something like below, let me know if this schema is acceptable and I'll post some sql.

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




Script for SelectTopNRows command from SSMS

drop table #yourtable;

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

insert into #yourtable values

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 = ''

  @Script = @Script + Replace(@Script_prepare, '?', [location])
  [id] is not null

Exec (@Script);

select * from #temp;




All Articles