CODEWITHSUNDEEP
sql-servert-sql
user1098028
user1098028

Reputation: 69

Sql get count and total rows count dynamically

I have two tables as below

NameId      Name 
11       Ancillary
22       Reviews
33       Audit
44       Logging

id      NameId    CountyId
51       11       1
52       11       1
53       11       2
54       22       2
55       22       3
56       33       3
57       33       3

Results should be total number of names associated to a county as below. Can the Nameid be dynamic meaning if more than 4 names are there query should be able to show the count automatically without hard coding row name. Also calculate total for each row at the end.

CountyId AncillaryCount   ReviewsCount  AuditCount    LoggingCount   Total   
1           2             0             0               0               2
2           1             1             0               0               2
3           0             1             2               0               3

Upvotes: 0

Views: 112

Answers (1)

user7715598
user7715598

Reputation: 

IF OBJECT_ID('Tempdb..#Temp') IS NOT NULL
Drop table #Temp

DECLARE @TabA Table (NameId INT,Name Varchar(100))
INSERT INTO @TabA
SELECT 11,'Ancillary' Union all
SELECT 22,'Reviews'   Union all
SELECT 33,'Audit'     Union all
SELECT 44,'Logging'

DECLARE @TabB Table (id INT, NameId INT,CountyId INT)
INSERT INTO @TabB
SELECT 51,11,1 UNION ALL
SELECT 52,11,1 UNION ALL
SELECT 53,11,2 UNION ALL
SELECT 54,22,2 UNION ALL
SELECT 55,22,3 UNION ALL
SELECT 56,33,3 UNION ALL
SELECT 57,33,3 UNION ALL
SELECT 57,44,3 

SELECT B.CountyId,
A.Name, 
COUNT(A.Name) Over(Partition by B.CountyId,A.Name Order by B.CountyId) AS CountName 
INTO #Temp from @TabA A
LEFT join @TabB B
On b.NameId=a.NameId


DECLARE @Coulmn nvarchar(max),
        @Coulmn2 nvarchar(max),
        @SumCoulmn nvarchar(max),
        @Sql nvarchar(max)

SELECT @Coulmn=STUFF((SELECT DISTINCT ', '+ '['+ Name +']' From #Temp
FOR XML PATH ('')),1,1,'')

SELECT @SumCoulmn=STUFF((SELECT DISTINCT ', '+ + Name +'Count' From #Temp
FOR XML PATH ('')),1,1,'')
SET @SumCoulmn= '('+REPLACE(@SumCoulmn,',',' + ')+')'

SELECT @Coulmn2=STUFF((SELECT DISTINCT ', '+ 'ISNULL(' + Name + ',''0'')' +' AS  ['+Name +'Count]' From #Temp
FOR XML PATH ('')),1,1,'')

SET @Sql='SELECT *,'+@SumCoulmn+ ' AS Total From 
            (  
          SELECT CountyId,'+@Coulmn2+ ' From 
            (
            SELECT * From #Temp
            )As Src
            PIVOT
            (
            MAX(CountName) FOR Name IN ('+ @Coulmn +')
            )Pvt
            )Dt
            '
Print @Sql
Exec(@Sql)

Upvotes: 1

Related Questions