Click here to Skip to main content
15,861,125 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
Here is my sql table data,
----------------------
HTML
ID	Team	Emp	Status
1	PP	H001	Active
2	AR	H002	In-Active
3	BL	H003	Active
4	EN	H004	Active
5	PP	H005	Active
6	AR	H006	Active
7	BL	H007	In-Active
8	EN	H008	In-Active
9	PP	H009	In-Active
10	AR	H010	In-Active
11	BL	H011	Active
12	EN	H012	In-Active
13	PP	H013	Active
14	AR	H014	Active
15	BL	H015	In-Active
16	EN	H016	In-Active

--------------------------

And now i need output is like,
---------------------------
HTML
Status	   PP	AR	BL	EN
Active	   3	2	2	1
In-Active	1	2	2	3

---------------------------


Please help me out of this.

Thanks in advance.

What I have tried:

Itried this,
---------------------
SQL
SELECT  Status, count(*) FROM [Tbl_Code] group by Status

---------
Posted
Updated 22-Feb-17 2:30am
Comments
Dave Kreskowiak 22-Feb-17 9:11am    
This sniffs of homework.

1 solution

Use pivot like this:
SELECT status, [PP],[AR],[BL], [EN] FROM
(SELECT status, team FROM tablename) as src
PIVOT
(
   COUNT(team) FOR team IN ([PP],[AR],[BL], [EN])
) AS output
Learn Using PIVOT and UNPIVOT[^]
 
Share this answer
 
v2
Comments
Raj 0203 22-Feb-17 8:50am    
Awesome bro.. Thank You Sooo Much..!
Raj 0203 22-Feb-17 9:04am    
Buddy, can we make a little change in the output like,
-----------------
Status Total PP AR BL EN
Active 8 3 2 2 1
In-Active 8 1 2 2 3
----------------------------------
Peter Leow 22-Feb-17 10:17am    
Absolutely can be done, post it as a new question, and I will answer it.
Raj 0203 22-Feb-17 10:22am    
Please check i have posted a new question.
Peter Leow 22-Feb-17 10:31am    
answered.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900