Request for a sample of types by year



  • There are two tables, can one request select the entries grouped by type(s) and year with the number? And if one request cannot be made, how can it be best done?

    [NAME]
    ID|NAME

    1 Вася
    2 Маша
    3 Петя

    [DATA]
    ID|ID_N|SDATE

    1 1 2008
    2 1 2008
    3 1 2010
    4 2 2011
    <...>

    Result:

            2008  2010  2011
    Вася 2 1 0
    Маша 0 0 1
    Петя 0 0 0



  • Try this way.

    DECLARE @cols AS NVARCHAR(MAX)
    DECLARE @query AS NVARCHAR(MAX)
    

    SELECT @cols = ISNULL(@cols + ', ', '') + QUOTENAME(SDATE)
    FROM (SELECT DISTINCT SDATE FROM DATA) AS b;

    set @query ='select *
    from
    (
    select NAME, SDATE, count(*) cou
    from NAME, DATA
    where NAME.id = DATA.id_n
    group by NAME, SDATE
    ) s
    pivot (sum(cou) for SDATE in ('+@cols+')) pvt'

    execute(@query)


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2