Select Distinct 日期组 Into #tmp From 表A
Declare Cur Cursor For
Select 日期组 From #tmp
Declare @Date Varchar(100)
Declare @str Varchar(2000)
Declare @Colum Varchar(50)
Open Cur
Fetch Cur Into @Date
While @@Fetch_Status=0
Begin
Select @Colum=名称 From 表A Where 日期组 = @Date
Set @Str='Alter Table #tmp Add ['+ RTRIM(@Colum) + '] DECIMAL(18,2) NULL DEFAULT 0'
Exec(@str)
Set @Str='UPDATE #tmp SET ['+ RTRIM(@Colum) + ']= T.价额 '+
'FROM(SELECT 价额 ,名称 FROM 表A WHERE 日期组 ='+
@Date +')T WHERE #tmp.['+@Colum+']=T.名称'
Execute(@Str) End
Declare Cur Cursor For
Select 日期组 From #tmp
Declare @Date Varchar(100)
Declare @str Varchar(2000)
Declare @Colum Varchar(50)
Open Cur
Fetch Cur Into @Date
While @@Fetch_Status=0
Begin
Select @Colum=名称 From 表A Where 日期组 = @Date
Set @Str='Alter Table #tmp Add ['+ RTRIM(@Colum) + '] DECIMAL(18,2) NULL DEFAULT 0'
Exec(@str)
Set @Str='UPDATE #tmp SET ['+ RTRIM(@Colum) + ']= T.价额 '+
'FROM(SELECT 价额 ,名称 FROM 表A WHERE 日期组 ='+
@Date +')T WHERE #tmp.['+@Colum+']=T.名称'
Execute(@Str) End
select
[日期组]=name,
[AAA],
[BBB],
[CCC],
[DDD]
from
(select
zj = [日期组] ,
flag = 1,
name = [日期组],
AAA = sum(case when [名称]='AAA' then [价格] else 0 end),
BBB = sum(case when [名称]='BBB' then [价格] else 0 end),
CCC = sum(case when [名称]='CCC' then [价格] else 0 end),
DDD = sum(case when [名称]='DDD' then [价格] else 0 end)
from
a
group by
[日期组]
union all
select
zj = [日期组] ,
flag = 2,
name = '数量',
AAA = sum(case when [名称]='AAA' then [数量] else 0 end),
BBB = sum(case when [名称]='BBB' then [数量] else 0 end),
CCC = sum(case when [名称]='CCC' then [数量] else 0 end),
DDD = sum(case when [名称]='DDD' then [数量] else 0 end)
from
a
group by
[日期组]
union all
select
zj = [日期组] ,
flag = 3,
name = '占比',
AAA = sum(case when [名称]='AAA' then [占比] else 0 end),
BBB = sum(case when [名称]='BBB' then [占比] else 0 end),
CCC = sum(case when [名称]='CCC' then [占比] else 0 end),
DDD = sum(case when [名称]='DDD' then [占比] else 0 end)
from
a
group by
[日期组]
) b
order by
zj,flag