SQLTeam.com | Weblogs | Forums

Using aliases in Common Table Expression (CTE)

Good day

How do I use an alias for the outer query. Other column alises are fine except for t_region which I want
to be displayed as Region. Am trying to have an output as displayed below. Meaning for Region A we have user1 and 2 instead of repeating Region A value for user 2.

t_region Username Opened Closed Rerouted
Region A user1 5 5 2
user2 0 2 1

Region B user3 3 2 2

I have tried so many statements and this is the one I could get results except that I get all sorts of errors when trying to have an alias for for column t_region
Your assistance appreciated

WITH Cte AS (
SELECT *,
Rn = ROW_NUMBER() OVER(PARTITION BY t_region ORDER BY (SELECT NULL))
FROM #TEMP (temporary table)
)
SELECT
t_region = CASE WHEN Rn = 1 THEN (t_region) ELSE '' END ,
(t_username) Username , ISNULL(t_open,0) Opened, ISNULL(t_close,0) Closed, ISNULL( t_rerouted,0) ReRouted
FROM Cte c
ORDER BY c.t_region

Welcome. help us help you. provide sample data with proper column data type.

create table #TEMP(t_region int, t_username varchar(50), 
t_open bit, t_close bit, t_rerouted bit )

insert into #TEMP
select 1, 'Darth Vader', 0, 1, 0

...
)
SELECT
CASE WHEN Rn = 1 THEN (t_region) ELSE '' END AS Region,
...

t_region = ... alias is t_region
... As t_region alias is t_region

I don't think you can have both.

It would help - quite a bit - if you shared the errors you are getting. We have no way of knowing.

Also, whether you alias the table or not (and you should always alias your tables) - you need to use that alias when referencing the columns.

And - you really shouldn't be using T-SQL for display. That should be done in the report or the application.

Quite right, sorry. Corrected.

I have action log table with the following fields,
Region, date, action, username with sample data as follows:-

REGION DATE_TIME ACTION USERNAME
Region A 2021-06-02 open user1
Region A 2021-06-02 close user1
Region A 2021-06-02 rerouted user1
Region A 2021-06-10 open user2
Region B 2021-06-02 close user2
Region B 2021-06-02 open user2

CREATE TABLE #TEMP
(
t_region varchar(30),
t_username varchar(30),
t_open int,
t_close int,
t_rerouted int,
)

#TEMP is a temporary table built from action log table to get the totals per action, for each user in a region
When executing the query am expecting the results as follows:

Region Username Opened Closed Rerouted
Region A user1 1 1 1
user2 1 0 0
Region B user1 1 1 0

If you look at the result set above for both user1 and user2 both worked in region A and all I want to achieve is not to have a repetitive values for Region Column. My challenge is I am unable to create an alias for column t_region

Thank you for your assistance

Thanks for the feedback. Kindly note that am not getting an error. All I need to do is to have a proper column name using an alias instead of t_region which is the field name.

I have action log table with the following fields,
Region, date, action, username with sample data as follows:-

REGION DATE_TIME ACTION USERNAME
Region A 2021-06-02 open user1
Region A 2021-06-02 close user1
REGION A 2021-06-02 rerouted user1
REGION A 2021-06-10 open user2
REGION B 2021-06-02 close user2
REGION B 2021-06-02 open user2

CREATE TABLE #TEMP
(
t_region varchar(30),
t_username varchar(30),
t_open int,
t_close int,
t_rerouted int,
)

#TEMP is a temporary table built from action log table to get the totals per action, for each user in a region
When executing the query am expecting the results as follows:

Region Username Opened Closed Rerouted
REGION A user1 1 1 1
User2 1 0 0
REGION B user1 1 1 0

If you look at the result set above for both user1 and user2 both worked in region A and all I want to achieve is not to have a repetitive values for Region Field. My challenge is I am unable to create an alias for column t_region with this code snippet below

