CODEWITHSUNDEEP
sql-serversql-server-2008
VIPUL PARMAR
VIPUL PARMAR

Reputation: 292

how to select multiple same column in one column

I have two tables that is :

  1. theatre(id,tname,taddress)
  2. movie(id,mname,tid).

In theatre table there is one record with id 1, like : 

 1, Big Cinema, abc.

And in moive table there are 3 record like :

1) 1, Race, 1.
2) 2, BMB, 1.
3) 3, SOTY, 1.

Now I want to show theatre name only one time and with all three movie.

Upvotes: 1

Views: 98

Answers (2)

wertyk
wertyk

Reputation: 408

If did you mean to concatinate some columns in one column you can try 

Select t.tname + ' ' + t.taddress as tNameAdress, m.mname 
from movie m inner join theatre t on m.tid = t.id

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Is this what you need ?

select [id],[tname],
    stuff((select ',' + CAST(t2.[mname] as varchar(10))
    from movie t2 where t1.[id] = t2.[tid]
    for xml path('')),1,1,'') SomeColumn
from theatre t1
group by [id],[tname]

SQL Fiddle

Upvotes: 4

Related Questions