冰天雪地跪求 SQL行转列写法
已有表结构和数据如下：
IDLineIDLineNameClassDateSaleInfo
11000线路12012-07-0945/40/25
21000线路12012-07-1040/40/0
31000线路12012-07-1145/2/0
41001线路22012-07-0950/50/48
51001线路22012-07-1055/30/0
61001线路22012-07-1150/5/0
需要查询展现的结果如下：
LineIDLineName 07/09 07/10 07/10
1000线路145/40/25 40/40/0 45/2/0
1001线路250/50/48 55/30/0 50/5/0
需求如下：
1：查询结果中的07/09，07/10，07/10为ClassDate的月份/日期，并且为动态，不固定。如：查询一个月的数据，那就是 07/01 至 07/31
2：在SQL 2008环境下，可用存储过程，临时表。
小弟才疏学浅，研究过SQL的 PIVOT 关键字，但始终不能达到效果，望各路神仙、高人不啬赐教。
拜谢~！
SQL数据脚本如下：
CREATE TABLE Test ( ID INT IDENTITY , LineID INT , LineName VARCHAR(20) , ClassDate DATETIME , SaleInfo VARCHAR(50) ) goINSERT INTO dbo.Test ( LineID , LineName , ClassDate , SaleInfo ) SELECT 1000 , -- LineID - int '线路1' , -- LineName - varchar(20) '2012-07-09' , -- ClassDate - datetime '45/40/25' -- SaleInfo - varchar(50) UNION ALL SELECT 1000 , -- LineID - int '线路1' , -- LineName - varchar(20) '2012-07-10' , -- ClassDate - datetime '40/40/0' -- SaleInfo - varchar(50) UNION ALL SELECT 1000 , -- LineID - int '线路1' , -- LineName - varchar(20) '2012-07-11' , -- ClassDate - datetime '45/2/0' -- SaleInfo - varchar(50) UNION ALL SELECT 1001 , -- LineID - int '线路2' , -- LineName - varchar(20) '2012-07-09' , -- ClassDate - datetime '50/50/48' -- SaleInfo - varchar(50) UNION ALL SELECT 1001 , -- LineID - int '线路2' , -- LineName - varchar(20) '2012-07-10' , -- ClassDate - datetime '55/30/0' -- SaleInfo - varchar(50) UNION ALL SELECT 1001 , -- LineID - int '线路2' , -- LineName - varchar(20) '2012-07-11' , -- ClassDate - datetime '50/5/0'
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2012-07-09 11:17:47-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([ID] int,[LineID] int,[LineName] varchar(5),[ClassDate] datetime,[SaleInfo] varchar(8))insert [tb]select 1,1000,'线路1','2012-07-09','45/40/25' union allselect 2,1000,'线路1','2012-07-10','40/40/0' union allselect 3,1000,'线路1','2012-07-11','45/2/0' union allselect 4,1001,'线路2','2012-07-09','50/50/48' union allselect 5,1001,'线路2','2012-07-10','55/30/0' union allselect 6,1001,'线路2','2012-07-11','50/5/0'--------------开始查询--------------------------declare @sql varchar(8000)set @sql = 'select LineID ,LineName'select @sql = @sql + ' , max(case convert(varchar(10),ClassDate,120) when ''' + convert(varchar(10),ClassDate,120) + ''' then SaleInfo else '''' end) [' + convert(varchar(10),ClassDate,120) + ']'from (select distinct convert(varchar(10),ClassDate,120) as ClassDate from tb) as aset @sql = @sql + ' from tb group by LineID,LineName'exec(@sql) ----------------结果----------------------------/* LineID LineName 2012-07-09 2012-07-10 2012-07-11----------- -------- ---------- ---------- ----------1000 线路1 45/40/25 40/40/0 45/2/01001 线路2 50/50/48 55/30/0 50/5/0(2 行受影响)*/