This can be accomplished using pivot function. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. See pivot function at: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16
Display Totals of a table
Debilon
431
Reputation points
I need to find totals of a table and create a report
If a rating is Y it counts as 1
if all ratings are N is counts as UnDesignated.
DECLARE @T AS TABLE
(
id int identity(1,1),
BName Varchar (50),
Adress1 Varchar(50),
City Varchar(10),
BState Varchar(2),
ARatingClass1 Varchar(1),
ARatingClass2 Varchar(1),
ARatingClass3 Varchar(1),
PRatingClass1 Varchar(1),
PRatingClass2 Varchar(1),
PRatingClass3 Varchar(1),
IRatingClass1 Varchar(1),
IRatingClass2 Varchar(1),
IRatingClass3 Varchar(1)
)
INSERT INTO @T VALUES
('Alpha','128 Alpha Street','New York','NY', 'N','Y','N','Y','Y','Y','N','Y','Y'),
('Bravo','126 Bravo Street','Van Nuys','CA', 'Y','N','Y','N','N','n','y','N','N'),
('Charlie','124 Charlie Street','Chicago','IL', 'Y','N','Y','N','N','n','y','N','N'),
('Delta','121 Delta Street','Miami','FL', 'N','N','N','N','N','N','N','N','N'),
('Echo','119 Echo Street','Augusta','GA', 'N','N','N','N','N','N','N','N','N')
...
Totals:
ARatingClass1: 2
ARatingClass2: 1
ARatingClass3: 2
PRatingClass1: 1
PRatingClass2: 1
PRatingClass3: 1
IRatingClass1: 2
IRatingClass2: 1
IRatingClass3: 1
UnDesignated: 2
SQL Server | Other
14,503 questions
5 answers
Sort by: Most helpful
-
-
Jingyang Li 5,896 Reputation points Volunteer Moderator
2023-02-17T15:38:58.3833333+00:00 Here is the unpivot part:
DECLARE @T AS TABLE ( id int identity(1,1), BName Varchar (50), Adress1 Varchar(50), City Varchar(10), BState Varchar(2), ARatingClass1 Varchar(1), ARatingClass2 Varchar(1), ARatingClass3 Varchar(1), PRatingClass1 Varchar(1), PRatingClass2 Varchar(1), PRatingClass3 Varchar(1), IRatingClass1 Varchar(1), IRatingClass2 Varchar(1), IRatingClass3 Varchar(1) ) INSERT INTO @T VALUES ('Alpha','128 Alpha Street','New York','NY', 'N','Y','N','Y','Y','Y','N','Y','Y'), ('Bravo','126 Bravo Street','Van Nuys','CA', 'Y','N','Y','N','N','n','y','N','N'), ('Charlie','124 Charlie Street','Chicago','IL', 'Y','N','Y','N','N','n','y','N','N'), ('Delta','121 Delta Street','Miami','FL', 'N','N','N','N','N','N','N','N','N'), ('Echo','119 Echo Street','Augusta','GA', 'N','N','N','N','N','N','N','N','N') --... --Totals: -- ARatingClass1: 2 -- ARatingClass2: 1 -- ARatingClass3: 2 -- PRatingClass1: 1 -- PRatingClass2: 1 -- PRatingClass3: 1 -- IRatingClass1: 2 -- IRatingClass2: 1 -- IRatingClass3: 1 -- UnDesignated: 2 select Col,Sum(case when val='Y' then 1 else 0 end) cnt from @t t CROSS APPLY (Values ('ARatingClass1', ARatingClass1), ('ARatingClass2', ARatingClass2), ('ARatingClass3', ARatingClass3), ('PRatingClass1', PRatingClass1), ('PRatingClass2', PRatingClass2), ('PRatingClass3', PRatingClass3), ('IRatingClass1',IRatingClass1), ('IRatingClass2',IRatingClass2), ('IRatingClass3',IRatingClass3)) d(Col,val) Group by Col --Need more information for UnDesignated
-
Jingyang Li 5,896 Reputation points Volunteer Moderator
2023-02-17T15:52:36.73+00:00 the whole query:
DECLARE @T AS TABLE ( id int identity(1,1), BName Varchar (50), Adress1 Varchar(50), City Varchar(10), BState Varchar(2), ARatingClass1 Varchar(1), ARatingClass2 Varchar(1), ARatingClass3 Varchar(1), PRatingClass1 Varchar(1), PRatingClass2 Varchar(1), PRatingClass3 Varchar(1), IRatingClass1 Varchar(1), IRatingClass2 Varchar(1), IRatingClass3 Varchar(1) ) INSERT INTO @T VALUES ('Alpha','128 Alpha Street','New York','NY', 'N','Y','N','Y','Y','Y','N','Y','Y'), ('Bravo','126 Bravo Street','Van Nuys','CA', 'Y','N','Y','N','N','n','y','N','N'), ('Charlie','124 Charlie Street','Chicago','IL', 'Y','N','Y','N','N','n','y','N','N'), ('Delta','121 Delta Street','Miami','FL', 'N','N','N','N','N','N','N','N','N'), ('Echo','119 Echo Street','Augusta','GA', 'N','N','N','N','N','N','N','N','N') ;with mycte as ( select BName,Col,Sum(case when val='Y' then 1 else 0 end) cnt from @t t CROSS APPLY (Values ('ARatingClass1', ARatingClass1), ('ARatingClass2', ARatingClass2), ('ARatingClass3', ARatingClass3), ('PRatingClass1', PRatingClass1), ('PRatingClass2', PRatingClass2), ('PRatingClass3', PRatingClass3), ('IRatingClass1',IRatingClass1), ('IRatingClass2',IRatingClass2), ('IRatingClass3',IRatingClass3)) d(Col,val) Group by Col,BName) ,cteUnDesignated as ( select 'UnDesignated' Col, count(distinct BName) cnt from mycte group by BName Having(sum(cnt)=0) ) select Col, Sum(cnt) cnt from mycte group by Col UNION ALL select Col,sum(cnt) cnt from cteUnDesignated group by Col --Need more information for UnDesignated
-
Jingyang Li 5,896 Reputation points Volunteer Moderator
2023-02-17T19:19:40.9633333+00:00 DECLARE @T AS TABLE ( id int identity(1,1), BName Varchar (50), Adress1 Varchar(50), City Varchar(10), BState Varchar(2), ARatingClass1 Varchar(1), ARatingClass2 Varchar(1), ARatingClass3 Varchar(1), PRatingClass1 Varchar(1), PRatingClass2 Varchar(1), PRatingClass3 Varchar(1), IRatingClass1 Varchar(1), IRatingClass2 Varchar(1), IRatingClass3 Varchar(1) ) INSERT INTO @T VALUES ('Alpha','128 Alpha Street','New York','NY', 'N','Y','N','Y','Y','Y','N','Y','Y'), ('Bravo','126 Bravo Street','Van Nuys','CA', 'Y','N','Y','N','N','n','y','N','N'), ('Charlie','124 Charlie Street','Chicago','IL', 'Y','N','Y','N','N','n','y','N','N'), ('Delta','121 Delta Street','Miami','FL', 'N','N','N','N','N','N','N','N','N'), ('Echo','119 Echo Street','Augusta','GA', 'N','N','N','N','N','N','N','N','N') ;with mycte as ( select BName,Col,Sum(case when val='Y' then 1 else 0 end) cnt from @t t CROSS APPLY (Values ('ARatingClass1', ARatingClass1), ('ARatingClass2', ARatingClass2), ('ARatingClass3', ARatingClass3), ('PRatingClass1', PRatingClass1), ('PRatingClass2', PRatingClass2), ('PRatingClass3', PRatingClass3), ('IRatingClass1',IRatingClass1), ('IRatingClass2',IRatingClass2), ('IRatingClass3',IRatingClass3)) d(Col,val) Group by Col,BName) ,cteUnDesignated as ( select 'UnDesignated' Col, count(distinct BName) cnt from mycte group by BName Having(sum(cnt)=0) ) ,mycteFinal as ( select Col, Sum(cnt) cnt from mycte group by Col UNION ALL select Col,sum(cnt) cnt from cteUnDesignated group by Col ) ,myPivot as ( select Col, cnt ,row_number()Over(partition by left(Col,10) Order by Right(Col,1) ) rn from mycteFinal) Select max(Case when rn=1 then Col else null end) col1, max(Case when rn=1 then cnt else null end) cnt1, max(Case when rn=2 then Col else null end) col2, max(Case when rn=2 then cnt else null end) cnt2, max(Case when rn=3 then Col else null end) col3, max(Case when rn=3 then cnt else null end) cnt3 from mypivot group by left(Col,10)
-
Anonymous
2023-02-20T06:29:29.03+00:00 Hi @Debilon
In response to your initial question, I wrote the following query.
DECLARE @T AS TABLE (id int identity(1,1), BName Varchar (50), Adress1 Varchar(50), City Varchar(10), BState Varchar(2), ARatingClass1 Varchar(1), ARatingClass2 Varchar(1), ARatingClass3 Varchar(1), PRatingClass1 Varchar(1), PRatingClass2 Varchar(1), PRatingClass3 Varchar(1), IRatingClass1 Varchar(1), IRatingClass2 Varchar(1), IRatingClass3 Varchar(1) ) INSERT INTO @T VALUES ('Alpha','128 Alpha Street','New York','NY', 'N','Y','N','Y','Y','Y','N','Y','Y'), ('Bravo','126 Bravo Street','Van Nuys','CA', 'Y','N','Y','N','N','n','y','N','N'), ('Charlie','124 Charlie Street','Chicago','IL', 'Y','N','Y','N','N','n','y','N','N'), ('Delta','121 Delta Street','Miami','FL', 'N','N','N','N','N','N','N','N','N'), ('Echo','119 Echo Street','Augusta','GA', 'N','N','N','N','N','N','N','N','N'); with T1 as( select case when ARatingClass1 = 'Y' then 1 else 0 end as ARatingClass1, case when ARatingClass2 = 'Y' then 1 else 0 end as ARatingClass2, case when ARatingClass3 = 'Y' then 1 else 0 end as ARatingClass3, case when PRatingClass1 = 'Y' then 1 else 0 end as PRatingClass1, case when PRatingClass2 = 'Y' then 1 else 0 end as PRatingClass2, case when PRatingClass3 = 'Y' then 1 else 0 end as PRatingClass3, case when IRatingClass1 = 'Y' then 1 else 0 end as IRatingClass1, case when IRatingClass2 = 'Y' then 1 else 0 end as IRatingClass2, case when IRatingClass3 = 'Y' then 1 else 0 end as IRatingClass3 from @T ),T2 as( select *,ARatingClass1 + ARatingClass2 + ARatingClass3 + PRatingClass1 + PRatingClass2 + PRatingClass3 + IRatingClass1 + IRatingClass2 + IRatingClass3 as uns from T1 ),T3 as( select ARatingClass1,ARatingClass2,ARatingClass3 ,PRatingClass1,PRatingClass2,PRatingClass3 ,IRatingClass1,IRatingClass2,IRatingClass3, case when uns = 0 then 1 else 0 end as uns from T2 ),T4 as( select sum(ARatingClass1) as ARatingClass1,sum(ARatingClass2) as ARatingClass2 ,sum(ARatingClass3) as ARatingClass3,sum(PRatingClass1) as PRatingClass1, sum(PRatingClass2) as PRatingClass2,sum(PRatingClass3) as PRatingClass3, sum(IRatingClass1) as IRatingClass1,sum(IRatingClass2) as IRatingClass2, sum(IRatingClass3) as IRatingClass3,sum(uns) as UnDesignated from T3 ),T5 as( select * from T4 as t unpivot(nums for kinds in(ARatingClass1,ARatingClass2,ARatingClass3, PRatingClass1,PRatingClass2,PRatingClass3,IRatingClass1,IRatingClass2, IRatingClass3,UnDesignated)) as d) select kinds,nums from T5;
But when I finished writing and looked at your new comments, I realized that your real problem was much more complicated. The list of categories is so long, it's hard to imagine how complex a query would be if you just used it. Maybe you can consider Jingyang Li's suggestion.
Best regards,
Percy Tang