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
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
Who is Participating?
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
SELECT
ld.locationID,
STUFF(cross1.AVAILlOCATION
FROM (
SELECT DISTINCT locationID
FROM lkup_locationByDay
) AS Ld
CROSS APPLY (
SELECT ',' + cast(ll.availableDays as VARCHAR)
from lkup_locationByDay LL
where ld.locationID = ll.locationid
FOR XML PATH('')
)AS cross1(AVAILlOCATIONS)
WHERE locationID in (4,5,97,104,110,111,112,21
ORDER BY locationID