mssql query to list unique records

• arunvinoth

  Newbie

  Points: 3

  More actions

  May 22, 2008 at 11:27 pm

  #191491

  hi

  let me explain my need..

  following are the tables im using..

  tbl_company (company table - parent)

  idcompany

  1test

  2test123

  tbl_dept (department table - master)

  iddept

  1dept1

  2dept2

  tbl_compdept (company departments table - child)

  cmpiddeptid

  1 1

  2 1

  2 2

  wats my need is.. while the company is listing..

  by query using joins, result was like this..

  companydept

  test dept1

  test123 dept1

  test123 dept2

  i need company test123 should be listed only once..

  when i use group by or distinct means, all r listed..

  is there any way to filter out the repeating company list by just listing the company list only once..

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  May 23, 2008 at 12:41 am

  #819498

  [font="Verdana"]Do you mean you need something like this?

  company dept

  test dept1

  test123 dept1,dept2

  If so, go for Pivot.

  http://msdn.microsoft.com/en-us/library/ms177410.aspx?wt.slv=RightRail

  Mahesh

  [/font]

  MH-09-AM-8694

• Mark Beringer-730383

  SSCommitted

  Points: 1824

  More actions

  May 27, 2008 at 5:11 am

  #820456

  Try this:

  select

  c1.company,

  (

  select

  c.dept + ', ' as [text()]

  from

  comp c

  where

  c.company = c1.company

  order by

  dept

  for xml path ('')

  ) as Department

  from

  (select distinct company from comp) c1

  Just change the query to use your tables as I created one table instead of joining to tables.

  Works great!