CREATE FUNCTION [dbo].[fn_ParseString]
(
@CSVString VARCHAR(8000) ,
@Delimiter VARCHAR(10)
)
RETURNS @tbl TABLE (i INT IDENTITY, s VARCHAR(1000))
AS
/*
select * from dbo.fn_ParseString ('a-bb-c-ddd-e','-')
*/
BEGIN
DECLARE @i INT ,
@j INT
SELECT @i = 1
IF @Delimiter = ''
BEGIN
WHILE @i <= LEN(@CSVString)
BEGIN
SELECT @j = CHARINDEX(' ', @CSVString, @i)
IF @j = 0
BEGIN
SELECT @j = LEN(@CSVString) + 1
END
IF SUBSTRING(@CSVString, @i, @j - @i) <> ''
INSERT @tbl SELECT SUBSTRING(@CSVString, @i, @j - @i)
SELECT @i = @j + 1
END
END
ELSE
WHILE @i <= LEN(@CSVString)
BEGIN
SELECT @j = CHARINDEX(@Delimiter, @CSVString, @i)
IF @j = 0
BEGIN
SELECT @j = LEN(@CSVString) + 1
END
IF SUBSTRING(@CSVString, @i, @j - @i) <> ''
INSERT @tbl SELECT SUBSTRING(@CSVString, @i, @j - @i)
SELECT @i = @j + LEN(@Delimiter)
END
RETURN
END
go
Select
item,
orderno,
case when sum(value) between 0 and 1000 Then '?0 to ?10'
when sum(value) between 1001 and 5000 Then '?11 to ?50'
when sum(value) between 5001 and 10000 Then '?51 to ?100'
when sum(value) between 10001 and 20000 Then '?101 to ?200'
else 'Over ?20000' end as "Type",
sum(value)
From TableA
where
item in ( select * from dbo.fn_ParseString (@Company, ',' ) )
group by
company,
orderno
Thanks
Main Topics
Browse All Topics
by: angelIIIPosted on 2009-11-02 at 15:23:53ID: 25724837
this article will give you what you need:
http://www.experts-exchang