Click here to Skip to main content
15,066,062 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

Here is a sample example for my query.

I have a column in my Table which contains valies like this
[Column]
AS,BS
AS,CS
CS,ES
DE,AS
IR,CS,DE.

I need to find the Count of each value and output shuld come like this
AS 5
BS 3
CS 4 .

can anyone help me to write a Procedure


Thanks,
Anusha
Posted
Comments
_Amy 24-Sep-12 1:17am
   
What have you tried so far?

Anusha,

try this
SQL
create table alphabet
(testword varchar(10)
)

insert into alphabet values('AS,BS')
insert into alphabet values('AS,CS')
insert into alphabet values('CS,ES')
insert into alphabet values('DE,AS')


alter Procedure [dbo].[asp_CommaSeparatedStringToTable]
AS
BEGIN
  declare @rownum as int
  DECLARE @String VARCHAR(10)
  DECLARE @StringInput VARCHAR(100)
  set @rownum =1
CREATE TABLE #temp1
(
   COL1 varchar(50),  
   rownum int, 
)
CREATE TABLE #temp2
(
   COL1 varchar(50),   
)
Insert into #temp1
  select testword,ROW_NUMBER() over (order by testword) as rowno from alphabet
  
  while (@rownum <=(select MAX(rownum) from #temp1))
  BEGIN
 
select @StringInput=COL1 from #temp1 where rownum=@rownum

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO  #temp2
        VALUES ( @String )
    END
    set @rownum=@rownum+1
    END
    
    select COUNT(1),col1 from #temp2 group by col1
     drop table #temp2
     drop table #temp1
END
GO

exec [asp_CommaSeparatedStringToTable]


I have hardcoded table and column inside stored procedure..
   
v2
If all your data in the column was like :
ColumnName
------
aa
aa
bb
bb
cc

Then you would write:
SQL
select ColumnName,Count(ColumnName) from tablename group by columnname

That is why it is a bad idea to put multiple data items in a single row (i.e. "aa,bb")
   
Hi,
Thanks for the code.. :) This is what i want.. I ll try it out with my data and i ll reach you back for queries..

Thanks & Regards
Anusha.
   
Comments
Santhosh Kumar Jayaraman 24-Sep-12 3:03am
   
Yeah..By the way, please dont use answers to post your reply..Use comments. mark it as solved, if it works for you
Anusha Sridhar 24-Sep-12 5:21am
   
okay.! Will follow that hereafter.. :) thankyou.. !
Santhosh Kumar Jayaraman 24-Sep-12 5:22am
   
ya welcome
Hi,
Thanks for the reply..
Actually I have lots of services in a table which ll be like AVM, ABC, BBC, CVM, etc and so on.. many are ter..
so I want the count of each services. like eg,
COL1    COL2    COL3
---------------------
AVM     ABC     BBC
ABC     AVM     AVM
BBC     CVM     AVM
CVM     ABC     CVM
----------------------
I want the output to be like this,

AVM     4
ABC     3
BBC     2
CVM     3

I tried a method, but its exceeding the limit..

Pls help me to write a clear procedure for this..

Thanks,
Anusha
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900