怎么样使纵向表变横向表的存储过程？
怎么写存储过程？？？
数据结构：
日期 商品名称 购买数量 使用数量
2013-6-1 AAA 1500 500
2013-6-1 BBB 5000 800
2013-6-2 AAA 7500 580
2013-6-3 AAA 3500 150
2013-6-4 BBB 1500 600
2013-6-4 AAA 6300 222
…………
该怎么改写语句呢？？？？
6-1 6-2 6-3 6-4 6-5 6-6 6-7 6-8 6-9 6-10 …………
AAA 购买数量 1500 7500 3500 6300
AAA 使用数量 500 580 150 0
bbb 购买数量 5000 0 0 1500
bbb 使用数量 5000 800 0 600
如此，而且每天要查询。
今天20号的，就要查询到19号的，明天21号，就要查询到20号的。。。
求各位大神，帮忙想下办法，这样让查询速度快。怎么写语句让纵向变成横向？？？ 存储 数据结构
[解决办法]
----------------------------
-- Author :TravyLee(两情若是久长时，又岂在朝朝暮暮！)
-- Date :2013-06-25 14:20:14
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
--Jul 9 2008 14:17:44
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：#test
if object_id('tempdb.dbo.#test') is not null
drop table #test
go
create table #test
(
[日期] datetime,
[商品名称] varchar(3),
[购买数量] int,
[使用数量] int
)
insert #test
select '2013-6-1','AAA',1500,500 union all
select '2013-6-1','BBB',5000,800 union all
select '2013-6-2','AAA',7500,580 union all
select '2013-6-3','AAA',3500,150 union all
select '2013-6-4','BBB',1500,600 union all
select '2013-6-4','AAA',6300,222
go
select
[商品名称],
'购买数量' as [状态],
sum(case when [日期]='2013-6-1' then [购买数量] else 0 end) as '6-1',
sum(case when [日期]='2013-6-2' then [购买数量] else 0 end) as '6-2',
sum(case when [日期]='2013-6-3' then [购买数量] else 0 end) as '6-3',
sum(case when [日期]='2013-6-4' then [购买数量] else 0 end) as '6-4'
......
from
#test
group by
[商品名称]
union all
select
[商品名称],
'使用数量' as [状态],
sum(case when [日期]='2013-6-1' then [使用数量] else 0 end) as '6-1',
sum(case when [日期]='2013-6-2' then [使用数量] else 0 end) as '6-2',
sum(case when [日期]='2013-6-3' then [使用数量] else 0 end) as '6-3',
sum(case when [日期]='2013-6-4' then [使用数量] else 0 end) as '6-4'
......
from
#test
group by
[商品名称]
order by
[商品名称]
--**************************************
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GoodsName] [nvarchar](50) NULL,
[BuyCount] [int] NULL,
[useCount] [int] NULL,
[TestDate] [nvarchar](50) NULL
) ON [PRIMARY]
insert into [Test] values('AAA',1500,500,'2013-6-1')
insert into [Test] values('BBB',5000,800,'2013-6-1')
insert into [Test] values('AAA',7500,580,'2013-6-2')
insert into [Test] values('AAA',3500,150,'2013-6-3')
insert into [Test] values('BBB',1500,600,'2013-6-4')
insert into [Test] values('AAA',6300,222,'2013-6-5')
Declare @SqlStr nvarchar(max)
Set @SqlStr='select GoodsName '
select @SqlStr+=',max(case TestDate when '''+TestDate+''' then BuyCount else 0 end) as '''+TestDate+'''
,max(case TestDate when '''+TestDate+''' then useCount else 0 end) as '''+TestDate+'''
'
from (select distinct TestDate from Test) a
set @SqlStr+=' from [Test] group by GoodsName'
select @SqlStr
exec(@SqlStr)
