Comma-separated field

Hi,
table (contact) has a field categories with values like
row 1:
bil;jack;john;don
row 2:
bil;zub;sam;tom;jack
Ideally I would like to create a new table (split_table) with the
following values in each row without any duplicate. The
item NoOfOccurence
-- --
bil 2
jack 2
john 1
don 1
zub 1
sam 1
tom 1
Does anyone has a simple code for a newbie using MS SQL 2000. I don't
mind using a stored procedure or cursor. I've seen solution but they
seem too complicated for me to even modify.
I appreciate any help and suggestion.
Thank you and kind regards
BilsCheck the article at
http://www.windowsitpro.com/SQLServ...678/25678.html.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Bils" <bjeewa@.advsol.com> wrote in message
news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
> Hi,
> table (contact) has a field categories with values like
> row 1:
> bil;jack;john;don
> row 2:
> bil;zub;sam;tom;jack
> Ideally I would like to create a new table (split_table) with the
> following values in each row without any duplicate. The
> item NoOfOccurence
> -- --
> bil 2
> jack 2
> john 1
> don 1
> zub 1
> sam 1
> tom 1
> Does anyone has a simple code for a newbie using MS SQL 2000. I don't
> mind using a stored procedure or cursor. I've seen solution but they
> seem too complicated for me to even modify.
> I appreciate any help and suggestion.
> Thank you and kind regards
> Bils
>|||My solution is:
select * into #t from source
select * into #t1 from source where 0 = 1
while exists(select * from #t where len(row) > 0)
begin
insert into #t1 (row)
select substring(row,1,charindex(';',row + ';') - 1)
from #t
update #t set row = substring(row,charindex(';',row + ';') + 1,len(row))
end
select row as Item,count(*) as NoOfOccurence
from #t1
where len(row) > 0
group by row
drop table #t1
drop table #t
No proc, no cursor - is that you want?
endorsed by signature
*** Serg Yury ***
"Bils" <bjeewa@.advsol.com> '?/'' ? '' '?:
news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
> Hi,
> table (contact) has a field categories with values like
> row 1:
> bil;jack;john;don
> row 2:
> bil;zub;sam;tom;jack
> Ideally I would like to create a new table (split_table) with the
> following values in each row without any duplicate. The
> item NoOfOccurence
> -- --
> bil 2
> jack 2
> john 1
> don 1
> zub 1
> sam 1
> tom 1
> Does anyone has a simple code for a newbie using MS SQL 2000. I don't
> mind using a stored procedure or cursor. I've seen solution but they
> seem too complicated for me to even modify.
> I appreciate any help and suggestion.
> Thank you and kind regards
> Bils
>|||simply brilliant. Thank you very much guys for your assistance
Kind regards
Bils|||IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2
SELECT
Row
, Data
INTO
#tmp
FROM
(
SELECT TOP 0 NULL AS Row , NULL AS Data
UNION ALL SELECT 1 , 'bil;jack;john;don'
UNION ALL SELECT 2 , 'bil;zub;sam;tom;jack'
) Data
SELECT
Row
, NULLIF( SUBSTRING( #tmp.Data , n , CHARINDEX( ';' , #tmp.Data + ';' ,
n ) - n ) , '' ) AS Item
INTO
#tmp2
FROM
#tmp
INNER JOIN
tblNumbers
ON
tblNumbers.n BETWEEN 1 AND DATALENGTH( #tmp.Data )
AND
SUBSTRING( ';' + #tmp.Data , n , 1 ) = ';'
SELECT
Item
, COUNT(*) AS NoOfOccurence
FROM
#tmp2
GROUP BY
Item
ORDER BY
NoOfOccurence DESC
, Item
This should be quicker than using a cursor/loop.
"Bils" <bjeewa@.advsol.com> wrote in message
news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
> Hi,
> table (contact) has a field categories with values like
> row 1:
> bil;jack;john;don
> row 2:
> bil;zub;sam;tom;jack
> Ideally I would like to create a new table (split_table) with the
> following values in each row without any duplicate. The
> item NoOfOccurence
> -- --
> bil 2
> jack 2
> john 1
> don 1
> zub 1
> sam 1
> tom 1
> Does anyone has a simple code for a newbie using MS SQL 2000. I don't
> mind using a stored procedure or cursor. I've seen solution but they
> seem too complicated for me to even modify.
> I appreciate any help and suggestion.
> Thank you and kind regards
> Bils
>|||Sure, no cursor - but if the data contains 50,000 records with 100 items per
record, you'll have 5,000,000 record updates to the tempdb and it will take
forever.
This is not a scalable solution.
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435c815f$1_1@.isa.dvgd.mps...
> My solution is:
> select * into #t from source
> select * into #t1 from source where 0 = 1
> while exists(select * from #t where len(row) > 0)
> begin
> insert into #t1 (row)
> select substring(row,1,charindex(';',row + ';') - 1)
> from #t
> update #t set row = substring(row,charindex(';',row + ';') +
1,len(row))
> end
> select row as Item,count(*) as NoOfOccurence
> from #t1
> where len(row) > 0
> group by row
> drop table #t1
> drop table #t
> No proc, no cursor - is that you want?
>
> --
> endorsed by signature
> *** Serg Yury ***
> "Bils" <bjeewa@.advsol.com> '?/'' ? '' '?:
> news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
>|||You can also do without the temporary table by using a subquery.
... replace #tmp with your datasource
SELECT
Item
, COUNT(*) AS NoOfOccurence
FROM
(
SELECT
Row
, NULLIF( SUBSTRING( #tmp.Data , n , CHARINDEX( ';' , #tmp.Data + ';' ,
n ) - n ) , '' ) AS Item
FROM
#tmp
INNER JOIN
tblNumbers
ON
tblNumbers.n BETWEEN 1 AND DATALENGTH( #tmp.Data )
AND
SUBSTRING( ';' + #tmp.Data , n , 1 ) = ';'
) Data
GROUP BY
Item
ORDER BY
NoOfOccurence DESC
, Item
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:435ca9c0$0$142$7b0f0fd3@.mistral.news.newnet.co.uk...
> IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
> IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2
> SELECT
> Row
> , Data
> INTO
> #tmp
> FROM
> (
> SELECT TOP 0 NULL AS Row , NULL AS Data
> UNION ALL SELECT 1 , 'bil;jack;john;don'
> UNION ALL SELECT 2 , 'bil;zub;sam;tom;jack'
> ) Data
>
> SELECT
> Row
> , NULLIF( SUBSTRING( #tmp.Data , n , CHARINDEX( ';' , #tmp.Data + ';' ,
> n ) - n ) , '' ) AS Item
> INTO
> #tmp2
> FROM
> #tmp
> INNER JOIN
> tblNumbers
> ON
> tblNumbers.n BETWEEN 1 AND DATALENGTH( #tmp.Data )
> AND
> SUBSTRING( ';' + #tmp.Data , n , 1 ) = ';'
> SELECT
> Item
> , COUNT(*) AS NoOfOccurence
> FROM
> #tmp2
> GROUP BY
> Item
> ORDER BY
> NoOfOccurence DESC
> , Item
>
> This should be quicker than using a cursor/loop.
>
> "Bils" <bjeewa@.advsol.com> wrote in message
> news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
>|||First of all: what is tblNumbers?
I suppose:
SELECT n INTO tblNumbers
FROM
(
SELECT TOP 0 NULL AS n
UNION ALL SELECT 1
UNION ALL SELECT 2
-- ...
-- ... to DATALENGTH(#tmp.Data) '
) Data
I agree - your solution more elegant, if not to take into account creation
of table tblNumbers...
endorsed by signature
*** Serg Yury ***
"Rebecca York" <rebecca.york {at} 2ndbyte.com> /
: news:435ca9c0$0$142$7b0f0fd3@.mistral.news.newnet.co.uk...
> IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
> IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2
> SELECT
> Row
> , Data
> INTO
> #tmp
> FROM
> (
> SELECT TOP 0 NULL AS Row , NULL AS Data
> UNION ALL SELECT 1 , 'bil;jack;john;don'
> UNION ALL SELECT 2 , 'bil;zub;sam;tom;jack'
> ) Data
>
> SELECT
> Row
> , NULLIF( SUBSTRING( #tmp.Data , n , CHARINDEX( ';' , #tmp.Data + ';' ,
> n ) - n ) , '' ) AS Item
> INTO
> #tmp2
> FROM
> #tmp
> INNER JOIN
> tblNumbers
> ON
> tblNumbers.n BETWEEN 1 AND DATALENGTH( #tmp.Data )
> AND
> SUBSTRING( ';' + #tmp.Data , n , 1 ) = ';'
> SELECT
> Item
> , COUNT(*) AS NoOfOccurence
> FROM
> #tmp2
> GROUP BY
> Item
> ORDER BY
> NoOfOccurence DESC
> , Item
>
> This should be quicker than using a cursor/loop.
>|||Everyone needs tblNumbers :)
It contains numbers 1 to 100,000.
To populate it:-
USE UtilsDatabase
CREATE TABLE
dbo.tblNumbers
(
n INT
, CONSTRAINT PK_dbo_tblNumbers PRIMARY KEY CLUSTERED ( n ASC )
)
GRANT SELECT ON dbo.tblNumbers TO PUBLIC
IF NOT EXISTS( SELECT n FROM dbo.tblNumbers )
INSERT INTO dbo.tblNumbers ( N )
SELECT TOP 0 NULL AS n
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
DECLARE @.Loop TINYINT , @.HowManyZeros TINYINT , @.MAX_N INT
SELECT @.Loop = 1 , @.HowManyZeros = 5
WHILE @.Loop < @.HowManyZeros
BEGIN
SELECT @.MAX_N = MAX( n ) FROM dbo.tblNumbers
INSERT INTO
dbo.tblNumbers ( n )
SELECT
n + n2 * @.MAX_N AS N
FROM
dbo.tblNumbers
CROSS JOIN
( SELECT n AS n2 FROM dbo.tblNumbers WHERE n BETWEEN 1 AND 9 ) BaseTen
SELECT @.Loop = @.Loop + 1
END
DENY INSERT , UPDATE , DELETE ON dbo.tblNumbers TO PUBLIC
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435da744$1_1@.isa.dvgd.mps...
> First of all: what is tblNumbers?
> I suppose:
> SELECT n INTO tblNumbers
> FROM
> (
> SELECT TOP 0 NULL AS n
> UNION ALL SELECT 1
> UNION ALL SELECT 2
> -- ...
> -- ... to DATALENGTH(#tmp.Data) '
> ) Data
> I agree - your solution more elegant, if not to take into account creation
> of table tblNumbers...
> --
> endorsed by signature
> *** Serg Yury ***
> "Rebecca York" <rebecca.york {at} 2ndbyte.com> /
> : news:435ca9c0$0$142$7b0f0fd3@.mistral.news.newnet.co.uk...
>
>