Add a value to each line returned from a union

ttist25
ttist25 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead
Commented:
I would like to add a "faux" column to the results to identify  which table each row came from.

I thought you already derived a "Source" column in your cte ? it should generating the result as what you have been shown in your question.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
It appears that you have already done it.  As long as all queries in a UNION have the same schema you can populate columns however you wish,  either with table.column values or descriptive hard-coded values.
awking00Information Technology Specialist

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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 HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.

Author

Commented:
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 HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.

Author

Commented:
Yep.  Nailed it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial