June 3, 2010 at 3:25 am
DECLARE @mytable
TABLE (
id INTEGER NOT NULL,
potype VARCHAR(10) NOT NULL
);
INSERT @mytable VALUES (1,'A>A');
INSERT @mytable VALUES (1,'B&B');
INSERT @mytable VALUES (1,'C<C');
SELECT P1.id,
csv = STUFF
(
(
SELECT ',' + P2.potype
FROM @mytable P2
WHERE P2.id = P1.id
ORDER BY P2.potype ASC
FOR XML PATH(''), TYPE
).value('./text()[1]', 'VARCHAR(MAX)')
, 1, 1, SPACE(0)
)
FROM @mytable P1
GROUP BY P1.id;
:laugh:
June 4, 2010 at 7:05 am
I found a better solution in SqlServer 2008
declare @mytable table
(
id int,
potype varchar(10)
)
declare @Text varchar(max)=''
insert into @mytable values (1,'AA')
insert into @mytable values (1,'BB')
insert into @mytable values (1,'CC')
select @Text += potype +','
from @mytable
select left(@Text,len(@Text)-1)
June 4, 2010 at 2:45 pm
Nagesh S-432384 (6/3/2010)
Nagesh S-432384 (6/3/2010)
Jeff Moden (6/2/2010)
Very cool feedback, Nagesh. Thank you for taking the time.one more late response Jeff 🙂 I am in IST (in India) so I will be always late :-).
Nag
I mean to say my response will be late 😉
I am a big follower of you Jeff, thanks for you're great contribution here in SSC, I have learned a lot here from great people like you. Thanks once again for all SSC members.
Nag
Thanks for the feedback, Nag. And, understood on the time difference. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2010 at 2:51 pm
cfrestrepo (6/4/2010)
I found a better solution in SqlServer 2008
Ummm... it depends on what you mean by "better". First, that solution will work on all versions of SQL Server (observing that you need to use 8000 instead of MAX prior to SS 2k5). However, when compared to the XML method used previous on this thread, the concatenation method you used is a form of RBAR and is also a fair bit slower than the XML concatenation method.
So, let me ask... what do you mean by "better" in this case?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2010 at 9:25 pm
Nagesh S-432384 (5/31/2010)
Hope this might help you 🙂
declare @mytable table
(
potype varchar(10)
)
insert into @mytable values ('AA')
insert into @mytable values ('BB')
insert into @mytable values ('CC')
declare @potypestring varchar(50)
select @potypestring = isnull(@potypestring + ',' , '' ) + isnull( potype , '' )
from @mytable
select @potypestring
Nag
.
Dear Nag,
Until now, it also confused me how the sql server can automatically catenate the string together. As far as I know, SQL is ad-hoc query and will execute all at once.
OR. it execute one by one,catenate and assign a variable. SO HOW?
Thanks!:blink:
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply