how to select multiple same column in one column

Question

I have two tables that is :

theatre(id,tname,taddress)
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.

How do you want them displayed? Also, what are the column names are in the second table? — Radu Gheorghiu
– Radu Gheorghiu, Commented Jul 30, 2013 at 10:02

Prahalad Gaggar · Accepted Answer · 2013-07-30 10:03:14Z

4

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

answered Jul 30, 2013 at 10:03

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

VIPUL PARMAR Over a year ago

Thanks, I want to display data like : Theare name Movie : Big Cinema Race BMB SOTY

Prahalad Gaggar Over a year ago

@VIPULPARMAR Check this Fiddle And the SQL Query for the same is select [tname] TheatreName, stuff((select ',' + CAST(t2.[mname] as varchar(10)) from movie t2 where t1.[id] = t2.[tid] for xml path('')),1,1,'') Movie from theatre t1 group by [id],[tname]

VIPUL PARMAR Over a year ago

I want show this movie name in gridview. So it can be possible. Pls reply me.

VIPUL PARMAR Over a year ago

I don't have C# code i want to design this. That has one gridview first Headerfield is theatreName, second Headerfield has another datalist and in datalist shows all movie name. So how it is possible pls reply me.! Thanks

VIPUL PARMAR Over a year ago

Hi I wan't to design gridview inside another gridview with insert,update, and delete and table are 1) tbl_Show(ShowID{P},ShowTime,Silver,Gold, Platiniam,ShowTimeId) 2) tbl_STime(ShowTimeId{P},TheatreId,MovieId,FromDate,ToDate) 3) tbl_Theatre(TheatreId{P},TName,Taddress) I wan't to design same like this so visit www.codeproject.com/Articles/14293/Gridview-Inside-a-GridView-in-ASP-NET-2-0 so please help me thanks.

wertyk · Accepted Answer · 2013-07-30 10:07:00Z

0

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

answered Jul 30, 2013 at 10:07

wertyk

40810 silver badges31 bronze badges

1 Comment

VIPUL PARMAR Over a year ago

Thanks, I want to display data like : Theare name, Movie : Big Cinema, Race, BMB, SOTY

Collectives™ on Stack Overflow

how to select multiple same column in one column

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related