drop table T_Sales
Create table T_Sales(
F_ID int identity(1,1),
F_GoodName varchar(20),
F_Price money,
F_SaleDate smalldatetime)
GO
insert into T_Sales values('14寸彩电',1000,'2007-04-05')
insert into T_Sales values('14寸彩电',1000,'2007-04-15')
insert into T_Sales values('14寸彩电',1000,'2007-04-25')
insert into T_Sales values('14寸彩电',1000,'2007-04-10')
insert into T_Sales values('17寸彩电',1100,'2007-04-15')
insert into T_Sales values('17寸彩电',1100,'2007-04-25')
insert into T_Sales values('17寸彩电',1100,'2007-04-12')
insert into T_Sales values('17寸彩电',1100,'2007-04-13')
insert into T_Sales values('21寸彩电',1200,'2007-04-25')
insert into T_Sales values('29寸彩电',1300,'2007-05-05')
insert into T_Sales values('14寸宽屏彩电',2200,'2007-05-25')
insert into T_Sales values('17寸宽屏彩电',2300,'2007-06-05')
insert into T_Sales values('19寸宽屏彩电',2400,'2007-06-15')
insert into T_Sales values('21寸宽屏彩电',2500,'2007-07-05')
insert into T_Sales values('29寸宽屏彩电',2600,'2007-07-15')
insert into T_Sales values('小鸭冰箱',600,'2007-08-05')
insert into T_Sales values('中鸭冰箱',800,'2007-08-15')
insert into T_Sales values('大鸭冰箱',1000,'2007-09-05')
insert into T_Sales values('瘦鸭冰箱',900,'2007-10-05')
-- select * from T_Salesdeclare @SQL varchar(8000)
set @SQL='select distinct F_GoodName '
select @SQL=@SQL+',sum(case F_GoodName when '''+F_GoodName+''' then F_Price end )as ['+substring(convert(varchar(10),F_SaleDate,120),9,2)+']'
from (select distinct F_GoodName,F_SaleDate from T_Sales)a
exec(@SQL+' from T_Sales group by F_GoodName,F_SaleDate')这样得到的列没办法合并啊,大伙帮帮忙,我要的结果如下:商品名称 4号 5号 6号..10号...15号... 25号
14寸彩电 0 1000 0 1000 1000 100017寸彩电 0 0 0 1100 0...
Create table T_Sales(
F_ID int identity(1,1),
F_GoodName varchar(20),
F_Price money,
F_SaleDate smalldatetime)
GO
insert into T_Sales values('14寸彩电',1000,'2007-04-05')
insert into T_Sales values('14寸彩电',1000,'2007-04-15')
insert into T_Sales values('14寸彩电',1000,'2007-04-25')
insert into T_Sales values('14寸彩电',1000,'2007-04-10')
insert into T_Sales values('17寸彩电',1100,'2007-04-15')
insert into T_Sales values('17寸彩电',1100,'2007-04-25')
insert into T_Sales values('17寸彩电',1100,'2007-04-12')
insert into T_Sales values('17寸彩电',1100,'2007-04-13')
insert into T_Sales values('21寸彩电',1200,'2007-04-25')
insert into T_Sales values('29寸彩电',1300,'2007-05-05')
insert into T_Sales values('14寸宽屏彩电',2200,'2007-05-25')
insert into T_Sales values('17寸宽屏彩电',2300,'2007-06-05')
insert into T_Sales values('19寸宽屏彩电',2400,'2007-06-15')
insert into T_Sales values('21寸宽屏彩电',2500,'2007-07-05')
insert into T_Sales values('29寸宽屏彩电',2600,'2007-07-15')
insert into T_Sales values('小鸭冰箱',600,'2007-08-05')
insert into T_Sales values('中鸭冰箱',800,'2007-08-15')
insert into T_Sales values('大鸭冰箱',1000,'2007-09-05')
insert into T_Sales values('瘦鸭冰箱',900,'2007-10-05')
-- select * from T_Salesdeclare @SQL varchar(8000)
set @SQL='select distinct F_GoodName '
select @SQL=@SQL+',sum(case F_GoodName when '''+F_GoodName+''' then F_Price end )as ['+substring(convert(varchar(10),F_SaleDate,120),9,2)+']'
from (select distinct F_GoodName,F_SaleDate from T_Sales)a
exec(@SQL+' from T_Sales group by F_GoodName,F_SaleDate')这样得到的列没办法合并啊,大伙帮帮忙,我要的结果如下:商品名称 4号 5号 6号..10号...15号... 25号
14寸彩电 0 1000 0 1000 1000 100017寸彩电 0 0 0 1100 0...
F_ID int identity(1,1),
F_GoodName varchar(20),
F_Price money,
F_SaleDate smalldatetime)
GO insert into T_Sales values( '14寸彩电 ',1000, '2007-04-05 ')
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-15 ')
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-25 ')
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-10 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-15 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-25 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-12 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-13 ')
insert into T_Sales values( '21寸彩电 ',1200, '2007-04-25 ')
insert into T_Sales values( '29寸彩电 ',1300, '2007-05-05 ')
insert into T_Sales values( '14寸宽屏彩电 ',2200, '2007-05-25 ')
insert into T_Sales values( '17寸宽屏彩电 ',2300, '2007-06-05 ')
insert into T_Sales values( '19寸宽屏彩电 ',2400, '2007-06-15 ')
insert into T_Sales values( '21寸宽屏彩电 ',2500, '2007-07-05 ')
insert into T_Sales values( '29寸宽屏彩电 ',2600, '2007-07-15 ')
insert into T_Sales values( '小鸭冰箱 ',600, '2007-08-05 ')
insert into T_Sales values( '中鸭冰箱 ',800, '2007-08-15 ')
insert into T_Sales values( '大鸭冰箱 ',1000, '2007-09-05 ')
insert into T_Sales values( '瘦鸭冰箱 ',900, '2007-10-05 ')
-- select * from T_Sales declare @SQL varchar(8000)
set @SQL= 'select distinct F_GoodName '
select @SQL=@SQL+ ',sum(case right(''0''+rtrim(day(F_SaleDate)),2) when '+days+ ' then F_Price end )as [ '+days+ '] '
from (select distinct right('0'+rtrim(day(F_SaleDate)),2) as days from T_Sales) a
exec(@SQL+ ' from T_Sales group by F_GoodName')
go
/*
F_GoodName 05 10 12 13 15 25
-------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
14寸彩电 1000.0000 1000.0000 NULL NULL 1000.0000 1000.0000
14寸宽屏彩电 NULL NULL NULL NULL NULL 2200.0000
17寸彩电 NULL NULL 1100.0000 1100.0000 1100.0000 1100.0000
17寸宽屏彩电 2300.0000 NULL NULL NULL NULL NULL
19寸宽屏彩电 NULL NULL NULL NULL 2400.0000 NULL
21寸彩电 NULL NULL NULL NULL NULL 1200.0000
21寸宽屏彩电 2500.0000 NULL NULL NULL NULL NULL
29寸彩电 1300.0000 NULL NULL NULL NULL NULL
29寸宽屏彩电 NULL NULL NULL NULL 2600.0000 NULL
大鸭冰箱 1000.0000 NULL NULL NULL NULL NULL
瘦鸭冰箱 900.0000 NULL NULL NULL NULL NULL
小鸭冰箱 600.0000 NULL NULL NULL NULL NULL
中鸭冰箱 NULL NULL NULL NULL 800.0000 NULL
*/drop table T_Sales
go
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-05 ')
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-15 ')
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-25 ')
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-10 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-15 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-25 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-12 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-13 ')
insert into T_Sales values( '21寸彩电 ',1200, '2007-04-25 ')
insert into T_Sales values( '29寸彩电 ',1300, '2007-05-05 ')
insert into T_Sales values( '14寸宽屏彩电 ',2200, '2007-05-25 ')
insert into T_Sales values( '17寸宽屏彩电 ',2300, '2007-06-05 ')
insert into T_Sales values( '19寸宽屏彩电 ',2400, '2007-06-15 ')
insert into T_Sales values( '21寸宽屏彩电 ',2500, '2007-07-05 ')
insert into T_Sales values( '29寸宽屏彩电 ',2600, '2007-07-15 ')
insert into T_Sales values( '小鸭冰箱 ',600, '2007-08-05 ')
insert into T_Sales values( '中鸭冰箱 ',800, '2007-08-15 ')
insert into T_Sales values( '大鸭冰箱 ',1000, '2007-09-05 ')
insert into T_Sales values( '瘦鸭冰箱 ',900, '2007-10-05 ')
go
declare @sql varchar(8000)
set @sql = 'select F_GoodName'
select @sql = @sql + ' , sum(case datename(day,f_saledate) when ''' + f_saledate + ''' then F_Price else 0 end) [' + f_saledate + '号]'
from (select distinct datename(day,f_saledate) f_saledate from T_Sales) as a
set @sql = @sql + ' from T_Sales group by F_GoodName'
exec(@sql) drop table t_sales/*
F_GoodName 10号 12号 13号 15号 25号 5号
-------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
14寸彩电 1000.0000 .0000 .0000 1000.0000 1000.0000 1000.0000
14寸宽屏彩电 .0000 .0000 .0000 .0000 2200.0000 .0000
17寸彩电 .0000 1100.0000 1100.0000 1100.0000 1100.0000 .0000
17寸宽屏彩电 .0000 .0000 .0000 .0000 .0000 2300.0000
19寸宽屏彩电 .0000 .0000 .0000 2400.0000 .0000 .0000
21寸彩电 .0000 .0000 .0000 .0000 1200.0000 .0000
21寸宽屏彩电 .0000 .0000 .0000 .0000 .0000 2500.0000
29寸彩电 .0000 .0000 .0000 .0000 .0000 1300.0000
29寸宽屏彩电 .0000 .0000 .0000 2600.0000 .0000 .0000
大鸭冰箱 .0000 .0000 .0000 .0000 .0000 1000.0000
瘦鸭冰箱 .0000 .0000 .0000 .0000 .0000 900.0000
小鸭冰箱 .0000 .0000 .0000 .0000 .0000 600.0000
中鸭冰箱 .0000 .0000 .0000 800.0000 .0000 .0000
*/
--你说的是什么意思啊
Create table T_Sales(
F_ID int identity(1,1),
F_GoodName varchar(20),
F_Price money,
F_SaleDate smalldatetime)
GO
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-05 ')
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-15 ')
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-25 ')
insert into T_Sales values( '14寸彩电 ',1000, '2007-04-10 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-15 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-25 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-12 ')
insert into T_Sales values( '17寸彩电 ',1100, '2007-04-13 ')
insert into T_Sales values( '21寸彩电 ',1200, '2007-04-25 ')
insert into T_Sales values( '29寸彩电 ',1300, '2007-05-05 ')
insert into T_Sales values( '14寸宽屏彩电 ',2200, '2007-05-25 ')
insert into T_Sales values( '17寸宽屏彩电 ',2300, '2007-06-05 ')
insert into T_Sales values( '19寸宽屏彩电 ',2400, '2007-06-15 ')
insert into T_Sales values( '21寸宽屏彩电 ',2500, '2007-07-05 ')
insert into T_Sales values( '29寸宽屏彩电 ',2600, '2007-07-15 ')
insert into T_Sales values( '小鸭冰箱 ',600, '2007-08-05 ')
insert into T_Sales values( '中鸭冰箱 ',800, '2007-08-15 ')
insert into T_Sales values( '大鸭冰箱 ',1000, '2007-09-05 ')
insert into T_Sales values( '瘦鸭冰箱 ',900, '2007-10-05 ')
-- select * from T_Sales declare @SQL varchar(8000)
set @SQL= 'select distinct F_GoodName '
select @SQL=@SQL+ ',sum(case F_GoodName when '''+F_GoodName+ ''' then F_Price end )as [ '+substring(convert(varchar(10),F_SaleDate,120),9,2)+ '] '
from (select distinct F_GoodName,F_SaleDate from T_Sales) a
print @SQL
exec(@SQL+ ' from T_Sales group by F_GoodName,F_SaleDate ')