Put something into each row in a GROUP BY statement

• Edvard Korsbæk

  SSCertifiable

  Points: 5999

  More actions

  November 11, 2020 at 8:13 am

  Go to Answer

  #3806712

  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

   

• Steve Collins

  SSCrazy

  Points: 2214

  More actions

  November 11, 2020 at 3:03 pm

  Answer

  #3806831

  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

• Edvard Korsbæk

  SSCertifiable

  Points: 5999

  More actions

  November 11, 2020 at 4:50 pm

  #3806873

  Thanks!

   

  Did exactly what i want.

   

  mgf

  Edvard Korsbæk