Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
Solved
sql query help
Posted on 2015-01-13
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
3 Comments
Active today
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
Active today
Your original query just needs some slight adjustments:
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
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
Featured Post
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.
Join & Write a Comment Already a member? Login.
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month5 days, 19 hours left to enroll
569 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.