SQL series Cross reports

We will use the research report:

1. Simple report operations:

The preceding table can be generated after the following operations:

SELECT <br/> [Year], <br/> Q1 = SUM (case when [Quarter] = 1 THEN Quantity END ), <br/> Q2 = SUM (case when [Quarter] = 2 THEN Quantity END), <br/> Q3 = SUM (case when [Quarter] = 3 THEN Quantity END ), <br/> Q4 = SUM (case when [Quarter] = 4 THEN Quantity END) <br/> FROM tb group by [Year] <br/>
In addition to SUM, other common Aggregate functions such as AVG, MIN, SUM, STDEV, COUNT, STDEVP, VAR, GROUPING, VARP, and MAX can also be used.
Supplement:
STDEV returns the statistical standard deviation of all values in a given expression.
STDEVP returns the population statistics standard deviation of all values in a given expression.

VAR returns the statistical variance of all values in the given expression.
VARP returns the population statistical variance of all values in the given expression.
Note: The preceding Aggregate functions can only be used for numeric columns.
GROUPING is often used in combination with CUBE and ROLLUP. If it is an additional column, output 1; otherwise, output 0.
2. Cross reports involving multiple columns

SQL statement:

Select [year], <br/> q1_quantity = sum (case when [quarter] = 1 then quantity end ), <br/> q1_price = cast (AVG (case when [quarter] = 1 then quantity end) as decimal )), <br/> qinimoney = sum (case when [quarter] = 1 then cast (quantity * price as decimal (10, 2) end ), <br/> q2_quantity = sum (case when [quarter] = 2 then quantity end ), <br/> q2_price = cast (AVG (case when [quarter] = 2 then quantity end) as decimal (10, 2 )), <br/> q2_money = sum (case when [quarter] = 2 then cast (quantity * price as decimal (10, 2) end ), <br/> q3_quantity = sum (case when [quarter] = 3 then quantity end ), <br/> q3_price = cast (AVG (case when [quarter] = 3 then quantity end) as decimal (10, 2 )), <br/> q3_money = sum (case when [quarter] = 3 then cast (quantity * price as decimal (10, 2) end ), <br/> q4_quantity = sum (case when [quarter] = 4 then quantity end ), <br/> q4_price = cast (AVG (case when [quarter] = 4 then quantity end) as decimal (10, 2 )), <br/> q4_money = sum (case when [quarter] = 4 then cast (quantity * price as decimal (10, 2) end) <br/> from TB gro <span style = "color: # ff0000"> up by [year] <br/> </span>

3. columns that dynamically generate a cross report:
Charts generated this time can be written in SQL statements:

DECLARE @ s nvarchar (4000) <br/> SET @ S = ''<br/> SELECT @ S = @ S + '<br/> Q' + CAST ([Quarter] as nvarchar) + '_ Quantity = SUM (case when [Quarter] =' + CAST ([Quarter] as nvarchar) + 'then Quantity END ), <br/> Q' + CAST ([Quarter] as nvarchar) + '_ Price = CAST (AVG (case when [Quarter] =' + CAST ([Quarter] as nvarchar) + 'then Quantity END) as decimal (10, 2), <br/> Q' + CAST ([Quarter] as nvarchar) + '_ Money = SUM (case when [Quarter] =' + CAST ([Quarter] as nvarchar) + 'then CAST (Quantity * Price as decimal (10, 2) END ), 'From tb group by [Quarter] <br/> SET @ S = 'select [Year], '+ SUBSTRING (@ S, 1, LEN (@ S)-1) + 'from tb group by [Year] '<br/> EXEC (@ S) <br/>