Avatar of ttist25
ttist25
 asked on

Add a value to each line returned from a union

Hello,

I'm creating a CTE using two select statements and a UNION of two tables.  I would like to add a "faux" column to the results to identify  which table each row came from.

Something like:

;WITH
MyCTE AS (
    SELECT field1, field2, field3, 't1' as Source
     FROM table1
     UNION
     SELECT  field1, field2, field3, 't2' as Source
     FROM table2
)

The results would look like:
---------------------------------------------
field1 | field2 | field3 | Source
---------------------------------------------
   A     |      B    |     C     |     T2
   C     |      R    |     G     |     T1
---------------------------------------------

Any idea how I can do that?

THanks!
Microsoft SQL Server

Avatar of undefined
Last Comment
ttist25

8/22/2022 - Mon
SOLUTION
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
awking00

Since there will always be a difference with the "faux" columns, I would use union all to improve performance since a sort would not be required.
ttist25

ASKER
Hey guys - you are right.  What I failed to include in my example query was that I was using SELECT DISTINCT blah in my UNION queries which was pointless in this situation.  The distinct is what was preventing me from adding the Source column alias.  

Thanks for the responses and sorry for the delay in mine.  Been getting my butt kicked on something else.  

PS - Jim - what is the trophy in your new pic?
Jim Horn

It's a foot-and-a-half knockoff of the NHL's Stanley Cup that is awarded every year for their championship team.  

I play in the Adult Hockey Association, which is in the Twin Cities of Minnesota and has 180+ amateur adult hockey (aka Beer League) teams spread across ten divisions.  Last year my Wood Haulers team won the championship in our division, so after the championship game we were awarded the cup, and just like the professionals we all make silly 'We're Number One!' poses with it.   We took a 2-2 tie into overtime and a shootout, and as the goalie I got us to a shootout win after eight shots.   The picture was taken in the locker room after the game.  

After that roller coaster of emotion which capped off a four-day tournament I may have had three working brain cells.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ttist25

ASKER
Eight shots denied???!  Nice job!!!!!

I'm finding that amateur hockey is a relatively popular past time among many of the "data folks" I work with.  

Wonder what that says about us?  :D

Congrats!
Jim Horn

>Wonder what that says about us?  :D
I think my neighbor nailed that one.  He thinks our fathers had mostly manual labor jobs so when they finished their work day they were exhausted and wanted to drink and relax, so golf and softball were the big sports.  Our generation has mostly sit-down, less-than-social jobs, so when we finish our work day we want to tell raunchy jokes to our buddies and then go out and hit something.
ttist25

ASKER
Yep.  Nailed it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.