Manipulating non-normalized table data 2

[NuJoizey]

Say I have data in tblMovie like so:

MovieTitle               Genre       Director
--------------------     ------      --------
Sleepless In Seattle     Comedy      Jones
Sleepless In Seattle     Romance     Jones
Die Hard2                Action      Smith
When Harry Met Sally     Romance     Jones

If I execute:

SELECT MovieTitle FROM tblMovie WHERE Director = "Jones", I would get:

MovieTitle               Genre       Director
--------------------     ------      --------
Sleepless In Seattle     Comedy      Jones
Sleepless In Seattle     Romance     Jones
When Harry Met Sally     Romance     Jones

The result set would return 3 titles - but that's not really accurate, because it really should only be two titles.


Without using a reporting tool, in TSQL is there a way to return a result set that looks something like:

MovieTitle               Genre               Director
--------------------     ------              --------
Sleepless In Seattle     Comedy/Romance      Jones
When Harry Met Sally     Romance             Jones

 

[hmckillop]

Hello

there are two examples in the faq, though the first one is a more direct fit.

Have a look at

http://www.tek-tips.com/faqs.cfm?fid=6466

cheers


"I'm living so far beyond my income that we may almost be said to be living apart

 

[NuJoizey]

thank you for that link.

 

[RyanEK]

Hi,

If you're using SQL2005, you can use xml path and do it in one query...

declare @tblmovie table (movie varchar(20), genre varchar(20), director varchar(20))
insert into @tblmovie values('Sleep', 'Comedy', 'Jones')
insert into @tblmovie values('Sleep', 'Romance', 'Jones')
insert into @tblmovie values('Die', 'Action', 'Smith')
insert into @tblmovie values('Harry', 'Romance', 'Jones')

select t1.movie, t2.genre, t1.director
from @tblmovie t1
join (
 select movie,
 replace((
     select genre as 'data()'
     from @tblmovie t2 
     where t1.movie = t2.movie
     for xml path('')),' ',',') as genre
 from @tblmovie t1
 group by movie) as t2 on t1.movie = t2.movie
where director = 'Jones'
group by t1.movie, t1.director, t2.genre

Ryan

 

[hmckillop]

Ryan,

very tidy - you should supplement the faqs and add this in with a brief explanation, but definitely worth a star.

Cheers

"I'm living so far beyond my income that we may almost be said to be living apart

 

[NuJoizey]

actually, i happen to be using oracle for this one, and since i frequent this board I forgot that it was specific to MSSQL. So I should probably repost there, as there is probably a corresponding pl sql way to do it. But these examples are helpful nonetheless, and I do thank you all for your posts.

 

[markros]

Very similar question to this one is here

http://forums.asp.net/p/1429938/3202537.aspx#3202537

Happened just yesterday, as usual.