can any one plz tell me how to write a select store proc for this using four or more table for report generating ? RSS

• • Reply
  

  sivaganesh12...

  Member

  227 Points

  308 Posts

  can any one plz tell me how to write a select store proc for this using four or more table for re...

  May 04, 2012 04:39 AM|LINK

  for example here i am using five table?
   table1
  catid categoryname 
  1         cricket
  2         football
  table2
  sid catid(fk) subcatgoryname

  1       1            bowling
  2       1             batting
  3       1            fielding
  4       2            foul
  5      2            goal
  
   table3
  adforumid memberid (fk)  adforumname activesstatus
  1                         1                inform               1

   table4
  adinfid  adforumid(fk) cid(fk)   sid(fk)
  1               1                     1         1
  2               1                     1         2
  3               1                     2         4
  
  table 5
  memberid membername firstname lastname
  1                   john              paul         stepen
  2              andrew              sam         winson
  
  i just want to take report depend on table 3 activestatus in where condition like this

  DECLARE @Names VARCHAR(8000)
  
  SELECT
  
  @Names = COALESCE(@Names + ', ', '') + (CatId+ '/' +CatName
  
  + ','SubCatId+'|'+SubCatName,SubCatId|SubCatName,..]))
  from
  table4
  left outer join
  table1 on catid=catid
  left outer join
  table2 on subid=subid
  left outer join
  table3
  on adforumid=adforumid
  left outer join
  table5 on memberid=memberid
  where
  activestatus =1
  
  select
  
  adinfid
  ,membername
  ,memberid
  ,adforumname
  , @Names as names
  
  from
  table4
  left outer join
  table1 on catid=catid
  left outer join
  table2 on subid=subid
  left outer join
  table3
  on adforumid=adforumid
  left outer join
  table5 on memberid=memberid
  where
  activestatus =1

  
  
  

  
  in this one store proc iam expecting output like this
  
  
  adinfid membername memberid adforumname   Names 
  1             john                   1            inform           [1| cricket,1| bowling,2| batting] [2|football,4|foul] 
  
   can any one plz try to help me by solving this problem

  [CatId1|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..][CatId2|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..][CatId2|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..][CatId2|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..]

  • Edited by sivaganesh1234 on May 04, 2012 04:52 AM
  • Edited by sivaganesh1234 on May 04, 2012 04:57 AM
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: can any one plz tell me how to write a select store proc for this using four or more table fo...

  May 04, 2012 07:31 AM|LINK

  are you looking for this type of output?

  let me know.

  declare @table1 table
  (
  	catid int,categoryname varchar(100)
  )
  insert into @table1
  select 1,'cricket' union all
  select 2,'football'
  
  declare @table2 table
  (
  	sid int,catid int,subcatgoryname varchar(100)
  )
  insert into @table2
  select 1,1,'bowling' union all
  select 2,1,'batting' union all
  select 3,1,'fielding' union all
  select 4,2,'foul' union all
  select 5,2,'goal'
  
  declare @table3 table
  (
  	adforumid int,memberid int,adforumname varchar(100),activesstatus bit
  )
  insert into @table3
  select 1,1,'inform',1
  
  declare @table4 table
  (
  	adinfid int,adforumid int,cid int,sid int
  )
  insert into @table4
  select 1,1,1,1 union all
  select 2,1,1,2 union all
  select 3,1,2,4
  
  declare @table5 table
  (
  	memberid int,membername varchar(100),firstname varchar(100),lastname varchar(100)
  )
  insert into @table5
  select 1,'john','paul','stepen' union all
  select 2,'andrew','sam','winson'
  
  --[1| cricket,1| bowling,2| batting]
  DECLARE @Data VARCHAR(1000)
  SET @Data=''
  
  select '['+CAST(t1.catid as varchar(10))+'/'+t1.categoryname+','+Cast(t2.sid as varchar(10))
  +' | '+t2.subcatgoryname+cast(t2.sid as varchar(10))+']'
  from @table3 as t3
  inner join @table5 as t5 on t3.memberid=t5.memberid
  inner join @table4 as t4 on t3.adforumid=t4.adforumid
  inner join @table1 as t1 on t4.cid=t1.catid
  inner join @table2 as t2 on t4.cid=t2.catid
  where t3.activesstatus=1
  GROUP BY t1.catid,t1.categoryname,t2.subcatgoryname,t2.sid
  
  --output
  --(No column name)
  --[1/cricket,2 | batting2]
  --[1/cricket,1 | bowling1]
  --[1/cricket,3 | fielding3]
  --[2/football,4 | foul4]
  --[2/football,5 | goal5]

  
  
  

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: can any one plz tell me how to write a select store proc for this using four or more table fo...

  May 04, 2012 07:57 AM|LINK

  declare @table1 table ( catid int,categoryname varchar(100) )
  insert into @table1
  select 1,'cricket' union all
  select 2,'football'
  
  declare @table2 table ( sid int,catid int,subcatgoryname varchar(100))
  insert into @table2
  select 1,1,'bowling' union all
  select 2,1,'batting' union all
  select 3,1,'fielding' union all
  select 4,2,'foul' union all
  select 5,2,'goal'
  
  declare @table3 table (adforumid int,memberid int,adforumname varchar(100),activesstatus bit)
  insert into @table3
  select 1,1,'inform',1
  
  declare @table4 table(adinfid int,adforumid int,cid int,sid int)
  insert into @table4
  select 1,1,1,1 union all
  select 2,1,1,2 union all
  select 3,1,2,4
  
  declare @table5 table(memberid int,membername varchar(100),firstname varchar(100),lastname varchar(100))
  insert into @table5
  select 1,'john','paul','stepen' union all
  select 2,'andrew','sam','winson'
  
  ;with cte1 as(
  	select t4.adinfid, t5.membername, t5.memberid, t3.adforumname
  			, t1.catid, t1.categoryname, t2.sid, t2.subcatgoryname
  	from @table3 as t3
  	inner join @table5 as t5 on t3.memberid=t5.memberid
  	inner join @table4 as t4 on t3.adforumid=t4.adforumid
  	inner join @table1 as t1 on t4.cid=t1.catid
  	inner join @table2 as t2 on t4.cid=t2.catid and t2.sid = t4.sid
  	where t3.activesstatus=1
  	
  )
  , cte2 as(
  	select memberid, membername, adforumname
  		, '[' + CAST(catid as varchar) + '|' + categoryname + ','+ stuff(
  		(select ', '+Cast(t2.sid as varchar(10)) +'| '+t2.subcatgoryname
  		From cte1 t2 where t2.memberid = t1.memberid and t1.catid = t2.catid for xml path('')),1,1,'') + ']' as name
  	from cte1 t1
  	group by memberid, membername, adforumname, catid, categoryname
  )
  
  select t1.memberid, t1.membername, t1.adforumname
  		, stuff((select ' ' + name
  		   from cte2 t2 where t1.memberid=t2.memberid and t1.adforumname = t2.adforumname
  		   for xml path('')
  		 ),1,1,'') as name
  from cte2 t1
  group by t1.memberid, t1.membername, t1.adforumname

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on May 04, 2012 08:45 AM
  • Marked as answer by Chen Yu - MSFT on May 08, 2012 05:57 AM
