Multiple COUNTS in SELECT LIST sql

Question

I have a query with multiple joins Like this:

SELECT branch,[language].Name,COUNT(r.ID) 'COUNTofStudent'
FROM Registration r
    INNER JOIN Branch
    ON r.fk_Branch=branch.ID
    INNER JOIN [Language]
    ON r.fk_Language=[Language].ID
GROUP BY branch,[language].Name
ORDER BY branch,[language].Name

that returns me this:

Branch  language  COUNT
a    English     234
a    German      321
a    Spanish     10
b    English     258
b    Spanish     159

. . .

Now I want have this One:

Branch English  German Spanish
a    234      321    10
b    258      0      159

. . .

How can I do this with a query?

This will definitely help you : http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — Deepak Shingan, Jan 08 '15 at 10:47

score 0 · Answer 1 · answered Jan 08 '15 at 10:47

declare @t table (Branch varchar(100),LanguageName varchar(100),COUNTID INT)

insert into @t(Branch,LanguageName,COUNTID)values ('A','ENG',234)
insert into @t(Branch,LanguageName,COUNTID)values ('A','German',321)
insert into @t(Branch,LanguageName,COUNTID)values ('A','Spanish',10)
insert into @t(Branch,LanguageName,COUNTID)values ('B','ENG',258)
insert into @t(Branch,LanguageName,COUNTID)values ('B','Spanish',159)



;With pivot_data as
(
Select Branch,LanguageName,COUNTID
from @t
)
select Branch,ISNULL([ENG],0)As English, ISNULL([Spanish],0)As Spanish,ISNULL([German],0)As German
from   pivot_data
pivot  ( max(COUNTID) for LanguageName in ([ENG], [Spanish],[German]) ) as p;

thanks for ur help!How can i do what I want with a single query? — maryam rad, Jan 08 '15 at 10:50
just remove CTE and place the table name in select statement — mohan111, Jan 08 '15 at 10:58

score 0 · Accepted Answer · answered Jan 08 '15 at 10:58

--Conditional aggregation

;
WITH    CTE
          AS ( SELECT   branch ,
                        [language].Name ,
                        COUNT(r.ID) as [COUNTofStudent]
               FROM     Registration r
                        INNER JOIN Branch ON r.fk_Branch = branch.ID
                        INNER JOIN [Language] ON r.fk_Language = [Language].ID
               GROUP BY branch ,
                        [language].Name
               ORDER BY branch ,
                        [language].Name
             )
    SELECT  Branch ,
            SUM(CASE WHEN Name = 'English' THEN [COUNTofStudent]
                     ELSE 0
                END) AS English ,
            SUM(CASE WHEN Name = 'German' THEN [COUNTofStudent]
                     ELSE 0
                END) AS German ,
            SUM(CASE WHEN Name = 'Spanish' THEN [COUNTofStudent]
                     ELSE 0
                END) AS Spanish
    FROM    CTE
    GROUP BY CTE.Branch

Multiple COUNTS in SELECT LIST sql

2 Answers2