Find matches

Question

I have a table with two columns (db: sql server 2008):

id name ----- ------ 1 Bob 2 Mike 3 Mary 4 Mike 5 Barry 6 Benson 7 Burrows 

I want to get the number of names starting with B and starting with M (on one line)?

how

 Count of B Count of M ----------- ------------ 4 3 

The only thing that suits me is union. Any ideas to make this cleaner in a single request (without combining)?

Answer 1

Try using CASE ,

 SELECT SUM(CASE WHEN SUBSTRING(name,1,1) = 'B' Then 1 ELSE 0 END), SUM(CASE WHEN SUBSTRING(name,1,1) = 'M' Then 1 ELSE 0 END) FROM TAbleName 

SQLFiddle Demo

Answer 2

You can use PIVOT for this. If you have a known number of columns, you can adjust the values ​​using STATIC PIVOT:

 select * from ( select substring(name, 1, 1) name, -- use the same field twice, substring(name, 1, 1) initial -- once will be for the count the other for columns from yourtable ) x pivot ( count(name) for initial in ([B], [M]) ) p 

See SQL Fiddle With Demo

If you have an unknown number of columns to convert, you can use dynamic sql and create a dynamic PIVOT:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(substring(name, 1, 1)) from yourtable FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT ' + @cols + ' from ( select substring(name, 1, 1) name, substring(name, 1, 1) initial from yourtable ) x pivot ( count(name) for initial in (' + @cols + ') ) p ' execute(@query) 

See SQL script for a demo

If you want to filter the dataset only for those that start with B or M , you can use the WHERE to filter.

 where substring(name, 1, 1) in ('B', 'M') 

Answer 3

Here is another way

 Declare @T Table ([id] varchar(5), [name] varchar(7)); INSERT INTO @T([id], [name]) VALUES ('1', 'Bob'), ('2', 'Mike'), ('3', 'Mary'), ('4', 'Mike'), ('5', 'Barry'), ('6', 'Benson'), ('7', 'Burrows') ;WITH CTE AS (SELECT Initials = SUBSTRING(name,1,1) ,Cnt = COUNT([name]) FROM @t GROUP BY SUBSTRING(name,1,1)) SELECT [Count of B] = (SELECT Cnt FROM CTE WHERE Initials = 'B') ,[Count of M] = (SELECT Cnt FROM CTE WHERE Initials = 'M') 

Result

 Count of B Count of M 4 3