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
– ZLK, Commented Jun 13, 2017 at 3:50

user7715598 · Accepted Answer · 2017-06-13 08:05:28Z

1

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)

answered Jun 13, 2017 at 8:05

user7715598

Sign up to request clarification or add additional context in comments.

3 Comments

Panagiotis Kanavos Over a year ago

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 ?

user7715598 Over a year ago

@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

Panagiotis Kanavos Over a year ago

Please check the SO tour and the guidelines, especially how to write a good answer. Code-only answers are considered low quality and flagged automatically for review. This has been discussed extensively.

Collectives™ on Stack Overflow

Sql get count and total rows count dynamically

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related