October 20, 2009 at 2:56 pm
The table has 3 fields
ID, FILE_NO, STATUS
I need a query or view that shows the data for each ID, concatenate FILE_NO fields with commas between as one field.
e.g.
ID FILE_NO
3 152144
3 222777
4 566466
4 777888
4 999111
would look like this:
ID NEWFIELD
3 152144, 222777
4 566466, 777888, 999111
Any help is greatly appreciated!!!
October 20, 2009 at 5:32 pm
A "search" on this site is still one of the best ways to find what you need...
When you run across one of the many articles or scripts on this subject, make sure you read the "Join the Discussion" thing... sometimes the solutions in those discussions are a lot better than the articles themselves.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2009 at 6:20 pm
Ok - after reading more I'm still stuck. This is what I was trying to emulate:
http://www.sqlservercentral.com/articles/Test+Data/61572/
Mytable:
ID (identity/key field)
PID (non unique number field)
FILE_NO (6 character field that I want to concatenate)
I created the following function:
CREATE FUNCTION dbo.fnConcatTest (@PID INT)RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return+', ','')+FILE_NO
FROM dbo.MYTABLE
WHERE PID = PID
ORDER BY ID
RETURN @Return
END
but when I do this:
SELECT DISTINCT PID, dbo.fnConcatTest(FILE_NO) AS CSVString FROM dbo.MYTABLE
it's putting every FILE_NO in a string for each PID - not the FILE_NO's that necessarily go with that PID.
Here's sample data from my table.
ID PID FILE_NO
283157169
3353167843
295157170
3230157506
11830163859
I was hoping to get results like:
3 157169, 167843
5 157170
30 157506, 163859
but instead I'm getting:
3 157169, 167843, 157170, 157506, 163859
5 157169, 167843, 157170, 157506, 163859
30 157169, 167843, 157170, 157506, 163859
HELP!!!
October 20, 2009 at 8:28 pm
A quick trick I stored in my cookbook.
I think I originally got it from one of Jeff's articles on concatenation.
declare @sample table (ID int, PID int, FILE_NO int)
insert into @sample
select 28, 3, 157169 union all
select 335, 3, 167843 union all
select 29, 5, 157170 union all
select 32, 30, 157506 union all
select 118, 30, 163859
select * from @sample
select PID,stuff((SELECT ',' + cast(file_no as varchar(20))
FROM @sample s2
WHERE s2.pid = s1.PID -- must match GROUP BY below
ORDER BY s2.file_no
FOR XML PATH('')
),1,1,'') as [FILE_NOs]
from @sample s1
GROUP BY s1.pid -- without GROUP BY multiple rows are returned
order by s1.pid
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 9:49 pm
Bob has shown the way with the best performance in 2k5. Lemme show you what you did wrong with yours....
CREATE FUNCTION dbo.fnConcatTest (@PID INT)RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return+', ','')+FILE_NO
FROM dbo.MYTABLE
WHERE PID = [font="Arial Black"][highlight]@[/highlight][/font]PID
ORDER BY ID
RETURN @Return
END
The other thing is, don't use DISTINCT when calling this... use GROUP BY instead. See the following article for why...
http://www.sqlservercentral.com/articles/Test+Data/61572/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply