June 22, 2009 at 11:24 am
Hi All,
The front end sends data to my stored procedure as a comma separated values. Example 1,11,2,22,3,33 with a datatype nvarchar(4000)
I need to convert this into 2 column table as below
Col1 Col2
1 11
2 22
3 33
how can i achieve this in my stored procedure
Kindly suggest
Regards,
June 22, 2009 at 12:53 pm
Take a look at http://www.sommarskog.se/arrays-in-sql-2005.html#CSVand see if that help.
Eli
June 22, 2009 at 12:56 pm
This was answered 2 days ago: http://www.sqlservercentral.com/Forums/Topic738904-338-1.aspx
/* Anything is possible but is it worth it? */
June 22, 2009 at 1:13 pm
HTH
-- NOTE - putting your sample code like this helps people quickly help you!
declare @list varchar(1000)
set @list = '1,11,2,22,3,33'
-- make an XML variable to hold the list
declare @xml XML
-- convert the list to XML
set @xml = '' + replace(@list, ',', '') + ''
-- get rid of the temp table if it already exist.
-- not needed unless the code is going to be run repeately
if object_id('tempdb..#temp') is not null drop table #temp
CREATE TABLE #temp (
RowID int identity, --<< NOTE - this identity field is the key
RawData int)
-- populate the temp table from the xml list
insert into #temp
select x.data.value('value[1]','int') AS RawData
from @xml.nodes('/rows/row') AS x(data)
;with CTE AS
(
-- every pair of rows go together.
-- check the modulus of the RowID against 2 (a pair)
-- if it's 1, then it's the first part.
-- if it's 0, then it's the second part.
-- make the RowNum be the same for each part of the pair for grouping later
select RowNum = case when RowID % 2 = 1 then RowID else RowID - 1 end,
Col1 = case when RowID % 2 = 1 then RawData else 0 end,
Col2 = case when RowID % 2 = 0 then RawData else 0 end
from #temp
)
-- now sum the columns up, grouping by the RowNumber.
select Col1 = sum(Col1),
Col2 = sum(Col2)
from CTE
group by RowNum
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 22, 2009 at 1:16 pm
Gatekeeper (6/22/2009)
This was answered 2 days ago: http://www.sqlservercentral.com/Forums/Topic738904-338-1.aspx
No it wasn't. Similiar, but yet completely different.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 22, 2009 at 1:29 pm
WayneS (6/22/2009)
Gatekeeper (6/22/2009)
This was answered 2 days ago: http://www.sqlservercentral.com/Forums/Topic738904-338-1.aspxNo it wasn't. Similiar, but yet completely different.
I wouldn't say completely different. All I removed was col1 from the posted code I linked to and replaced ':' with ','.
DECLARE @t TABLE (col2 varchar(30));
INSERT INTO @t
SELECT '1,11,2,22,3,33';
; WITH t1 (col2) AS
(
SELECT
',' + col2 + ','
FROM @t
)
SELECT
t2.Item
FROM t1
CROSS APPLY
(
SELECT
SUBSTRING(t1.col2, N + 1, CHARINDEX(',', t1.col2, N + 1) - N - 1) Item
FROM Tally
WHERE N < LEN(t1.col2)
AND SUBSTRING(t1.col2, N, 1) = ','
) t2;
/* Anything is possible but is it worth it? */
June 22, 2009 at 1:35 pm
Well, I guess I was off a bit more than I thought I was.
DECLARE @t TABLE (col2 varchar(30));
INSERT INTO @t
SELECT '1,11,2,22,3,33';
; WITH t1 (col2) AS
(
SELECT
',' + col2 + ','
FROM @t
)
SELECT
ROW_NUMBER() OVER (ORDER BY t2.N) as Col1,
t2.Item as Col2
FROM t1
CROSS APPLY
(
SELECT N,
SUBSTRING(t1.col2, N + 1, CHARINDEX(',', t1.col2, N + 1) - N - 1) Item
FROM Tally
WHERE N < LEN(t1.col2)
AND SUBSTRING(t1.col2, N, 1) = ','
) t2;
/* Anything is possible but is it worth it? */
June 22, 2009 at 2:44 pm
Gatekeeper (6/22/2009)
Well, I guess I was off a bit more than I thought I was.
No problem. When I saw the way he wanting his output, I knew it was going to be different than what we posted for the other post.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply