can we do looping in select query.

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  July 8, 2009 at 6:12 am

  #1021588

  try this:

  CREATE TABLE purchase --table:

  (Date DATETIME,

  Location VARCHAR(100),

  Purchase_id INT)

  INSERT INTO purchase

  SELECT '2009-12-24', 'Dadar', 123

  CREATE TABLE Item --table:

  (Item_name VARCHAR(100),

  Item_id INT)

  INSERT INTO Item

  SELECT 'Laptop', 21 UNION ALL

  SELECT 'Mouse', 22 UNION ALL

  SELECT 'Speaker', 23

  CREATE TABLE relatio --table:

  (Purchase_id INT,

  Item_id INT,)

  INSERT INTO relatio

  SELECT 123,21 UNION ALL

  SELECT 123,22 UNION ALL

  SELECT 123,23

  -- Starting data

  SELECT * FROM purchase

  SELECT * FROM Item

  SELECT * FROM relatio

  SELECT

  Date,

  Location,

  STUFF((SELECT ',' + Item_name

  FROM Item i INNER JOIN relatio r ON r.Item_Id = i.Item_Id

  WHERE r.Purchase_Id = p.Purchase_id

  FOR XML PATH(''))

  ,1,1,'') as [Item]

  FROM purchase p

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life

• lavanya.pothu

  SSC Enthusiast

  Points: 111

  More actions

  July 8, 2009 at 7:16 am

  #1021637

  thanks a lot.

  u r genius