Anusha,
try this
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..