I have this script, which gives me correct info :
SELECT datepart(yyyy, [Første reg# dato]) AS årgang, COUNT(*) AS antal, Model,variant FROM dbo.[Ark1$]
WHERE model <> '-'
GROUP BY model,Variant,datepart(yyyy, [Første reg# dato])
ORDER BY DATEPART(yyyy, [Første reg# dato]),model,Variant
A part of the result is:
1960 2 125-3 UOPLYST
1960 2 ES 250
1960 2 RT 125
1961 2 250 UOPLYST
1961 2 ES 175
1961 2 ES 250
1962 2 1 WL CAMPI-PåHÆNGSVOGN MC
1962 2 250 UOPLYST
1962 2 ES 250
1962 2 ES 300
problem is, that it's 166 rows in total, and that's a bit difficult to place in a small magazine.
What i could use is:
1960 2 125-3 UOPLYST, 2 ES 250, 2 RT 125
1961 2 250 UOPLYST, 2 ES 175, 2 ES 250
1962 .....
Still group by datepart(yyyy, [Første reg# dato]), but i just cannot figure out 'How to'.
If interesting, its the numbers of veteran motorcycles from DDR in Denmark..
Best regards
Edvard Korsbæk
Step 1 could be to summarize by the concatenation of MODEL and VARIANT. Then further summarize by year (årgang) and use STUFF+FOR XML to aggregate the concatenation of MV_ANTAL and MODEL_VARIANT ordered by MODEL_VARIANT. Something like these 2 (pre/post SQL Server 2016) queries
/* pre-SQL Server 2016 using STUFF+FOR XML */
with yr_mv_cte(årgang, model_variant, mv_antal) as (
select datepart(yyyy, [Første reg# dato]),
concat(model, ' ', variant),
count(*)
from dbo.[Ark1$]
where model <> '-'
group by datepart(yyyy, [Første reg# dato]),
concat(model, ' ', variant))
select ymc.årgang,
stuff((select ', ' + concat(cast(ymc1.mv_antal as varchar(12)), ' ', ymc1.model_variant)
from yr_mv_cte ymc1
where ymc1.årgang=ymc.årgang
and ymc1.model_variant=ymc.model_variant
order by ymc1.model_variant
for xml path('')), 1, 2, '') newCol,
sum(mv_antal) as total_antal
from yr_mv_cte ymc
group by ymc.årgang
order by ymc.årgang;
/* SQL Server 2016+ using STRING_AGG */
with yr_mv_cte as (
select datepart(yyyy, [Første reg# dato]) as årgang,
concat(model, ' ', variant) model_variant,
count(*) as mv_antal
from dbo.[Ark1$]
where model <> '-'
group by datepart(yyyy, [Første reg# dato]),
concat(model, ' ', variant))
select årgang,
string_agg(concat(cast(mv_antal as varchar(12)), ' ', model_variant), ',')
within group (order by model_variant) newCol,
sum(mv_antal) as total_antal
from yr_mv_cte
group by årgang
order by årgang;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 11, 2020 at 4:50 pm
Thanks!
Did exactly what i want.
mgf
Edvard Korsbæk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply