Using aliases in Common Table Expression (CTE)

Transact-SQL
1

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

2

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
3

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

4

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

I don't think you can have both.

5

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.

6

Quite right, sorry. Corrected.

7

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

8

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

9

@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

10

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

11

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

12

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
13

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?

14

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

15

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