• • Reply
  

  sivaganesh12...

  Member

  227 Points

  308 Posts

  Re: can any one plz tell me how to write a select store proc for this using four or more table fo...

  May 04, 2012 09:48 AM|LINK

   thanks yogi  the out  put i want to get a single row  can u please  help  me

  adinfid membername memberid adforumname   Names 
  1             john                   1            inform           [1| cricket,1| bowling,2| batting] [2|football,4|foul] 

  
  

  
  

• • Reply
  

  sivaganesh12...

  Member

  227 Points

  308 Posts

  Re: can any one plz tell me how to write a select store proc for this using four or more table fo...

  May 04, 2012 09:50 AM|LINK

  thanks  

  sandeepmittal11     

  i am  also trying your  store proc also

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: can any one plz tell me how to write a select store proc for this using four or more table fo...

  May 04, 2012 09:51 AM|LINK

  check sandeep's query, its work for you, which is below one (same as above)

  declare @table1 table ( catid int,categoryname varchar(100) )
  insert into @table1
  select 1,'cricket' union all
  select 2,'football'
  
  declare @table2 table ( sid int,catid int,subcatgoryname varchar(100))
  insert into @table2
  select 1,1,'bowling' union all
  select 2,1,'batting' union all
  select 3,1,'fielding' union all
  select 4,2,'foul' union all
  select 5,2,'goal'
  
  declare @table3 table (adforumid int,memberid int,adforumname varchar(100),activesstatus bit)
  insert into @table3
  select 1,1,'inform',1
  
  declare @table4 table(adinfid int,adforumid int,cid int,sid int)
  insert into @table4
  select 1,1,1,1 union all
  select 2,1,1,2 union all
  select 3,1,2,4
  
  declare @table5 table(memberid int,membername varchar(100),firstname varchar(100),lastname varchar(100))
  insert into @table5
  select 1,'john','paul','stepen' union all
  select 2,'andrew','sam','winson'
  
  ;with cte1 as(
          select t4.adinfid, t5.membername, t5.memberid, t3.adforumname
                          , t1.catid, t1.categoryname, t2.sid, t2.subcatgoryname
          from @table3 as t3
          inner join @table5 as t5 on t3.memberid=t5.memberid
          inner join @table4 as t4 on t3.adforumid=t4.adforumid
          inner join @table1 as t1 on t4.cid=t1.catid
          inner join @table2 as t2 on t4.cid=t2.catid and t2.sid = t4.sid
          where t3.activesstatus=1
          
  )
  , cte2 as(
          select memberid, membername, adforumname
                  , '[' + CAST(catid as varchar) + '|' + categoryname + ','+ stuff(
                  (select ', '+Cast(t2.sid as varchar(10)) +'| '+t2.subcatgoryname
                  From cte1 t2 where t2.memberid = t1.memberid and t1.catid = t2.catid for xml path('')),1,1,'') + ']' as name
          from cte1 t1
          group by memberid, membername, adforumname, catid, categoryname
  )
  
  select t1.memberid, t1.membername, t1.adforumname
                  , stuff((select ' ' + name
                     from cte2 t2 where t1.memberid=t2.memberid and t1.adforumname = t2.adforumname
                     for xml path('')
                   ),1,1,'') as name
  from cte2 t1
  group by t1.memberid, t1.membername, t1.adforumname
  
  
  --output
  --memberid membername adforumname name
  --1 john inform [1|cricket, 1| bowling, 2| batting] [2|football, 4| foul]

  
  
  

  Yogesh Bhadauriya
  .Net All About