Collapsing 1->M into a single row

• Rick Hodder

  Old Hand

  Points: 359

  More actions

  March 27, 2007 at 12:02 pm

  #172605

  I'm trying to create a query that will collapse a 1->M into a single row with each of the values from the many side as fields

  So if I have a table of facilities,

  id               description

  1                Maryland

  2                Delaware

  3                Washington

   

  I want to create a single row where the facility ids are columns

   

  FacA     FacB    FacC

  1          2         3

   

  I've only gotten as far as getting a comma delimited list of the ids:

  <code>

  DECLARE @Facilities TABLE (facility_id INT, description VARCHAR(50))

  INSERT INTO @Facilities (facility_id , description ) VALUES (1,'Facility 1')

  INSERT INTO @Facilities (facility_id , description ) VALUES (2,'Facility 2')

  INSERT INTO @Facilities (facility_id , description ) VALUES (3,'Facility 3')

  DECLARE @FacilityList TABLE (facility_id int)

  INSERT INTO @FacilityList (Facility_id)

  SELECT Facility_ID FROM @Facilities

  DECLARE @CommaDelimList varchar(max)

  SET @CommaDelimList=''

  SELECT @CommaDelimList=@CommaDelimList+CASE WHEN @CommaDelimList<>'' THEN ',' ELSE '' END + LTRIM(STR(facility_id))

  FROM @FacilityList

  SELECT @CommaDelimList

  </code>

  Any suggestions?

  ---

  Thanks,

  Rick Hodder

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 27, 2007 at 12:55 pm

  #697257

  this is what is known as a cross tab query; you are converting rows to columns. you can search for cross tab or pivot to find more examples:

  here's an example with your data:

  create table #test(id int,description varchar(30) )

  insert into #test(id,description) values(1,'Maryland')

  insert into #test(id,description) values(2,'Delaware')

  insert into #test(id,description) values(3,'Washington')

  insert into #test(id,description) values(1,'somethingelse')

  insert into #test(id,description) values(2,'more')

  insert into #test(id,description) values(3,'data')

  SELECT

  CASE WHEN id=1 then description else NULL END AS FACA,

  CASE WHEN id=2 then description else NULL END AS FACB,

  CASE WHEN id=3 then description else NULL END AS FACC

  FROM #TEST

  sample results:

  FACA	FACB	FACC
  Maryland	NULL	NULL
  NULL	Delaware	NULL
  NULL	NULL	Washington
  somethingelse	NULL	NULL
  NULL	more	NULL
  NULL	NULL	data

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!