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
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
JOIN (
SELECT id, count(*) AS ct
FROM other_tbl
GROUP BY id
) o USING (id);
ELSE NULL
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:
source to share
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
source to share
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;
source to share