SQL dynamic ORDER BY using alias
Column aliases defined in the SELECT
can only be used in the ORDER BY
on their own. Not in an expression.
You can adjust your original attempt as follows.
;WITH T
AS (SELECT u.FirstName + ' ' + u.LastName AS PhysicianName,
COUNT(r.Id) AS ReferralsCount
FROM Referrals r
INNER JOIN Users u
ON r.PhysicianId = u.Id
GROUP BY r.PhysicianId,
u.FirstName,
u.LastName)
SELECT *
FROM T
ORDER BY CASE
WHEN @orderby = 'PhysicianName' THEN PhysicianName
END,
CASE
WHEN @orderby = 'ReferralsCount' THEN ReferralsCount
END
Dynamic Order By clause with column and sort information coming from a table?
This will only work if you are using dynamic SQL. I was not able to get it to work as-is.
ORDER BY clause is not sorting by alias in SQL Server
You have the column UserName
and an alias UserName
. It is the column value that is being used to sort the results, not the alias. While it is perfectly acceptable to use an alias name inside the ORDER BY
clause, it cannot be used inside CASE WHEN
statement.
The solution is to use a sub-query (or CTE):
DECLARE
@Sortorder VARCHAR(5) = 'asc',
@ColumnNumber INT = 9
SELECT * FROM (
SELECT SUBSTRING(csu.UserName, /* removed for readability */) AS UserNameCopy, w.WorkItemId
FROM [tasks].[WorkItems] w
LEFT JOIN operations.CustomerServiceUser csu ON csu.UserId = w.AssignedToUserId
WHERE w.[ShowInTaskList] = 1 AND UserName IS NOT NULL
) AS SubQuery
ORDER BY
CASE WHEN @ColumnNumber = 9 AND @SortOrder = 'asc' THEN SubQuery.UserNameCopy END ASC,
CASE WHEN @ColumnNumber = 9 AND @SortOrder = 'desc' THEN SubQuery.UserNameCopy END DESC
ORDER BY with a CASE statement for column with alias
Try this
ORDER BY
CASE WHEN @sortColumnName = 'LastPayCheckDate' AND @sortDirection = 'ASC'
THEN MAX(PayCheckDate) END ASC,
CASE WHEN @sortColumnName = 'LastPayCheckDate' AND @sortDirection = 'DESC'
THEN MAX(PayCheckDate) END DESC
Example
create table Test (id int, somevalue int)
insert Test values(1,1)
insert Test values(2,1)
insert Test values(3,2)
insert Test values(3,2)
insert Test values(4,2)
run this in 1 shot
declare @sortDirection char(4)
select @sortDirection = 'DESC'
select somevalue, COUNT(*)
from Test
group by somevalue
order by case when @sortDirection = 'ASC'
then COUNT(*) end asc,
case when @sortDirection = 'DESC'
then COUNT(*) end desc
select @sortDirection = 'ASC'
select somevalue, COUNT(*)
from Test
group by somevalue
order by case when @sortDirection = 'ASC'
then COUNT(*) end asc,
case when @sortDirection = 'DESC'
then COUNT(*) end desc
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)
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/
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
Related Topics
Database View Does Not Reflect The Data in The Underying Table
How to Insert Data Directly from Excel to Oracle Database
Equivalent of on Conflict Do Nothing for Update Postgres
How to Set Numwidth in The Grid Output of Pl/Sql Developer
Group by Week, How to Get Empty Weeks
Sql Create Statement Incorrect Syntax Near Auto Increment
Understanding Bitmap Indexes in Postgresql
Oracle SQL Developer 3.1.07 Extra Spaces Between Characters Using Listagg
What Is The Advantage of Using Fast_Forward for Defining a Cursor
Creating Trigger That Runs on Two Tables
Get The Second Highest Value with Standard Sql
Sql Dynamic Order by Using Alias
Inserting a String with Double Quotes into a Table
String_Agg Not Behaving as Expected
Sql Field with Multiple Id's of Other Table