• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

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

0
erikTsomik
Asked:
erikTsomik
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
 
Scott PletcherSenior DBACommented:
Your original query just needs some slight adjustments:

  SELECT
      ld.locationID,
      STUFF(cross1.AVAILlOCATIONS, 1, 1, '') AS availableDays
  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,211,232,226,256)
  ORDER BY locationID
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now