March 11, 2012 at 1:20 am
Hello Friends,
I am facing a tough challenge in sorting a string. My data is like this...
List_of_values
47,48,49,92,93,94,95,96,108,107
37,27
I need to get output like this: Sorted output value
List_of_values
47,48,49,92,93,94,95,96,107,108
27,37
Any T_SQL code solving this problem is welcome... Thanks in advance.
March 11, 2012 at 1:31 am
I'm wondering why this needs to be a T-SQL task.
In a decent normalized database structure there shouldn't be a column with a comma separated list and esepcially not the need to re-order that list...
Anyway. Here's my approach using the DelimitedSplit8K function referenced in my signature together with the FOR XML syntax to return the comma separated list.
DECLARE @tbl TABLE
(
id INT IDENTITY(1,1),
list VARCHAR (200)
)
INSERT INTO @tbl
SELECT '47,48,49,92,93,94,95,96,108,107' UNION ALL
SELECT '37,27';
WITH cte AS
(
SELECT id, item
FROM @tbl
CROSS APPLY
(
SELECT *
FROM dbo.DelimitedSplit8K (list,',')
)x
)
SELECT
id,
STUFF(( SELECT ', ' + item
FROM cte t2
WHERE t2.id = t1.id
ORDER BY CAST(item AS INT)
FOR XML PATH('')
),1,2,'') AS new_list
FROM
cte t1
GROUP BY
id
Edit: typo fixed
March 11, 2012 at 1:34 am
my first thoughts are to split the string, then use something like FOR XML path to rebuild.
http://qa.sqlservercentral.com/articles/Tally+Table/72993/
where are these strings coming from....are you generating them?...in which case it would be better to solve the problem in the original code.
kind regards
Edit...seems Lutz is already on the case 😛
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 1:52 am
hi Lutz,
I got the result like this. The List is not sorted .....i have attached the screen shot as well.
id new_list
1 47,48,49,92,93,94,95,96,108,107, 47,48,49,92,93,94,95,96,108,107, 47,48,49,92,93,94,95,96,108,107, 47,48,49,92,93,94,95,96,108,107, 47,48,49,92,93,94,95,96,108,107, 47,48,49,92,93,94,95,96,108,107, 47,48,49,92,93,94,95,96,108,107, 47,48,49,92,93,94,95,96,108,107, 47,48,49,92,93,94,95,96,108,107, 47,48,49,92,93,94,95,96,108,107
2 37,27, 37,27
Thanks... Please suggest any other solution
March 11, 2012 at 1:53 am
Please assume that nothing can be done as these columns are loaded from some SSIS packages. Thanks.
March 11, 2012 at 3:06 am
shyamhr (3/11/2012)
hi Lutz,...
Thanks... Please suggest any other solution
I can't really offer any other solution since the one I posted works just fine for me (= returning the expected result).
One reason might be the ORDER BY in the FOR XML section I've used that you decided to eliminate...
Furthermore, it seems like you're using a different split function. Please post the code of the function you're using.
Edit: another reason might be the change of the column name. If you're using the identical column names as the split function returns, you'll need to qualif ythe column name in the subquery.
March 11, 2012 at 3:21 am
Sure Lutz,
I had to comment the order by as it throwed an error like this
(2 row(s) affected)
Msg 245, Level 16, State 1, Line 14
Conversion failed when converting the varchar value '47,48,49,92,93,94,95,96,108,107' to data type int.
When I looked into the 2 rows affected I see blanks only.. and if you notice, the error '47,48,49,92,93,94,95,96,108,107' gives the order the same but I need sorted values '47,48,49,92,93,94,95,96,107,108' ( 107 after 108).
I have tried my best to explain the problem, How can I sort this column. Thanks.
March 11, 2012 at 3:30 am
Please copy the code I posted and run it as it is. Without any modification. Neither renaming columns nor anything else.
What result do you get?
March 11, 2012 at 4:01 am
Hi Lutz,
This is what I get
(2 row(s) affected)
Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value '47,48,49,92,93,94,95,96,108,107' to data type int.
They are not sorted.. Take a look @ the error. (I ran the code as it is you have posted)
March 11, 2012 at 4:13 am
Please post the code you're running.
The code runs just fine on my system.
March 11, 2012 at 4:31 am
Please find the core I executed...
DECLARE @tbl TABLE
(
id INT IDENTITY(1,1),
list VARCHAR (200)
)
INSERT INTO @tbl
SELECT '47,48,49,92,93,94,95,96,108,107' UNION ALL
SELECT '37,27';
WITH cte AS
(
SELECT id, list
FROM @tbl
CROSS APPLY
(
SELECT *
FROM dbo.DelimitedSplit8K (list,',')
)x
)
SELECT
id,
STUFF(( SELECT ', ' + list
FROM cte t2
WHERE t2.id = t1.id
ORDER BY CAST(list AS INT)
FOR XML PATH('')
),1,2,'') AS new_list
FROM
cte t1
GROUP BY
id
March 11, 2012 at 4:37 am
I've asked you to run the code I posted. Why don't you just do that?
The code you've posted is different.
If you complain the code doesn't work, it might very well be because it's been modified...
March 11, 2012 at 4:50 am
Hi Lutz
DECLARE @tbl TABLE
(
id INT IDENTITY(1,1),
list VARCHAR (200)
)
INSERT INTO @tbl
SELECT '47,48,49,92,93,94,95,96,108,107' UNION ALL
SELECT '37,27';
WITH cte AS
(
SELECT id, item
FROM @tbl
CROSS APPLY
(
SELECT *
FROM dbo.DelimitedSplit8K (list,',')
)x
)
SELECT
id,
STUFF(( SELECT ', ' + item
FROM cte t2
WHERE t2.id = t1.id
ORDER BY CAST(item AS INT)
FOR XML PATH('')
),1,2,'') AS new_list
FROM
cte t1
GROUP BY
id
When I executed this, I got error:
Msg 207, Level 16, State 1, Line 11
Invalid column name 'item'.
March 11, 2012 at 5:06 am
It seems like you also decided not to use the split function based on the link I referenced in my signature.
How do you expect us to help you if you continuously ignore any given advice????
I'm out.
March 11, 2012 at 5:14 am
Hi Lutz,
I had used the Split function that you referred in the signature...
Here is that code, I am using...
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
Is this the right function or I have copied the wrong one. Please correct me, If I am wrong? Thanks...
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply