Copy codeThe Code is as follows:
-- Create database dbTemp
Use dbTemp
Create table test
(
Pid int identity (1, 1) not null primary key,
Years datetime,
IsFirstSixMonths int default (0), -- 0 indicates first half 1 indicates second half --
TotalCome int
)
Insert test
Select '2014-1-1 ', 2007
Union
Select '2017-3-1 ', 2007
Union
Select '2014-12-1 ', 2007
Union
Select '2014-1-1 ', 2008
Union
Select '2017-12-1 ', 2008
Select * from test
Select convert (char (4), Years, 120) as 'Year ',
IsFirstSixMonths = case when IsFirstSixMonths = 0 then 'when IsFirstSixMonths = 1 then' second half 'end,
Sum (totalcome) as 'sum' from test
Group by IsFirstSixMonths, convert (char (4), Years, 120)
Select convert (char (4), Years, 120) as 'Year ',
IsFirstSixMonths = case when IsFirstSixMonths = 0 then 'else' second half 'end,
Sum (totalcome) as 'sum' from test
Group by IsFirstSixMonths, convert (char (4), Years, 120)
-- Drop database dbtemp
The result is as follows:
Copy codeThe Code is as follows:
2007 first half 110
2007
2008 First Half 100
2008 second half 100