Sql get count and total rows count dynamically

Question

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

Search for 'dynamic pivot sql', there should be a bunch of results. If it doesn't actually need to be dynamic (e.g. you only have 4 different ones you actually need a count of), you can just do a regular pivot (or case aggregation). — ZLK, Jun 13 '17 at 03:50
Possible duplicate of [T-SQL dynamic pivot](https://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot) — iamdave, Jun 13 '17 at 10:05

score 1 · Answer 1 · answered Jun 13 '17 at 08:05

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)

A code-only answer isn't a good answer. Explain what this does and why it asnwers the OP's question. It doesn't help the OP and isn't useful to other people that may have a similar problem. SO is a Q&A site, not a discussion forum. You are aggregating names using the second-fastest method. Then you are creating a PIVOT statemement. What's with the temporary tables though? Isn't that a job for PIVOT ? — Panagiotis Kanavos, Jun 13 '17 at 08:12
@PanagiotisKanavos As a Developer ,in my perspective i wrote the code ,If you thought that you can give better answer you can give but don't criticize others — , Jun 13 '17 at 08:35
Please check the SO tour and the guidelines, especially [how to write a good answer](https://stackoverflow.com/help/how-to-answer). Code-only answers are considered low quality and flagged automatically for review. This [has been discussed extensively](https://meta.stackoverflow.com/questions/290231/is-it-wrong-to-ask-for-an-explanation-of-how-why-the-code-works-in-code-only-ans). — Panagiotis Kanavos, Jun 13 '17 at 09:27

Sql get count and total rows count dynamically

1 Answers1