June 25, 2009 at 2:13 am
descentflower (6/25/2009)
csv values in rows without using a while loop
Hi,
try this
--create table #temp (name1 varchar(5))
declare @S varchar(500)
set @S='1,2,3,4,5'
select @S = 'select ''' + replace (@s,',',''' union select ''')+''''
insert into #temp (name1)
exec (@s)
select * from #temp
ARUN SAS
June 25, 2009 at 2:41 am
Thats amazing thank you so much !! :w00t:
Anything to take care using
June 25, 2009 at 3:10 am
arun.sas (6/25/2009)
descentflower (6/25/2009)
csv values in rows without using a while loopHi,
try this
--create table #temp (name1 varchar(5))
declare @S varchar(500)
set @S='1,2,3,4,5'
select @S = 'select ''' + replace (@s,',',''' union select ''')+''''
insert into #temp (name1)
exec (@s)
select * from #temp
ARUN SAS
And how to do opposite of this in an efficient manner?
like Column to a CSV.
June 25, 2009 at 3:19 am
declare @S varchar(500), @x xml;
select @S='1,2,3,4,5',
@x = '' + REPLACE(@s, ',' , '') + '';
select a.value('.', 'int') as s
from @x.nodes('/a') t(a)
edit: I see 'Pandian S' beat me to it with the XML hack/solution :rolleyes:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2009 at 3:34 am
Pandian S (6/25/2009)
[font="Courier New"]DECLARE @S VARCHAR(500)DECLARE @X XML
SELECT @S='1,2,3,4,5'
SELECT @X = '<Data>' + REPLACE(@S,',','</Data><Data>') + '</Data>'
SELECT N.value('.','varchar(max)') 'Result' from @X.nodes('Data') as T(N)[/font]
Hi,This one takes a string as an input.
If i have 5 rows in a table like
tbItem
item
-----
1
2
3
4
5
then how can i get 1,2,3,4,5 as my output.
June 25, 2009 at 4:26 am
[font="Verdana"]DECLARE @TABLE1 TABLE (ID INT)
INSERT @TABLE1 VALUES(1)
INSERT @TABLE1 VALUES(2)
INSERT @TABLE1 VALUES(3)
INSERT @TABLE1 VALUES(4)
INSERT @TABLE1 VALUES(5)
DECLARE @IDs VARCHAR(MAX)
SELECT @IDs = COALESCE(@IDs,'') + CAST(ID AS VARCHAR) + ',' FROM @TABLE1
SELECT LEFT(@IDs,LEN(@IDs)-1) 'Result'
(OR)
;WITH CTEs(Result) AS (SELECT CAST(ID AS VARCHAR) + ',' FROM @TABLE1 FOR XML PATH(''))
SELECT LEFT(Result,LEN(Result)-1) 'Result' FROM CTEs[/font]
June 25, 2009 at 5:09 am
Thanks a lot friends !! it helped me a lot !
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply