display multiple row data in single row RSS

• • Reply
  

  rabindra_lal

  Member

  515 Points

  202 Posts

  display multiple row data in single row

  Apr 13, 2012 06:01 AM|LINK

  Brachmaster
  brachid branchname
  1    Bmach
  2       cmach
  3       dmach
  
  
  Qualificationmaster
  qualid    qualname
  1       bca
  2       mca
  3       dca
  
  disciplinemaster
  
  despid   brachid   qualid    despname
  
  1    1,2          1,2               machanical
  
  2        2         2,3                electrical
  
  
  
  
  output
  
  1   bmach,cmach          bca,mca          machanical

  plz help me how to write query for this result

  • Edited by rabindra_lal on Apr 13, 2012 06:01 AM
• • Reply
  

  sandeepmitta...

  Contributor

  6779 Points

  1058 Posts

  Re: display multiple row data in single row

  Apr 13, 2012 11:16 AM|LINK

  DECLARE @Brachmaster TABLE( branchid INT, branchname VARCHAR(10))
  INSERT INTO @Brachmaster
  SELECT 1,'Bmach'
  UNION ALL SELECT 2,'cmach'
  UNION ALL SELECT 3,'dmach'
  
  DECLARE @Qualificationmaster TABLE( qualid INT, qualname VARCHAR(10))
  INSERT INTO @Qualificationmaster
  SELECT 1,'bca'
  UNION ALL SELECT 2,'mca'
  UNION ALL SELECT 3,'dca'
  
  DECLARE @disciplinemaster TABLE(despid INT, branchid VARCHAR(10), qualid VARCHAR(10), despname VARCHAR(10))
  INSERT INTO @disciplinemaster
  SELECT 1, '1,2', '1,2', 'machanical'
  UNION ALL SELECT 2, '2', '2,3', 'electrical'
  
  --Soluction----
  /*
  	CREATE FUNCTION dbo.split(
  	  @delimited NVARCHAR(MAX),
  	  @delimiter NVARCHAR(100)
  	) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
  	AS
  	BEGIN
  	  DECLARE @xml XML
  	  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
  
  	  INSERT INTO @t(val)
  	  SELECT  r.value('.','varchar(MAX)') as item
  	  FROM  @xml.nodes('/t') as records(r)
  	  RETURN
  	END
  */
  ;WITH cteBranch AS (
  	SELECT t.despid, t.branchid, branchname
  	FROM (
  		SELECT despid, val as branchid
  		FROM @disciplinemaster d cross apply dbo.split(branchid, ',')
  	) t INNER JOIN @Brachmaster b on t.branchid = b.branchid
  )
  SELECT despid, stuff((SELECT ',' + branchname FROM cteBranch t2 where t1.despid = t2.despid for xml path('')),1,1,'') as branch into #branch
  FROM cteBranch t1
  GROUP BY despid
  
  ;with cteQual as (
  	SELECT t.despid, t.qualid, qualname
  	FROM (
  		SELECT despid, val as qualid
  		FROM @disciplinemaster d cross apply dbo.split(qualid, ',')
  	) t INNER JOIN @Qualificationmaster b on t.qualid = b.qualid
  )
  SELECT despid, stuff((SELECT ',' + qualname FROM cteQual t2 where t1.despid = t2.despid for xml path('')),1,1,'') as qual into #qual
  FROM cteQual t1
  GROUP BY despid
  
  SELECT d.despid, b.branch, q.qual, d.despname
  FROM @disciplinemaster d
  INNER JOIN #branch b on d.despid = b.despid
  INNER JOIN #qual q on d.despid = q.despid
  
  DROP TABLE #branch
  DROP TABLE #qual

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Marked as answer by rabindra_lal on Apr 13, 2012 11:25 AM