asked on

Is it possible to create a temporary column during a sql query?

If I am joining two tables, is it possible t create a temporary column for a table, strictly for the sake of me doing the sql query? I wouldn't want to make the additional column permanent, as it would only be used for the sake of the query?

For example, below suppose Table_1 has two columns, and Table_2 has three columns. For my query, I would like to create temporary column for table 1 named "StudentId", populate it with an incrementing unique integer starting at 1, and going as high as needed.

I suppose I could use a temp table, however if I could create a temporary column, that would be great.

Table_1
FirstName LastName StudentId
Jerry Grisselton
Merry Drakeson

Table_2
Jerry Grisselton SocialSSN
Merry DrakeSon

ASKER CERTIFIED SOLUTION

bbao

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

Requirment:
"For example, below suppose Table_1 has two columns, and Table_2 has three columns. For my query, I would like to create temporary column for table 1 named "StudentId", populate it with an incrementing unique integer starting at 1, and going as high as needed."

Select FirstName
    , LastName
    , row_Number() over (order by FirstName, LastName) as StudentId
From Table_1

Open in new window

This will give you:
FirstName LastName StudentId
Jerry Grisselton 1
Merry Drakeson 2

Question: What do you want to do with Table_2?

After joining, as you say, what will be the sample output?

Mike

SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

Tables as you know, have a primary key in them. Assuming your tables also each have like:

Table_1
ID FirstName LastName StudentId
xxx Jerry Grisselton
xxx Merry Drakeson

Table_2
ID FirstName LastName SocialSSN
xxx Jerry Grisselton
xxx Merry DrakeSon

The best is use them to link your table via:

Select row_Number() over (order by t.FirstName, t.LastName) as StudentId
    , t.FirstName
    , t.LastName
    , tt.SocialSSN
From Table_1 t Inner Join Table_2 tt
On t.ID = tt.ID

Open in new window

brgdotnet

ASKER

Thank you.