Need help with a query

• DBABC

  SSCarpal Tunnel

  Points: 4813

  More actions

  September 28, 2009 at 1:10 pm

  #210204

  Table : Company

  Id Name

  1 ABC

  2 XYZ

  Table : Details

  Id Desc CompanyId

  1 p1 1

  2 p2 1

  3 p1 2

  4 p2 2

  5 p3 2

  I need following result set from these tables.

  CompanyId, Name, DetailCount, DetailDesc

  1 ABC 2 p1;p2

  2 XYZ 3 p1;p2;p3

  can anyone help?

  Thanks,

  Usman

• GSquared

  SSC Guru

  Points: 260824

  More actions

  September 28, 2009 at 1:14 pm

  #1059171

  You should be able to modify this to fit your needs:

  /*

  Multi-row string concat using XML and string functions.

  Pretty slick trick. Got it from RBarryYoung and Lynn Pettis on SSC.

  */

  if object_id(N'tempdb..#T') is not null

  drop table #T;

  create table #T (

  ID int identity primary key,

  SetID int not null,

  Val varchar(100));

  insert into #T (SetID, Val)

  select 1, 'a' union all

  select 1, 'b' union all

  select 2, '1' union all

  select 2, '2' union all

  select 3, 'a<b' union all

  select 3, 'c>b';

  select distinct

  SetID,

  stuff( -- Gets rid of leading comma

  (select ', ' + Val -- No column name gets rid of that part in the XML

  from #T t2

  where t2.SetID = t1.SetID

  for xml

  path(''), -- ('') gets rid of row tags

  TYPE).value('.[1]', 'varchar(MAX)') -- value query allows XML characters

  ,1,2,'') -- part of Stuff statement

  from

  #T t1;

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon