Different ways to alias a column
I'd prefer the first one, since the second one is not portable -
select EmployeeName = empName from employees
is either a syntax error (at least in SQLite and Oracle), or it might not give you what you expect (comparing two columns EmployeeName and empName and returning the comparison result as a boolean/integer), whereas
select empName EmployeeName from employees
is the same as
select empName as EmployeeName from employees
which is my preferred variant.
How to reuse column alias in another column within same select statement?
In SQL Server, you can use APPLY
to define column aliases in the FROM
clause -- a good place, in my opinion:
select pd.id v.value_1,
(Case when value_1 in ('test1', 'test2') Then '0'
else value_1
End) as value_2
from pd cross apply
(values ( case . . . )
) v(value_1);
Notes:
- Don't use identifier names that need to be escaped, hence
value_1
instead ofvalue 1
. value_1
appears to be string. Thecase
expression forvalue_2
should return a string, so'0'
instead of0
.- I define table aliases using
as
after the expression. This is the SQL standard, but there is nothing per se wrong with using=
, which is SQL Server specific syntax.
Dynamic column alias from another column value in SELECT
It seems that there is no way to create the column alias dynamically without knowing the values since the beginning. As many commented the only way to achieve this kind of "table re-mapping" is to use the crosstab
function.
Crosstab function summary
This function takes 2 arguments:
- The first one is a SQL statement that must return 3 columns:
- The first column contains the values identifying each instance and that must be grouped in order to get the final result.
- The second column contains the values that are used as categories in the final pivot table: each value will create a separate column.
- The third column contains the values used to compile the new columns formed: for each category this column has the value of the instance that had the category value in the original table.
- The second argument is not mandatory and is a SQL statement that returns the distinct values the function should use as categories.
Example
In the example above we must pass a query to crosstab that:
- Returns as the first column the identifier of each final instance (in this case
id
) - As second column the values used as categories (all values in
key
) - As third column the values used to fill the categories (all values in
value
)
So the final query should be:
select * from crosstab(
'select "id", "key", "value" from testTable order by 1, 2;',
'select distinct "key" from testTable order by 1;'
) as result ("id" int8, "a" text, "b" text);
Since the crosstab function requires a column definition for the final pivot table, there is no way to determine the column alias dynamically.
Dynamically infer column names with client
A possible way to do that, with a PostgreSQL client, is to launch the second query we passed as argument to crosstab in order to retrieve the final columns and then infer the final crosstab query.
As an example, with pseudo-javascript:
const client;
const aliases = client.query(`select distinct "key" from testTable order by 1;`);
const finalTable = client.query(`select * from crosstab(
'select "id", "key", "value" from testTable order by 1, 2;',
'select distinct "key" from testTable order by 1;'
) as result ("id" int8, ${aliases.map(v => v + ' data_type').join(',')});`)
Useful articles
https://learnsql.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/
How to select values with a column alias?
I think you are looking for this:
SELECT *
FROM (VALUES (1), (2), (3)) AS tbl(col)
WHERE tbl.col = 1;
Note that when you escape an identifier (using "tbl.col"
), then that is one name that has a period in it. Not two names.
EDIT:
I would have expected the above to work, but it doesn't in SQLite. One alternative is to use a CTE:
with tbl(col) as (
VALUES (1), (2), (3)
)
SELECT *
FROM tbl
where tbl.col = 1
I want to change column alias dynamically as current_date() in bigquery
Consider below approach
execute immediate ('''
select * except(row_id) from (
select metric, dN, current_date - N as col, row_id
from (
select "inspection" as metric, d1, d2, d3, d4, d5, d6, to_json_string(t) as row_id
from your_table t
), unnest([
if(d1 is null,"insp_not_done",d1),
if(d2 is null,"insp_not_done",d2),
if(d3 is null,"insp_not_done",d3),
if(d4 is null,"insp_not_done",d4),
if(d5 is null,"insp_not_done",d5),
if(d6 is null,"insp_not_done",d6)
]) dN with offset as N
)
pivot (min(Dn) for col in (''' ||
(select string_agg("'" || date || "'", ',' order by offset desc)
from unnest(generate_date_array(current_date - 5, current_date)) date with offset) || "))"
)
if apply to dummy data like in below example
create temp table your_table as (
select '1' d1, null d2, '3' d3, '4' d4, null d5, '6' d6 union all
select '21' d1, '22' d2, null d3, '24' d4, '25' d5, '26' d6
);
output is
Related Topics
What Does It Mean to Have Jobs with a Null Stop Date
Unique Constraint Controlled by a Bit Column
Sqlserver Case Expressions - Short Circuit Evaluation
How to Use SQL Server Compact Edition Ce on Mono
Find Most Common Elements in Array with a Group By
Put Pg_Try_Advisory_Xact_Lock() in a Nested Subquery
MySQL Duplicates with Load Data Infile
Replace Row Value with Empty String If Duplicate
Ms Access 2010 Running Total in Query
Access Query Counter Per Group
Date Calculation with Parameter in Ssis Is Not Giving the Correct Result
Conditions in Left Join (Outer Join) VS Inner Join
From Keyword Not Found Where Expected (Oracle SQL)
Select * from Table or Select Id,Field1, Field2, Field3 from Table - Best Practice
Extract Characters to the Right of a Delimited Value in a Select Statement