asked on

sql query help

I have a query and what I am trying to do is for the same location create a list of days
The data looks like this

locationID      availableDays
97      1
97      2
104      1
104      2
104      3
104      4
104      5

What I need is this
locationID availableDays
97 1,2
104 1,2,3,4,5

SELECT 
      locationID,
      availableDays
  FROM lkup_locationByDay Ld
  CROSS APPLY (
		SELECT cast(ll.availableDays as VARCHAR) + ','
		from lkup_locationByDay LL
		where ld.ID = ll.id
		FOR XML PATH('')
  )AS cross1(AVAILlOCATIONS)
  
  where locationID in (4,5,97,104,110,111,112,211,232,226,256)
  order by locationID

Open in new window

Éric Moreau

have a look at http://emoreau.com/Entries/Blogs/2009/10/SQL-Concatenate-values-multiple-from-multiple-lines.aspx

Jim Horn

Bingo bango..

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
   DROP TABLE #tmp
GO

CREATE TABLE #tmp (locationID int, availableDays int)

INSERT INTO #tmp (locationID, availableDays) 
VALUES (97,1), (97,2), (104,1), (104,2), (104,3), (104,4), (104,5)

SELECT locationID, LEFT(availableDays, LEN(availableDays) -1) as details
FROM ( 
   SELECT DISTINCT t1.locationID,
      stuff((
      SELECT ' ' + CAST(availableDays as varchar(10)) + ', '
      FROM #tmp t2 
      WHERE t1.locationID = t2.locationID
      FOR XML PATH('')), 1, 1, '') as availableDays
   FROM #tmp t1) a

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial