Combining data from multiple sources in one column Let's say I have a tables like

    create table dbo.A
    (
        Id int identity(1,1) not null,
        BId int null,
        CId int null,
        SomeData nvarchar(50),
        constraint [PK_A] primary key clustered ([Id] asc),
        constraint [FK_A_B] foreign key (BId) references [dbo].[B]([Id]),
        constraint [FK_A_C] foreign key (CId) references [dbo].[C]([Id]),
    )
    go

    create table dbo.B
    (
        Id int identity(1,1) not null,
        constraint [PK_B] primary key clustered ([Id] asc)
    )
    go


    create table dbo.C
    (
        Id int identity(1,1) not null,
        BId int null,
        constraint [PK_C] primary key clustered ([Id] asc),
        constraint [FK_C_B] foreign key (BId) references [dbo].[B]([Id]),
    )
    go


I would like to get back data that would have following in row: 
A.Id, C.Id, A.SomeData where C.Id could be coming either from A.CId directly or as C.Id if you join A to by C on A.BId = C.BId. So let's say in my tables I have following data:

    B
    1 
    2

    C 
    1 1
    2 1
    3 2
    4 2

    A
    1 1 3 'test1'


Sample SQL to insert test data:

    SET IDENTITY_INSERT ON
    go

    insert B(Id)
        select 1
                union all
        select 2;

    insert C(Id, BId)
        select 1, 1
            union all
        select 2, 1
            union all
        select 3, 2
            union all
        select 4, 2;

    insert A(Id, BId, CId, SomeData)
        values(1, 1, 3, 'test1');
    go

    SET IDENTITY_INSERT OFF
    go


What I would like to get back is:

    1 1 'test1'
    1 2 'test1'
    1 3 'test1'
It seems you want a UNION:

SELECT id AS aid,  cid,  somedata 
FROM a

UNION -- ALL

SELECT a.id,  c.id,  a.somedata
FROM a JOIN c ON a.bid = c.bid ;


It's not clear if you want a UNION DISTINCT or UNION ALL. If it's the first, you could combine the subqueries into one, using OR. That may produce different plan though, which may be less efficient:

SELECT a.id AS aid,  c.id AS cid,  a.somedata
FROM a JOIN c ON a.bid = c.bid 
              OR a.cid = c.id ;