sql script assistance

• BaldingLoopMan

  SSCertifiable

  Points: 6378

  More actions

  February 5, 2010 at 7:21 am

  #220272

  drop table #temp

  create table #temp

  (

  offerid int

  , Catg varchar(100)

  )

  insert into #temp( offerid, Catg )

  select 1, 'health' union all

  select 1, 'fitness' union all

  select 1, 'weights' union all

  select 2, 'personal' union all

  select 3, 'yoga' union all

  select 3, 'health' union all

  select 3, 'stretch' union all

  select 4, 'weights' union all

  select 4, 'fitness' union all

  select 4, 'workout' union all

  select 4, 'stretch' union all

  select 5, 'fitness' union all

  select 5, 'health' union all

  select 6, 'health'

  --desired output

  offerid|catg

  1 | health, fitness, weights

  2 | personal

  3 | yoga, health, stretch

  4 | weights, fitness, workout, stretch

  5 | fitness, health

  6 | health

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 5, 2010 at 8:05 am

  #1114460

  here you go;

  this is using the FOR XML trick to concat your values together:

  create table #temp

  (

  offerid int

  , Catg varchar(100)

  )

  insert into #temp( offerid, Catg )

  select 1, 'health' union all

  select 1, 'fitness' union all

  select 1, 'weights' union all

  select 2, 'personal' union all

  select 3, 'yoga' union all

  select 3, 'health' union all

  select 3, 'stretch' union all

  select 4, 'weights' union all

  select 4, 'fitness' union all

  select 4, 'workout' union all

  select 4, 'stretch' union all

  select 5, 'fitness' union all

  select 5, 'health' union all

  select 6, 'health'

  SELECT offerid,stuff(( SELECT ',' + Catg

  FROM #temp s2

  WHERE s2.offerid= s1.offerid --- must match GROUP BY below

  ORDER BY Catg

  FOR XML PATH('')

  ),1,1,'') as [Categories]

  FROM #temp s1

  GROUP BY s1.offerid --- without GROUP BY multiple rows are returned

  ORDER BY s1.offerid

  drop table #temp

  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!

• Jeff Moden

  SSC Guru

  Points: 1004392

  More actions

  February 5, 2010 at 9:22 pm

  #1114915

  BaldingLoopMan (2/5/2010)

  ---

  drop table #temp

  create table #temp

  (

  offerid int

  , Catg varchar(100)

  )

  insert into #temp( offerid, Catg )

  select 1, 'health' union all

  select 1, 'fitness' union all

  select 1, 'weights' union all

  select 2, 'personal' union all

  select 3, 'yoga' union all

  select 3, 'health' union all

  select 3, 'stretch' union all

  select 4, 'weights' union all

  select 4, 'fitness' union all

  select 4, 'workout' union all

  select 4, 'stretch' union all

  select 5, 'fitness' union all

  select 5, 'health' union all

  select 6, 'health'

  --desired output

  offerid|catg

  1 | health, fitness, weights

  2 | personal

  3 | yoga, health, stretch

  4 | weights, fitness, workout, stretch

  5 | fitness, health

  6 | health

  BLM,

  I always have to ask when I see this type of denormalization... why do you need to do this? I mean, what are the business requirements?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• BaldingLoopMan

  SSCertifiable

  Points: 6378

  More actions

  July 28, 2010 at 12:07 pm

  #1199420

  sorry, didnt realize u had question.,

  I dont recall what it was for. i think i was generating a file and the end user consuming this file wanted the categories in their own column delimited my columns

• Jeff Moden

  SSC Guru

  Points: 1004392

  More actions

  July 29, 2010 at 7:23 am

  #1199780

  BaldingLoopMan (7/28/2010)

  ---

  sorry, didnt realize u had question.,

  I dont recall what it was for. i think i was generating a file and the end user consuming this file wanted the categories in their own column delimited my columns

  Heh... thanks for the feedback but that's not actually the business reason I was hoping for. It's always some user/customer somewhere that wants this type of denormalization... what I really like to find out is why they think they want (for example) the categories in their own comma delimited columns. IE, what in the heck were they thinking and what are they actually going to do with the result?

  Anyway... I realize this was a while ago and that the customer (and the real reason behind the request) are probably long gone. Thanks for trying.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)