January 12, 2007 at 1:47 pm
I have a query which returns the following output
DECLARE @tbl TABLE (ColA int, ColIDs varchar(100), ColDescription varchar(200) )
INSERT INTO @tbl
SELECT 130, '123;456;789;111', '3D0041-1 Receptacle'
UNION
SELECT 130, '123;456;111', '3D0041-1 Receptacle'
UNION
SELECT 130, '456;2;564;123;111', '3D0041-1 Receptacle'
UNION
SELECT 130, '444;555;666;33;22;111', '3D0041-1 Receptacle'
UNION
SELECT 130, '3322;2233;111', '3D0041-1 Receptacle'
UNION
SELECT 130, '12;32;45;67;77', '9D0062-1-08-10 Bracket'
UNION
SELECT 130, '12;3254;77', '9D0062-1-08-10 Bracket'
UNION
SELECT 130, '12;32;77', '9D0062-1-08-10 Bracket'
select * FROM @tbl
I need a query to return 2 rows like
130 3322;2233;111 3D0041-1 Receptacle
(OR)
130 123;456;111 3D0041-1 Receptacle
AND
130 12;3254;77 9D0062-1-08-10 Bracket
(OR)
130 12;32;77 9D0062-1-08-10 Bracket
The criteria for selecting the 2nd column would be the one that has the least number of ';'
select colA, MAX(ColIDs) ColIDs, ColDescription
FROM @tbl
GROUP BY colA, ColDescription
Basically I want to change the MAX function in the above query to get the ColIDs column with
the least number of ';'
Thanks in advance
January 12, 2007 at 2:38 pm
This would be handled real easy with a split function. Search SSC for dbo.split. Another option would be to create your own UDF to read through your ColIDs value one character at a time to split out the numbers, but if you're going to spend the time to do this, you'd might as well use the split function.
January 12, 2007 at 2:49 pm
Thanks John. I have a Split functions that return the number of times ';' repeats. How will the query work with the split function ? The query has to return the COlIDs with the least number of ';'
January 12, 2007 at 2:59 pm
So your function returns a scalar value and not a table? If so, can you post your split function.
January 12, 2007 at 3:10 pm
I have to give credit to Igor from where I got this function.
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
CREATE function OCCURS(@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000))
returns smallint
as
begin
return
case
when datalength(@cSearchExpression) > 0
then ( datalength(@cExpressionSearched)
- datalength(replace(@cExpressionSearched COLLATE Latin1_General_BIN,
@cSearchExpression COLLATE Latin1_General_BIN, '')))
/ datalength(@cSearchExpression)
else 0
end
end
January 12, 2007 at 3:18 pm
January 12, 2007 at 3:20 pm
It does not matter - any row with the least occurance would work.
January 12, 2007 at 3:22 pm
You may have to play around with it, but this should work (or at least get you started):
SELECT t1.ColA,
t1.ColIDs,
t1.ColDescription
FROM @tbl t1
INNER JOIN (
SELECT ColA,
MIN(dbo.Occurs(';',ColIDs)) as 'MINoccurs',
ColDescription
FROM @tbl t1
GROUP BY ColA, ColDescription
) t2
ON t1.ColA = t2.ColA AND t1.ColDescription = t2.ColDescription AND dbo.Occurs(';',t1.ColIDs) = t2.MINoccurs
January 12, 2007 at 3:29 pm
This would return 2 rows for each of ColA and ColDescription.
130 3D0041-1 Receptacle 123;456;111
130 3D0041-1 Receptacle 3322;2233;111
130 9D0062-1-08-10 Bracket 12;32;77
130 9D0062-1-08-10 Bracket 12;3254;77
I need either of rows in red and either of the rows in blue.
January 12, 2007 at 3:34 pm
If it doesn't matter which one, just throw a MIN/MAX group by on the whole thing:
SELECT t1.ColA,
MIN(t1.ColIDs) as ColIDs,
t1.ColDescription
FROM @tbl t1
INNER JOIN (
SELECT ColA,
MIN(dbo.Occurs(';',ColIDs)) as 'MINoccurs',
ColDescription
FROM @tbl t1
GROUP BY ColA, ColDescription
) t2
ON t1.ColA = t2.ColA AND t1.ColDescription = t2.ColDescription AND dbo.Occurs(';',t1.ColIDs) = t2.MINoccurs
GROUP BY t1.ColA, t1.ColDescription
January 12, 2007 at 3:35 pm
Thanks John. That worked.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply