CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user1681166
user1681166

Reputation: 241

combine 2 columns from 2 tables into one column

I have tab1 with following data

ID |  Name
1  |   furniture
2  |   Electronic
3  |   Books

tab2 as

foreignkeyID | Name

1            |   sofa
1            |   chair
3            |   c#
3            |   sql
2            |   fan
2            |   tv

I want result set as 

Name
furniture
sofa
chair
Electronic
FAN
TV
Books
c#
sql

I achieve the above result set using cursor ,while loop ,how will i get the desire output without looping

Upvotes: 4

Views: 120

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

try this,

Declare @tab1 table(ID int,  Name varchar(40))
insert into @tab1 values(1 ,'furniture')
,(2,'Electronic'),(3,'Books')

declare @tab2 table(foreignkeyID int, Name varchar(40))
insert into @tab2 values
(1,'sofa'),
(1,'chair'),(3,'c#'),(3,'sql'),
(2,'fan'),(2,'tv')

select name from
(select 1 rn, id, name from @tab1
union all
select 2, foreignkeyID, name from @tab2
)t4
order by  id,rn

Upvotes: 4

Rhys Jones
Rhys Jones

Reputation: 5508

A simple UNION could do the job. Or for something a bit different, you can use a recursive CTE. Normally recursive CTEs are used when there are multiple levels but there's no reason why you can't use the same technique for just 1 level. 

declare @t1 table (id int, category varchar(100))
insert @t1 values (1, 'furniture'), (2, 'Electronic'), (3, 'Books')

declare @t2 table (id int, item varchar(100))
insert @t2 values (1, 'sofa'), (1, 'chair'), (3, 'c#'), (3, 'sql'), (2, 'fan'), (2, 'tv')

-- Approach 1 : UNION

; with cte as (
    select
        id, 0 as level, category as name
    from
        @t1
    union all
    select
        id, 1 as level, item as name
    from
        @t2
)
select
    name
from
    cte
order by
    id, level

-- Approach 2 : Recursive CTE

; with cte as (
    select
        id, category as name, 0 as level
    from @t1
    union all
    select 
        t2.id, t2.item, level + 1 
    from
        @t2 t2 
            inner join cte on cte.id = t2.id
                         and level = 0
)
select
    name
from
    cte
order by
    id, level

Upvotes: 1

Related Questions