July 17, 2012 at 2:14 pm
here i am having three table in this i want to coalesce fname depend on artid
these are the 3 tables
declare @cat table
(
catid int,
categoryname varchar(20)
)
insert into @cat
select 1,'cricket' union all
select 2,'football'
declare @art table
(
artid int,
title varchar(20),
catid int
)
insert into @art
select 1,'batsman',1 union all
select 2,'bowler',1
declare @img table
(
imgid int,
artid int,
fname varchar(30)
)
insert into @img
select 1,1,'leftbatsman' union all
select 2,1,'rightbatsman' union all
select 3,2,'leftbowler' union all
select 4,2,'rightbowler'
when we select this tables
select* from @art
select* from @img
select* from @cat
catidcategoryname
1cricket
2football
artidtitlecatid
1batsman1
2bowler1
imgidartidfname
11leftbatsman
21rightbatsman
32leftbowler
42rightbowler
when i joined these the 3 tables and i pass the parameter @catid
declare @catid int=1
Select a.artid,
a.catid,
a.Title,
ai.fname,
c.CategoryName
from @art a
left join @img ai
on a.artid=ai.artid
inner join @cat c
on a.catid=c.catid
where a.catid=@catid
the output will be like this
artidcatidTitlefname CategoryName
11batsmanleftbatsmancricket
11batsmanrightbatsmancricket
21bowlerleftbowler cricket
21bowlerrightbowlercricket
and now i just tried to coalesce fname depend upon artid pass the parameter @catid
declare @catid int=1
Declare @fname varchar(max)
SELECT
@fname = COALESCE(@fname+ ',', '') + cast(ai.fname as varchar(max))
from @art a
left join @img ai
on a.artid=ai.artid
inner join @cat c
on a.catid=c.catid
where a.catid=@catid
Select a.artid,
a.catid,
a.Title,
@fname as fname,
c.CategoryName
from @art a
left join @img ai
on a.artid=ai.artid
inner join @cat c
on a.catid=c.catid
where a.catid=@catid
i am getting output like this
artidcatidTitlefname CategoryName
11batsmanleftbatsman,rightbatsman,leftbowler,rightbowlercricket
11batsmanleftbatsman,rightbatsman,leftbowler,rightbowlercricket
21bowlerleftbatsman,rightbatsman,leftbowler,rightbowlercricket
21bowlerleftbatsman,rightbatsman,leftbowler,rightbowlercricket
but i am trying the output like this
artidcatidTitlefname CategoryName
11batsmanleftbatsman,rightbatsmancricket
21bowlerlleftbowler,rightbowler cricket
can any one plz tell me how to write select query depend upon artid passing parameter @catid only
July 17, 2012 at 2:42 pm
Here is one way. study it and be sure to ask questions where you don't understand what is happening.
Select distinct
a.artid,
a.catid,
a.Title,
STUFF(
(SELECT ',' + im1.fname
FROM @img im1
WHERE im1.artid = ai.artid
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
, 1,1,SPACE(0)
),
c.CategoryName
from @art a
left join @img ai
on a.artid=ai.artid
inner join @cat c
on a.catid=c.catid
where a.catid=@catid
July 17, 2012 at 3:07 pm
thanks lynn it was working fine thanks a lot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply