Query to count records by department and display in Pivoted format in SQL Server

Hi PRA,

Refer below query.

SQL

CREATE TABLE #DEPORTMENT(Id INT, NAMEDEP VARCHAR(10))
INSERT INTO #DEPORTMENT(Id, NAMEDEP)
SELECT 1,'DENTIST'
UNION ALL
SELECT 2,'UROLOGY'
UNION ALL
SELECT 3,'EYE DOCTOR'

CREATE TABLE #HOSPITAL(Id INT, DEPORT INT, NUMBER INT)
INSERT INTO #HOSPITAL(Id, DEPORT, NUMBER)
SELECT 1,1, 1
UNION ALL
SELECT 2,1, 1
UNION ALL
SELECT 3,3, 1
UNION ALL
SELECT 4,2, 1
UNION ALL
SELECT 5,1, 1
UNION ALL
SELECT 6,2, 1
UNION ALL
SELECT 7,3, 1
UNION ALL
SELECT 8,2, 1
UNION ALL
SELECT 9,3, 1
UNION ALL
SELECT 10,2, 1
UNION ALL
SELECT 11,3, 1
UNION ALL
SELECT 12,3, 1

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(NAMEDEP) FROM (SELECT DISTINCT [NAMEDEP] FROM #DEPORTMENT) AS [NAMEDEP]
SET @DynamicPivotQuery = 'SELECT 1 Id, * FROM (
							SELECT d.NAMEDEP,COUNT(d.NAMEDEP) [Count] FROM #DEPORTMENT d
							JOIN #HOSPITAL h ON d.Id = h.DEPORT
							GROUP BY d.NAMEDEP)t
							pivot
							(
								MAX([Count]) FOR NAMEDEP IN ('+@ColumnName+')
							) piv'
EXEC (@DynamicPivotQuery)
DROP TABLE #HOSPITAL
DROP TABLE #DEPORTMENT

Output