We help IT Professionals succeed at work.

Add a value to each line returned from a union

ttist25
ttist25 asked
on
66 Views
Last Modified: 2018-09-17
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

CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
awking00Information Technology Specialist
CERTIFIED EXPERT

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.

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions