表A定义如下:
属性 类型
Year Integer
Quarter Varchar(30)
Amount float
Year Quarter Amount
2000 1 1.1
2000 2 1.2
2000 3 1.3
2000 4 1.4
2001 1 2.1
2001 2 2.2
2001 3 2.3
2001 4 2.4
其中每行表表示一个季度的数据。
如果处理表A中的数据,得到如下的结果。
Year Quarter1 Quarter2 Quarter3 Quarter4
2000 1.1 1.2 1.3 1.4 2001 2.1 2.2 2.3 2.4
请用SQL写一段代码实现。
属性 类型
Year Integer
Quarter Varchar(30)
Amount float
Year Quarter Amount
2000 1 1.1
2000 2 1.2
2000 3 1.3
2000 4 1.4
2001 1 2.1
2001 2 2.2
2001 3 2.3
2001 4 2.4
其中每行表表示一个季度的数据。
如果处理表A中的数据,得到如下的结果。
Year Quarter1 Quarter2 Quarter3 Quarter4
2000 1.1 1.2 1.3 1.4 2001 2.1 2.2 2.3 2.4
请用SQL写一段代码实现。
declare @sql varchar(8000)
set @sql='select Year'
select @sql=@sql+',[quarter'+ ltrim(quarter) +']=max(case quarter when ''' + quarter + '''
then amount else 0 end)' from (select quarter from A group by quarter order by quarter)b
exec(@sql+' from A group by year')
Quarter1 = sum(case quarter when '1' then amount else 0 end),
Quarter2 = sum(case quarter when '2' then amount else 0 end),
Quarter3 = sum(case quarter when '3' then amount else 0 end),
Quarter4 = sum(case quarter when '4' then amount else 0 end),
from A
group by year
--quarter是数字型的,稍作改动。declare @sql varchar(8000)
set @sql='select Year'
select @sql=@sql+',[quarter'+ ltrim(quarter) +']=max(case quarter when ''' + ltrim(quarter)+ '''
then amount else 0 end)' from (select quarter from A group by quarter order by quarter)b
exec(@sql+' from A group by year')
(SELECT a FROM dbo.Table_1 WHERE q=2 AND T1.YEAR=YEAR) as A2,
(SELECT a FROM dbo.Table_1 WHERE q=3 AND T1.YEAR=YEAR) as A3 , (SELECT a FROM dbo.Table_1 WHERE q=4 AND T1.YEAR=YEAR) as A4
FROM dbo.Table_1 T1
CREATE TABLE [dbo].[Table_1](
[year] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Q] [smallmoney] NULL,
[A] [money] NULL
) ON [PRIMARY]---------
2000 1.10 1.20 1.30 1.40
2000 1.10 1.20 1.30 1.40
2000 1.10 1.20 1.30 1.40
2000 1.10 1.20 1.30 1.40
2001 2.10 2.20 2.30 2.40
2001 2.10 2.20 2.30 2.40
2001 2.10 2.20 2.30 2.40
2001 2.10 2.20 2.30 2.40
[year] [varchar](50) ,
[Quarter] [int] ,
[Amount] [money]
)
insert into tab select 2000,1,1.1
insert into tab select 2000,2,1.2
insert into tab select 2000,3,1.3
insert into tab select 2000,4,1.4
insert into tab select 2001,1,2.1
insert into tab select 2001,2,2.2
insert into tab select 2001,3,2.3
insert into tab select 2001,4,2.4declare @sql varchar(8000)
set @sql='select [year] '
select @sql=@sql+',max(case when [quarter]='''+ltrim([quarter])+''' then amount else null end ) as quarter'+ltrim([quarter])
from (select distinct [quarter] from tab) a
set @sql=@sql+' from tab group by [year]'
print @sql
exec(@sql)
year quarter1 quarter2 quarter3 quarter4
2000 1.10 1.20 1.30 1.40
2001 2.10 2.20 2.30 2.40