Try this:
SELECT [status], wc AS [Place], COUNT(AppNo) AS CountOfApps
FROM Table1
GROUP BY [status], wc
ORDER BY [status], wc
But if you want to count distinct apps, use:
COUNT(DISTINCT AppNo) AS CountOfDistinctApps
Another way is to use
PIVOT[
^]
CREATE TABLE #tmp (AppNo INT, wc VARCHAR(30), [status] VARCHAR(30))
INSERT INTO #tmp (AppNo, wc, [status])
SELECT 100, 'Delhi', 'to HR'
UNION ALL SELECT 101, 'Delhi', 'to wc'
UNION ALL SELECT 104, 'dehradun', 'from wc'
UNION ALL SELECT 107, 'kolkata', 'from HR'
DECLARE @cols NVARCHAR(200)
SET @cols = STUFF((SELECT DISTINCT '],[' + [status]
FROM #tmp
ORDER BY '],[' + [status]
FOR XML PATH('')),1,2,'') + ']'
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)
SET @dt = N'SELECT AppNo, wc, [status]
FROM #tmp'
SET @pt = N'SELECT AppNo, wc,' + @cols +
'FROM (' + @dt + ') AS DT
PIVOT (COUNT([status]) FOR [status] IN (' + @cols + ')) AS PT'
EXEC(@pt)
SET @cols = STUFF((SELECT DISTINCT '],[' + [wc]
FROM #tmp
ORDER BY '],[' + [wc]
FOR XML PATH('')),1,2,'') + ']'
SET @dt = N'SELECT AppNo, wc, [status]
FROM #tmp'
SET @pt = N'SELECT AppNo, [status],' + @cols +
'FROM (' + @dt + ') AS DT
PIVOT (COUNT([wc]) FOR [wc] IN (' + @cols + ')) AS PT'
EXEC(@pt)
DROP TABLE #tmp
first pivot result:
Appno wc from... to...
104 dehradun 0 1 0 0
100 Delhi 0 0 1 0
101 Delhi 0 0 0 1
107 kolkata 1 0 0 0
second pivot result:
AppNo wc Dehra. Delphi Kolk.
107 from HR 0 0 1
104 from wc 1 0 0
100 to HR 0 1 0
101 to wc 0 1 0