This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(195.2, 70%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ED%3C/text%3E%3C/svg%3E)
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 97%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHR%3C/text%3E%3C/svg%3E)
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(278.4, 12%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJL%3C/text%3E%3C/svg%3E)
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
Thank you for this.
How do I calculate the UnDesignated ?
meaning the count of those records that all their ratings all across =- 'N' ?
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
How do i split the results based on the category and draw a "table" around it ?
--Totals: --
ARatingClass1: 2 ARatingClass2: 1 ARatingClass3: 2
PRatingClass1: 1 PRatingClass2: 1 PRatingClass3: 1
IRatingClass1 : 2 IRatingClass2: 1 IRatingClass3: 1
UnDesignated: 2
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)
Let me ask a follow-up question
In real life my pivot table will have 5 columns
max(Case when rn=1 then Col else null end) 'Class 1',
max(Case when rn=1 then cnt else null end) Total,
max(Case when rn=2 then Col else null end) 'Class 2',
max(Case when rn=2 then cnt else null end) Total,
max(Case when rn=3 then Col else null end) 'Class 3',
max(Case when rn=3 then cnt else null end) Total,
max(Case when rn=4 then Col else null end) 'Class 4',
max(Case when rn=4 then cnt else null end) Total,
max(Case when rn=5 then Col else null end) 'Class Limited',
max(Case when rn=5 then cnt else null end) Total
The Category list will be much longer
('Accessory Rating Class 1', AccessoryRatingClass1),
('Accessory Rating Class 2', AccessoryRatingClass2),
('Accessory Rating Class 3', AccessoryRatingClass3),
('Accessory Rating Class 4', AccessoryRatingClass4),
('Accessory Rating Limited', AccessoryRatingLimited),
('Airframe Rating Class 1' , AirframeRatingClass1),
('Airframe Rating Class 2' , AirframeRatingClass2),
('Airframe Rating Class 3' , AirframeRatingClass3),
('Airframe Rating Class 4' , AirframeRatingClass4),
('Airframe Rating Limited' , AirframeRatingLimited),
('Instrument Rating Class 1' , InstrumentRatingClass1),
('Instrument Rating Class 2' , InstrumentRatingClass2),
('Instrument Rating Class 3' , InstrumentRatingClass3),
('Instrument Rating Class 4' , InstrumentRatingClass4),
('Instrument Rating Class Limited' , InstrumentRatingLimited),
('Power Plant Rating Class 1' , PowerplantRatingClass1),
('Power Plant Rating Class 2' , PowerplantRatingClass2),
('Power Plant Rating Class 3' , PowerplantRatingClass3),
('Power Plant Rating Limited' , PowerplantRatingLimited),
('Propeller Rating Class 1' , PropellerRating1),
('Propeller Rating Class 2' , PropellerRating2),
('Propeller Rating Limited', PropellerRatingLimited),
('Radio Rating Class 1', RadioRatingClass1),
('Radio Rating Class 2', RadioRatingClass2),
('Radio Rating Class 3', RadioRatingClass3),
('Radio Rating Limited', RadioRatinglimited),
('Limited Rating Equipment', LimitedRatingEquipment),
('Limited Rating Aircraft Fabric', LimitedRatingAircraftFabric),
('Limited Rating Floats', LimitedRatingFloats),
('Limited Rating Specialized Servic', LimitedRatingSpecializedServic),
('Limited Rating Rotor Blades' , LimitedRatingRotorBlades),
('Limited Rating Landing Gear', LimitedRatingLandingGear),
('Limited Rating Other ', LimitedRatingOther) ,
('Limited Rating Non Destructive Test', LimitedRatingNonDestructiveTst))
My desired output is that everything will be sorted by its class meaning : class 1 will appear under the class 1 column of the pivot table class 2 will appear under the class 2 column of the pivot table class 3 will appear under the class 3 column of the pivot table class 4 will appear under the class 4 column of the pivot table class limited will appear under limited column (5) of the pivot table.
sometimes, a category will have only part of the classes (like the propellor class above)
I would suggest you to look at your data table and find a proper tool to get the result you need. It is still possible to continue but you may run into deep learning curve.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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