dynamic alias in T-SQL query
DECLARE @sql VARCHAR(1000);
SET @sql = 'SELECT
1234 AS REVENUE' + CAST (year(DATEADD(year,-1,getdate())) AS VARCHAR(20)) + ',
4321 AS REVENUE' + CAST (year(DATEADD(year,-2,getdate())) AS VARCHAR(20))
PRINT @sql
EXEC (@sql)
It is impossible to do that in the static SQL query. So only this way.
Dynamic Aliases in the SQL statement
I'm not sure if you can add dynamic aliases, but you should be able to do something like this (if you have only a few possible aliases):
SELECT
CASE P.Type WHEN 'Individual' THEN P.Amount ELSE NULL END AS Salary,
CASE P.Type WHEN 'Individual' THEN NULL ELSE P.Amount END AS Profit
FROM
Person p
dynamic alias in sql server
Approach without dynamic SQL:
--I create temp table for demonstration
DECLARE @some_table TABLE (
Something int,
EmpCode INT
)
INSERT INTO @some_table (Something, EmpCode)
VALUES (1, 10),(1, 22),(1, 12),(2, 12),(2, 30),(3, 65),(3, 15),(3, 11),(3, 5)
--Declare parameter we want to search
DECLARE @param int = 1
--Query
--In cte we select what we need based on parameter
;WITH cte AS (
SELECT 'CountEmp'+CAST(@param as nvarchar(10)) as SomeThing,
CAST(COUNT(EmpCode) as nvarchar(10)) as EmpCodeCount,
ROW_NUMBER() OVER (ORDER BY SomeThing ) as rn
FROM @some_table
WHERE SomeThing = @param
GROUP BY SomeThing
)
--And here comes UNION
SELECT SomeThing as Result
FROM (
SELECT SomeThing,rn
FROM cte
UNION ALL
SELECT EmpCodeCount ,rn
FROM cte
) as t
ORDER BY rn, SomeThing DESC
Output:
Result
------------------
CountEmp1
3
(2 row(s) affected)
T-SQL Dynamic alias without using dynamic SQL
No, you cannot change the name of the alias based on the value unless you use dynamic SQL.
When you are selecting the columns, you can only have one name/alias for each column.
If you want different column names, then you could use some like the following which uses different select statements:
IF @testing = 'choice'
select 1 as 'Chose'
ELSE
select 1 as 'didntChoose'
Or you could return two separate columns:
select
case when @testing = 'choice' then 1 else 0 end Chose,
case when @testing <> 'choice' then 1 else 0 end DidNotChose
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/
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
How to Use a Reserved Word in SQL as a Table Name
How to Run the Same Query Multiple Times Using Loop in Pl/Sql
SQL Server: Self-Reference Fk, Trigger Instead of on Delete Cascade
SQL Count of Open Orders Each Day Between Two Dates
SQL Server Performance for Alter Table Alter Column Change Data Type
SQL Server: How to Imitate Oracle Keep Dense_Rank Query
Search a JSON Array for an Object Containing a Value Matching a Pattern
Accessing Column Alias in Postgresql
Pivoting Variable Number of Rows to Columns
Creating a Flattened Table/View of a Hierarchically-Defined Set of Data
MySQL Deadlock Explanation Needed
How to Pivot on an Xml Column's Attributes in T-Sql
Convert Nvarchar to Datetime in SQL Server 2008
Simple Update Statement So That All Rows Are Assigned a Different Value