CODEWITHSUNDEEP
sqlsql-server-2008
Kevin M.
Kevin M.

Reputation: 139

Insert Record for Each Foreign Key to Result Set

I have a table like this:

Id   Employee_Group_Id     Name
1    256                   Tom
2    256                   Dick
3    256                   Harry
4    257                   Jane
5    257                   Lucy
6    258                   Bill
7    259                   Fraser
8    260                   Sally

I need a select statement for this table that will include all the employee group ID and name information above, plus this (inserted row can be anywhere in the query):

Employee_Group_Id     Name
256                   SOMEVALUE
256                   Tom   
256                   Dick
256                   Harry
257                   SOMEVALUE
257                   Jane
257                   Lucy
258                   SOMEVALUE
258                   Bill
259                   SOMEVALUE
259                   Fraser
260                   SOMEVALUE
260                   Sally

Upvotes: 0

Views: 25

Answers (1)

Szymon
Szymon

Reputation: 43023

You can just union 2 queries and give each record an order and then use that as the subquery:

select Employee_Group_Id, Name
from
(
    select Employee_Group_Id, Name, 2 as OrderValue from table1
    union all
    select distinct Employee_Group_Id, 'SOMEVALUE' as Name, 1 as OrderValue from table1
) X
order by Employee_Group_Id, OrderValue

Upvotes: 1

Related Questions