create table t1 ( partnum varchar(20), poredate varchar(20), foreqty int ) insert into t1 values('A001','2014--09-19',1000) insert into t1 values('A001','2014--09-20',2000) insert into t1 values('A001','2014--09-21',1500) insert into t1 values('A001','2014--09-22',1000) insert into t1 values('A001','2014--09-23',1000) insert into t1 values('A001','2014--09-24',1000) insert into t1 values('B001','2014--09-19',1000) insert into t1 values('B001','2014--09-22',1000) insert into t1 values('B001','2014--09-25',1000) insert into t1 values('B001','2014--09-27',1000) insert into t1 values('B001','2014--09-28',1000) declare @poredate varchar(2000) declare @sql varchar(2000) set @poredate=''; set @sql=''; select @poredate=@poredate+',['+poredate+']' from t1 group by poredate set @poredate=STUFF(@poredate,1,1,'') set @sql='select * from t1 pivot (max(foreqty) for poredate in('+@poredate+') )t2' --print @poredate exec (@sql)
create table ale(PartNum varchar(10),ForeDate varchar(20),ForeQty int)insert into ale select 'A001','2014--09-19',1000 union all select 'A001','2014--09-20',2000 union all select 'A001','2014--09-21',1500 union all select 'A001','2014--09-22',1000 union all select 'A001','2014--09-23',1000 union all select 'A001','2014--09-24',1000 union all select 'B001','2014--09-19',1000 union all select 'B001','2014--09-22',1000 union all select 'B001','2014--09-25',1000 union all select 'B001','2014--09-27',1000 -- 方法1 declare @tsql varchar(6000),@list varchar(6000)select @tsql=isnull(@tsql+',','')+'isnull(rtrim(['+ForeDate+']),'''') '''+ForeDate+''' ', @list=isnull(@list+',','')+'['+ForeDate+']' from (select distinct ForeDate from ale) tselect @tsql='select PartNum,'+@tsql +' from ale ' +' pivot(max(ForeQty) for ForeDate in('+@list+')) p 'exec(@tsql)/* PartNum 2014--09-19 2014--09-20 2014--09-21 2014--09-22 2014--09-23 2014--09-24 2014--09-25 2014--09-27 ---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ A001 1000 2000 1500 1000 1000 1000 B001 1000 1000 1000 1000(2 行受影响) */ -- 方法2 declare @tsql varchar(6000)select @tsql=isnull(@tsql+',','') +'max(case when ForeDate='''+ForeDate+''' then rtrim(ForeQty) else '''' end) '''+ForeDate+''' ' from (select distinct ForeDate from ale) tselect @tsql='select PartNum,'+@tsql +' from ale ' +' group by PartNum 'exec(@tsql)/* PartNum 2014--09-19 2014--09-20 2014--09-21 2014--09-22 2014--09-23 2014--09-24 2014--09-25 2014--09-27 ---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ A001 1000 2000 1500 1000 1000 1000 B001 1000 1000 1000 1000(2 行受影响) */
insert into T select 'A001','2014-09-19',1000 union all select 'A001','2014-09-20',2000 union all select 'A001','2014-09-21',1500 union all select 'A001','2014-09-22',1000 union all select 'A001','2014-09-23',1000 union all select 'A001','2014-09-24',1000 union all select 'B001','2014-09-19',1000 union all select 'B001','2014-09-22',1000 union all select 'B001','2014-09-25',1000 union all select 'B001','2014-09-27',1000
SELECT *,ROW_NUMBER() OVER(PARTITION BY PartNum ORDER BY PartNum) ID INTO #T1 FROM TDECLARE @SQL NVARCHAR(MAX) SET @SQL='SELECT PartNum' SELECT @SQL=@SQL+',MAX(CASE WHEN ID='+CONVERT(VARCHAR,ID)+' THEN ForeQty ELSE NULL END) ['+CONVERT(VARCHAR(10),ForeDate,120)+']' FROM #T1 GROUP BY ID,ForeDate ORDER BY ID SET @SQL=@SQL+' FROM #T1 GROUP BY PartNum' EXEC(@SQL)
insert into T select 'A001','2014-09-19',1000 union all select 'A001','2014-09-20',2000 union all select 'A001','2014-09-21',1500 union all select 'A001','2014-09-22',1000 union all select 'A001','2014-09-23',1000 union all select 'A001','2014-09-24',1000 union all select 'B001','2014-09-19',1000 union all select 'B001','2014-09-22',1000 union all select 'B001','2014-09-25',1000 union all select 'B001','2014-09-27',1000 DECLARE @SQL NVARCHAR(MAX) SET @SQL='SELECT PartNum' SELECT @SQL=@SQL+',MAX(CASE WHEN CONVERT(VARCHAR(10),ForeDate,120)='''+CONVERT(VARCHAR(10),ForeDate,120)+''' THEN ForeQty ELSE NULL END) ['+CONVERT(VARCHAR(10),ForeDate,120)+']' FROM T GROUP BY ForeDate ORDER BY ForeDate SET @SQL=@SQL+' FROM T GROUP BY PartNum' EXEC(@SQL)
--sql server 2005 plus declare @dt varchar(max) set @dt='' select @dt=@dt+',['+dt+']' from ( select distinct poredate as dt from t1 ) tb select @dt= stuff(@dt,1,1,'') exec(' select * from t1 pivot (sum(foreqty) for poredate in ('+@dt+')) as pt ')
(
partnum varchar(20),
poredate varchar(20),
foreqty int
) insert into t1 values('A001','2014--09-19',1000)
insert into t1 values('A001','2014--09-20',2000)
insert into t1 values('A001','2014--09-21',1500)
insert into t1 values('A001','2014--09-22',1000)
insert into t1 values('A001','2014--09-23',1000)
insert into t1 values('A001','2014--09-24',1000)
insert into t1 values('B001','2014--09-19',1000)
insert into t1 values('B001','2014--09-22',1000)
insert into t1 values('B001','2014--09-25',1000)
insert into t1 values('B001','2014--09-27',1000)
insert into t1 values('B001','2014--09-28',1000)
declare @poredate varchar(2000)
declare @sql varchar(2000)
set @poredate='';
set @sql='';
select @poredate=@poredate+',['+poredate+']' from t1 group by poredate
set @poredate=STUFF(@poredate,1,1,'')
set @sql='select * from t1 pivot (max(foreqty) for poredate in('+@poredate+') )t2'
--print @poredate
exec (@sql)
create table ale(PartNum varchar(10),ForeDate varchar(20),ForeQty int)insert into ale
select 'A001','2014--09-19',1000 union all
select 'A001','2014--09-20',2000 union all
select 'A001','2014--09-21',1500 union all
select 'A001','2014--09-22',1000 union all
select 'A001','2014--09-23',1000 union all
select 'A001','2014--09-24',1000 union all
select 'B001','2014--09-19',1000 union all
select 'B001','2014--09-22',1000 union all
select 'B001','2014--09-25',1000 union all
select 'B001','2014--09-27',1000
-- 方法1
declare @tsql varchar(6000),@list varchar(6000)select @tsql=isnull(@tsql+',','')+'isnull(rtrim(['+ForeDate+']),'''') '''+ForeDate+''' ',
@list=isnull(@list+',','')+'['+ForeDate+']'
from (select distinct ForeDate from ale) tselect @tsql='select PartNum,'+@tsql
+' from ale '
+' pivot(max(ForeQty) for ForeDate in('+@list+')) p 'exec(@tsql)/*
PartNum 2014--09-19 2014--09-20 2014--09-21 2014--09-22 2014--09-23 2014--09-24 2014--09-25 2014--09-27
---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
A001 1000 2000 1500 1000 1000 1000
B001 1000 1000 1000 1000(2 行受影响)
*/
-- 方法2
declare @tsql varchar(6000)select @tsql=isnull(@tsql+',','')
+'max(case when ForeDate='''+ForeDate+''' then rtrim(ForeQty) else '''' end) '''+ForeDate+''' '
from (select distinct ForeDate from ale) tselect @tsql='select PartNum,'+@tsql
+' from ale '
+' group by PartNum 'exec(@tsql)/*
PartNum 2014--09-19 2014--09-20 2014--09-21 2014--09-22 2014--09-23 2014--09-24 2014--09-25 2014--09-27
---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
A001 1000 2000 1500 1000 1000 1000
B001 1000 1000 1000 1000(2 行受影响)
*/
insert into T
select 'A001','2014-09-19',1000 union all
select 'A001','2014-09-20',2000 union all
select 'A001','2014-09-21',1500 union all
select 'A001','2014-09-22',1000 union all
select 'A001','2014-09-23',1000 union all
select 'A001','2014-09-24',1000 union all
select 'B001','2014-09-19',1000 union all
select 'B001','2014-09-22',1000 union all
select 'B001','2014-09-25',1000 union all
select 'B001','2014-09-27',1000
SELECT *,ROW_NUMBER() OVER(PARTITION BY PartNum ORDER BY PartNum) ID INTO #T1 FROM TDECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT PartNum'
SELECT @SQL=@SQL+',MAX(CASE WHEN ID='+CONVERT(VARCHAR,ID)+' THEN ForeQty ELSE NULL END) ['+CONVERT(VARCHAR(10),ForeDate,120)+']'
FROM #T1
GROUP BY ID,ForeDate
ORDER BY ID
SET @SQL=@SQL+' FROM #T1 GROUP BY PartNum'
EXEC(@SQL)
create table T(PartNum varchar(10),ForeDate Datetime,ForeQty int)
insert into T
select 'A001','2014-09-19',1000 union all
select 'A001','2014-09-20',2000 union all
select 'A001','2014-09-21',1500 union all
select 'A001','2014-09-22',1000 union all
select 'A001','2014-09-23',1000 union all
select 'A001','2014-09-24',1000 union all
select 'B001','2014-09-19',1000 union all
select 'B001','2014-09-22',1000 union all
select 'B001','2014-09-25',1000 union all
select 'B001','2014-09-27',1000
DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT PartNum'
SELECT @SQL=@SQL+',MAX(CASE WHEN CONVERT(VARCHAR(10),ForeDate,120)='''+CONVERT(VARCHAR(10),ForeDate,120)+''' THEN ForeQty ELSE NULL END) ['+CONVERT(VARCHAR(10),ForeDate,120)+']'
FROM T
GROUP BY ForeDate
ORDER BY ForeDate
SET @SQL=@SQL+' FROM T GROUP BY PartNum'
EXEC(@SQL)
--sql server 2005 plus
declare @dt varchar(max)
set @dt=''
select @dt=@dt+',['+dt+']' from (
select distinct poredate as dt from t1
) tb
select @dt= stuff(@dt,1,1,'') exec('
select * from t1
pivot
(sum(foreqty) for poredate in ('+@dt+')) as pt
')