spilting query problem

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

in my database one column storing values as 1,2,3,4,5 . now i want to send these values one by one in where condition

ex: select count(employeeid) from employee where level=1
then next time where comdition i want to send 2 then 3 ...

here i want count of employee who's level is 1 and whose level is 2 ....

Posted 1-Aug-12 20:52pm

sai sagar

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Answer 1 · 2012-08-01T21:19:00

Try the below approach

CREATE TABLE #Temp
(
	EmployeeID INT,
	[Level] VARCHAR(50)
)


INSERT INTO #Temp
SELECT 101, '1,2,3'
 UNION
SELECT 102, '2,3'
 UNION
SELECT 103, '1,3'


CREATE TABLE #Temp2
(
	[Level] INT
)

DECLARE @ColumnVal VARCHAR(100)
DECLARE @Level INT

SELECT @ColumnVal = isnull(@ColumnVal,'') + [Level]  + ','  FROM #Temp



WHILE CHARINDEX(',',@ColumnVal) != 0
BEGIN

SELECT @Level = SUBSTRING(@ColumnVal, 0,CHARINDEX(',',@ColumnVal))
SET @ColumnVal = SUBSTRING(@ColumnVal,CHARINDEX(',',@ColumnVal)+1,LEN(@ColumnVal))

	IF NOT EXISTS (SELECT 1 FROM #Temp2 WHERE Level = @Level)
	BEGIN

		INSERT INTO #Temp2
		SELECT @Level

	END

END


--SELECT * FROM #Temp2
--SELECT * FROM #Temp

SELECT COUNT(EmployeeID) AS EmployeeCount, Level 
FROM
(
	SELECT EmployeeID, T2.Level FROM #temp T1
	INNER JOIN #temp2 T2 ON 1 = 1
	WHERE T1.Level LIKE '%' + CAST(T2.Level AS VARCHAR) + '%'
) T
GROUP BY Level

DROP TABLE #Temp
DROP TABLE #Temp2

Santhosh Kumar Jayaraman · Answer 2 · 2012-08-01T20:54:00

Solution 1

try this.

If you have level coulmn datatype as int, then follow this.

SQL

Select  count(employeeid) from employee group by level

Else refer solution 3

Posted 1-Aug-12 20:54pm

Santhosh Kumar Jayaraman

Updated 2-Aug-12 18:57pm

v2

Comments

sai sagar 2-Aug-12 2:58am

can you read my question again...

i want pass value single one by one

Santhosh Kumar Jayaraman 2-Aug-12 3:00am

You want count of employees in all levels right? why do you want to pass 1 by 1.?

sai sagar 2-Aug-12 3:03am

but in level column values stores like 1,2,3,4,5

then how to split in where condition

Santhosh Kumar Jayaraman 2-Aug-12 3:04am

No when you are using group by it will automatically get the count of each level employees. Have you tried the above query?

sai sagar 2-Aug-12 3:08am

employee id level
----------- -------
101 1,2,3

102 2,3

103 1,3

now i want employee count who are having Level=1, level=2, level=3.

Santhosh Kumar Jayaraman 2-Aug-12 6:10am

Check the another answer posted by me

Santhosh Kumar Jayaraman · Answer 3 · 2012-08-02T00:10:00

try this..

--My table

SQL

Create table empotest
(Employeeid int,
Level varchar(100))

--insert

SQL

insert into empotest values (101,'1,2,3')
insert into empotest values (102,'1,2,3')
insert into empotest values (103,'4,5,6')

--My sql query

SQL

DECLARE @Count INT
Declare @totalcount int
Declare @recordcount int
declare @string varchar(100)
declare @EmpId int
SET @Count = 0
set @recordcount=1
create table #temptest
(Empid int,
Level int)
select @totalcount=count(*) from empotest where Level is not null
select @totalcount
While @recordcount<=@totalcount
Begin
SET @Count = 0;
	With CTE_test as
	(select employeeid,level,Row_Number() over (order by EmployeeId asc) as rn from  empotest)
	select @String=level,@EmpId=employeeid from cte_test where rn=@recordcount
	
	WHILE @Count <= LEN(@String)
	BEGIN
		IF SUBSTRING(@String,@Count,1) >= '0'
		AND SUBSTRING(@String,@Count,1) <= '9'
		BEGIN
			INSERT into #temptest values (@EmpId,SUBSTRING(@String,@Count,1))
			
		END
	SET @Count = @Count + 1
	END
	SET @recordcount = @recordcount + 1
	
END
select Level,count(Empid) from #temptest group by level

SQL

drop table #temptest

Table:
101 1,2,3
102 2,3
105 4,5,6

SruthiR · Answer 4 · 2012-08-02T00:56:00

Hi,

You can use the below function to split the string by comma

SQL

CREATE FUNCTION [dbo].[SplitString]
(
	@listString VARCHAR(8000),
	@Delimeter char(1)
)

RETURNS @ValueTable table 
(			
	Value VARCHAR(8000)
) 
AS
BEGIN

	DECLARE @NextString VARCHAR(8000)
	DECLARE @Pos INT
	DECLARE @NextPos INT
	DECLARE @String VARCHAR(8000)
	DECLARE @Delim VARCHAR(1)

	SET @NextString = ''
	SET @String = @listString
	SET @Delim = @Delimeter
	SET @String = @String + @Delim
	SET @Pos = charindex(@Delim,@String)

	WHILE (@pos <> 0)
		BEGIN
			SET @NextString = substring(@String,1,@Pos - 1)	
			INSERT INTO @ValueTable (Value) Values (@NextString)		
			SET @String = substring(@String,@pos+1,len(@String))
			SET @pos = charindex(@Delim,@String)			
		END 
		
	RETURN
END

GO

Then use the query like below.

SQL

select count(employeeid) 
from employee 
where level in 
(SELECT convert(VARCHAR(8000),Value) FROM  dbo.SplitString(columnvalue,','))

Hope it helps,
Regards,
SR

spilting query problem

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0