Multiple rows into single row for distinct records

• Sagar Sawant

  SSC Eights!

  Points: 853

  More actions

  March 12, 2012 at 1:58 am

  #258532

  Hi guys,

  I have a table:

  DECLARE @Tbl AS TABLE

  (

  pk_Id INT PRIMARY KEY NOT NULL IDENTITY(1,1),

  tblId INT,

  tblNo NVARCHAR(50),

  dt_StartTime DATETIME,

  dt_EndTime DATETIME

  )

  In that, there are multiple records with same table numbers but different timings

  INSERT INTO @Tbl

  SELECT 19473,4,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19474,5,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19471,2,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19475,6,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19473,4,'2012-03-12 12:30:00.000','2012-03-12 13:00:00.000'

  UNION ALL

  SELECT 19475,6,'2012-03-12 12:30:00.000','2012-03-12 13:00:00.000'

  UNION ALL

  SELECT 19489,7,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19473,4,'2012-03-12 13:00:00.000','2012-03-12 13:30:00.000'

  UNION ALL

  SELECT 19489,7,'2012-03-12 13:00:00.000','2012-03-12 13:30:00.000'

  Now I would like to generate below result. So it should display table number with their dining timings by comma separated.

  tblNo DiningTime

  212:00 PM-12:30 PM

  412:00 PM-12:30 PM,12:30 PM-01:00 PM,01:00 PM-01:30 PM

  512:00 PM-12:30 PM

  612:00 PM-12:30 PM

  712:00 PM-12:30 PM,01:00 PM-01:30 PM

  Any help would be greatly appreciated.

  Thanks in advance.

• Sagar Sawant

  SSC Eights!

  Points: 853

  More actions

  March 12, 2012 at 2:32 am

  #1457700

  I got solution.

  So mine query will be like this:

  DECLARE @Tbl AS TABLE

  (

  pk_Id INT PRIMARY KEY NOT NULL IDENTITY(1,1),

  tblId INT,

  tblNo NVARCHAR(50),

  dt_StartTime DATETIME,

  dt_EndTime DATETIME

  )

  In that, there are multiple records with same table numbers but different timings

  INSERT INTO @Tbl

  SELECT 19473,4,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19474,5,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19471,2,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19475,6,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19473,4,'2012-03-12 12:30:00.000','2012-03-12 13:00:00.000'

  UNION ALL

  SELECT 19475,6,'2012-03-12 12:30:00.000','2012-03-12 13:00:00.000'

  UNION ALL

  SELECT 19489,7,'2012-03-12 12:00:00.000','2012-03-12 12:30:00.000'

  UNION ALL

  SELECT 19473,4,'2012-03-12 13:00:00.000','2012-03-12 13:30:00.000'

  UNION ALL

  SELECT 19489,7,'2012-03-12 13:00:00.000','2012-03-12 13:30:00.000'

  Now I would like to generate below result. So it should display table number with their dining timings by comma separated.

  tblNo DiningTime

  2 12:00 PM-12:30 PM

  4 12:00 PM-12:30 PM,12:30 PM-01:00 PM,01:00 PM-01:30 PM

  5 12:00 PM-12:30 PM

  6 12:00 PM-12:30 PM

  7 12:00 PM-12:30 PM,01:00 PM-01:30 PM

  To get above result, So query should be like this

  SELECT i.tblNo,STUFF(g.dt_StartTime, 1, 1, '') AS DiningTime

  FROM

  (

  SELECT tblNo FROM @Tbl GROUP BYtblNo

  ) AS i

  CROSS APPLY

  (

  SELECT DISTINCT',' + CONVERT(VARCHAR(12),dt_StartTime,14)+' - '+CONVERT(VARCHAR(12),dt_EndTime,14)

  FROM@Tbl AS s

  WHEREs.tblNo = i.tblNo

  ORDER BY',' + CONVERT(VARCHAR(12),dt_StartTime,14)+' - '+CONVERT(VARCHAR(12),dt_EndTime,14)

  FOR XMLPATH('')

  ) AS g(dt_StartTime)

  Cheers