现在有2张表:dbo.ChargeOriginByDeviceCode和dbo.Origin
第一张表的数据有:
date originid devicecode price usercount
2012-02-14 787 gbox 200 12
2012-02-14 788 xggbox 400 20
2012-02-14 789 gbox 300 300
2012-02-14 790 gboxmtk 500 250
另一张表:
originid originname
787 艾丽卡
788 艾丽卡
789 艾丽卡
790 艾丽卡我现在想要的结果:根据originname为艾丽卡的originid的钱数
date 787gbox 788xggbox 789gbox 790gboxmtk
2012-02-14 200 400 300 500表头是第一张表中的originid+devicecode组成的
请问各位,这个要怎么办呢?
第一张表的数据有:
date originid devicecode price usercount
2012-02-14 787 gbox 200 12
2012-02-14 788 xggbox 400 20
2012-02-14 789 gbox 300 300
2012-02-14 790 gboxmtk 500 250
另一张表:
originid originname
787 艾丽卡
788 艾丽卡
789 艾丽卡
790 艾丽卡我现在想要的结果:根据originname为艾丽卡的originid的钱数
date 787gbox 788xggbox 789gbox 790gboxmtk
2012-02-14 200 400 300 500表头是第一张表中的originid+devicecode组成的
请问各位,这个要怎么办呢?
date datetime,
originid varchar(3),
devicecode varchar(16),
price int,
usercount int)
Create Table Origin(
originid varchar(3),
originname nvarchar(16))
go
insert into ChargeOriginByDeviceCode
select '2012-02-14', '787', 'gbox', 200, 12 union all
select '2012-02-14', '788', 'xggbox', 400, 20 union all
select '2012-02-14', '789', 'gbox', 300, 300 union all
select '2012-02-14', '790', 'gboxmtk', 500, 250insert into Origin
select '787', '艾丽卡' union all
select '788', '艾丽卡' union all
select '789', '艾丽卡' union all
select '790', '艾丽卡'select *
from (
select date, c.originid + devicecode as exp1, c.price
from ChargeOriginByDeviceCode c,Origin o
where c.originid = o.originid and o.originname = '艾丽卡'
) a
pivot ( sum(price) for exp1 in ([787gbox], [788xggbox], [789gbox], [790gboxmtk])) as tdrop table ChargeOriginByDeviceCode
drop table Origin/*
date 787gbox 788xggbox 789gbox 790gboxmtk
----------------------- ----------- ----------- ----------- -----------
2012-02-14 00:00:00.000 200 400 300 500
*/
date datetime,
originid varchar(3),
devicecode varchar(16),
price int,
usercount int)
Create Table Origin(
originid varchar(3),
originname nvarchar(16))
go
insert into ChargeOriginByDeviceCode
select '2012-02-14', '787', 'gbox', 200, 12 union all
select '2012-02-14', '788', 'xggbox', 400, 20 union all
select '2012-02-14', '789', 'gbox', 300, 300 union all
select '2012-02-14', '790', 'gboxmtk', 500, 250insert into Origin
select '787', '艾丽卡' union all
select '788', '艾丽卡' union all
select '789', '艾丽卡' union all
select '790', '艾丽卡'select *
from (
select date, c.originid + devicecode as exp1, c.price
from ChargeOriginByDeviceCode c,Origin o
where c.originid = o.originid and o.originname = '艾丽卡'
) a
pivot ( sum(price) for exp1 in ([787gbox], [788xggbox], [789gbox], [790gboxmtk])) as tdrop table ChargeOriginByDeviceCode
drop table Origin/*
date 787gbox 788xggbox 789gbox 790gboxmtk
----------------------- ----------- ----------- ----------- -----------
2012-02-14 00:00:00.000 200 400 300 500
*/
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + exp1
from (
select c.originid + devicecode as exp1
from ChargeOriginByDeviceCode c,Origin o
where c.originid = o.originid and o.originname = '艾丽卡'
) a
set @sql = '[' + @sql + ']'set @sql = '
select *
from (
select date, c.originid + devicecode as exp1, c.price
from ChargeOriginByDeviceCode c,Origin o
where c.originid = o.originid and o.originname = ''艾丽卡''
) a
pivot ( sum(price) for exp1 in (' + @sql + ')) as t'
exec (@sql)