October 13, 2014 at 10:26 am
I have the following working in SQL 2008 R2 but can't get it to work on a SQL 2000 box.... looks like the function (XML PATH) isn't supported in this version (SQL 2000). Any workaround?
SELECT
n1.ID,
STUFF((SELECT ', ' + n.FIRST_NAME
FROM Name n
where n1.id = n.co_id and n.Member_Type = 'CHILD'
group by co_id,first_name
for xml PATH('')), 1, 1,'')[Kids]
FROM Name n, Name n1
GROUP BY n1.ID, n.first_name
ORDER BY n.first_Name DESC
Thanks
Roger
October 14, 2014 at 3:13 pm
I'm afraid it won't work in 2000.
You have very few options:
1) use a cursor to concatenate the rows
2) use a variable in the select list
However, neither solution will correlate happily with the outer query.
Probably your best option is to let the client app consume the results and concatenate the rows on the client side.
-- Gianluca Sartori
October 14, 2014 at 10:47 pm
Quick SQL Server 2000 solution, both limited and cumbersome but works
😎
USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE #SAMPLE_DATA
(
SD_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,SD_GRID INT NOT NULL
,SD_NAME VARCHAR(25) NOT NULL
);
INSERT INTO #SAMPLE_DATA(SD_GRID, SD_NAME)
SELECT 1,'Alan' UNION ALL
SELECT 1,'Bill' UNION ALL
SELECT 2,'Chris' UNION ALL
SELECT 2,'Dave' UNION ALL
SELECT 3,'Eric' UNION ALL
SELECT 3,'Fabio' UNION ALL
SELECT 3,'Graham';
SELECT
SD.SD_GRID
,MIN(SD.SD_NAME + ISNULL(',' + SD2.SD_NAME,'') + ISNULL(',' + SD3.SD_NAME,'')) AS NameString
FROM #SAMPLE_DATA SD
LEFT OUTER JOIN #SAMPLE_DATA SD2
ON SD.SD_GRID = SD2.SD_GRID
AND SD.SD_NAME <> SD2.SD_NAME
LEFT OUTER JOIN #SAMPLE_DATA SD3
ON SD.SD_GRID = SD3.SD_GRID
AND SD2.SD_NAME <> SD3.SD_NAME
AND SD.SD_NAME <> SD3.SD_NAME
GROUP BY SD.SD_GRID;
DROP TABLE #SAMPLE_DATA;
Results
SD_GRID NameString
----------- -------------------
1 Alan,Bill
2 Chris,Dave
3 Eric,Fabio,Graham
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply