|
|
You're in the
sql server programming
group:Comma-separated field
sql server programming:
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
Check the article at
http://www.windowsitpro.com/SQLServer/Article/ArticleID/25678/25678.html. -- Dejan Sarka, SQL Server MVP Mentor www.SolidQualityLearning.com [quoted text, click to view] "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... [quoted text, click to view] > 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. [quoted text, click to view] "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. [quoted text, click to view] "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... > > 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 > > > >
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 [quoted text, click to view] "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... > > 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 > > > >
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... [quoted text, click to view] > 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 [quoted text, click to view] "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... > > 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. > > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |