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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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?
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?
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.
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.
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!
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!
>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.
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.
ASKER
Yep. Nailed it.