counting in SQL server 2000??
-
currently i m using sql server 2000. my column consist of mixture of country values. i need to take the count of each countries. example.... colA : india,japan,india,china,japan,USA,india,singapore,china,india the output should be..... india japan china USA singapore 4 2 2 1 1 how to achieve it? - KARAN
-
currently i m using sql server 2000. my column consist of mixture of country values. i need to take the count of each countries. example.... colA : india,japan,india,china,japan,USA,india,singapore,china,india the output should be..... india japan china USA singapore 4 2 2 1 1 how to achieve it? - KARAN
-
This will not actuall give the required results. The original question wanted india japan china USA singapore 4 2 2 1 1 This will give india 4 japan 2 china 2 USA 1 singapore 1 You actually need to pivot the data. This code should show you how to do it
/* create test table and populate */ create table #b1(cola varchar(20)) insert into #b1 select 'India' union all select 'USA' union all select 'India' union all select 'UK' /* now the actual code */ DECLARE @SQL nvarchar(4000) SET @SQL='' SELECT @SQL= @SQL + 'SUM(CASE WHEN cola=''' + a.cola + ''' THEN 1 ELSE 0 END) AS [' + a.cola + '],' FROM (select distinct cola from #b1) as a select @SQL = left(@SQL,len(@SQL)-1) SET @SQL='SELECT ' + @SQL + ' FROM #b1' EXEC(@SQL)Hope this helpsBob Ashfield Consultants Ltd