.....
WITH Cte AS (
SELECT *,
Rn = ROW_NUMBER() OVER(PARTITION BY t_region ORDER BY (SELECT NULL))
FROM #TEMP
)
SELECT
t_region = CASE WHEN Rn = 1 THEN (c.t_region) ELSE '' END ,
(t_username) Username , ISNULL(t_open,0) Opened, ISNULL(t_close,0) Closed, ISNULL( t_rerouted,0) ReRouted
FROM Cte c
ORDER BY c.t_region

@Phefos look at the following and post back with your data with insert statement. We are not going to do that for you. Its a good practice

create table #TEMP(t_region int, t_username varchar(50), 
t_open bit, t_close bit, t_rerouted bit )

insert into #TEMP

You need to provide your data with an insert statememt

This is not going to work

REGION DATE_TIME ACTION USERNAME
Region A 2021-06-02 open user1
Region A 2021-06-02 close user1
REGION A 2021-06-02 rerouted user1
REGION A 2021-06-10 open user2
REGION B 2021-06-02 close user2
REGION B 2021-06-02 open user2

Hi Phefos

Here is the create table and insert data script
( i corrected lower case of Region A unless its supposed to be like that )

create table Data 
(
REGION varchar(10) , DATE_TIME date , ACTION varchar(10), USERNAME varchar(10)
)

insert into data select 'REGION A', '2021-06-02', 'open','user1'
insert into data select 'REGION A', '2021-06-02', 'close','user1'
insert into data select 'REGION A', '2021-06-02', 'rerouted','user1'
insert into data select 'REGION A', '2021-06-10', 'open','user2'
insert into data select 'REGION B', '2021-06-02', 'close','user2'
insert into data select 'REGION B', '2021-06-02', 'open','user2'

select 'Data',* from data 

drop table Data 

image

Thank you hashgg1 for your response..

My challenge is am unable to create an aliase for t_region field..I have this code snippet below. As soon as am trying to create an alias for t_region I get errors ie 'Invalid column name'..

With the snippet below I get the results as expected only challenge is how do I create an alias for the column t_region..If there are better ways of doing this please assist
WITH Cte AS (
SELECT *,
Rn = ROW_NUMBER() OVER(PARTITION BY t_region ORDER BY (SELECT NULL))
FROM #TEMP
)
SELECT
t_region = CASE WHEN Rn = 1 THEN (c.t_region) ELSE '' END ,
(t_username) Username , ISNULL(t_open,0) Opened, ISNULL(t_close,0) Closed, ISNULL( t_reindex,0) ReRouted
FROM Cte c
ORDER BY c.t_region

With the data generated I would like to have end results as follows:

Region Username Opened Closed Rerouted
REGION A user1 1 1 1
user2 1 0 0
REGION B user1 1 1 0

Thank you

hi Phefos

you can create an alias in many different ways ( i mean way is one ..but at different points in the query )

hope this helps ..just sample so that you can see

;WITH create_alias
     AS (SELECT region AS t_region,
                date_time,
                action,
                username
         FROM   #temp),
     cte
     AS (SELECT *,
                Rn = Row_number()
                       OVER(
                         partition BY region
                         ORDER BY (SELECT NULL))
         FROM   #temp)
SELECT *
FROM   cte

Your table doesn't have a t_region column - therefore the following will fail:

Rn = ROW_NUMBER() OVER(PARTITION BY t_region ORDER BY (SELECT NULL))

Change it to:

Rn = ROW_NUMBER() OVER(PARTITION BY region ORDER BY (SELECT NULL))

Since the column name is 'region' - the following will also fail:

t_region = CASE WHEN Rn = 1 THEN (c.t_region) ELSE '' END ,

Chang it to:

t_region = CASE WHEN Rn = 1 THEN (c.region) ELSE '' END ,

In fact - everywhere you try to reference the column name as t_something will fail because the actual column names do not include the t_.

In one statement you stated you were getting all kinds of errors - which we have no idea what they are - then you state you are not getting any errors and just need a column alias.

So - which is it? Are you getting errors - or just incorrect data - or something else?

Thank you Harrish for the feedback. Resolved after removing the field name before Case statement

Thank you Jeff for the feedback. Resolved after removing the field name(t_region) before Case statement