22,209
社区成员
发帖
与我相关
我的任务
分享SQL 行转列
表结构
PartNum ForeDate ForeQty
A001 2014--09-19 1000
A001 2014--09-20 2000
A001 2014--09-21 1500
A001 2014--09-22 1000
A001 2014--09-23 1000
A001 2014--09-24 1000
B001 2014--09-19 1000
B001 2014--09-22 1000
B001 2014--09-25 1000
B001 2014--09-27 1000
我想变成这样
料号 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 100 1000 1000 1000
日期不固定
这个SQL语句要怎么写，我在网上找了些但写出来语法有错误
declare @sql as varchar(50)
set @sql=''
select @sql=@sql+','+CONVERT(char(12),ForeDate,3) from [epicor905].dbo.Forecast Group by ForeDate
set @sql=stuff(@sql,1,1,'')
set @sql='select * from (select PartNum,ForeDate,ForeQty from Forecast) a
pivot(max(foreQty) for ForeDate in ('+@sql+')) b'
exec(@sql)
訊息 102，層級 15，狀態 1，行 1
接近 'fro' 之處的語法不正確。
PartNum ForeDate ForeQty
A001 2014--09-19 1000
A001 2014--09-20 2000
A001 2014--09-21 1500
A001 2014--09-22 1000
A001 2014--09-23 1000
A001 2014--09-24 1000
B001 2014--09-19 1000
B001 2014--09-22 1000
B001 2014--09-25 1000
B001 2014--09-27 1000
我想变成这样
料号 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 100 1000 1000 1000
日期不固定
这个SQL语句要怎么写，我在网上找了些但写出来语法有错误
declare @sql as varchar(50)
set @sql=''
select @sql=@sql+','+CONVERT(char(12),ForeDate,3) from [epicor905].dbo.Forecast Group by ForeDate
set @sql=stuff(@sql,1,1,'')
set @sql='select * from (select PartNum,ForeDate,ForeQty from Forecast) a
pivot(max(foreQty) for ForeDate in ('+@sql+')) b'
exec(@sql)
訊息 102，層級 15，狀態 1，行 1
接近 'fro' 之處的語法不正確。
...全文
请发表友善的回复…
发表回复
zlp321002 2014-09-22
- 打赏
- 举报
--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
')
frankl123 2014-09-22
- 打赏
- 举报
哥们你日期有问题啊年月中间是两个"-"
wtujedp 2014-09-20
- 打赏
- 举报
你给你的测试数据里面，日期格式里，年后面是两个“-”估计是这个原因吧
Liyp92 2014-09-20
- 打赏
- 举报
弄错了。
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)
Liyp92 2014-09-20
- 打赏
- 举报
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
SELECT *,ROW_NUMBER() OVER(PARTITION BY PartNum ORDER BY PartNum) ID INTO #T1 FROM T
DECLARE @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)alexlee08 2014-09-20
- 打赏
- 举报
用了一樓及二樓的方法，但都出現
訊息 241，層級 16，狀態 1，行 2
從字元字串轉換成日期及/或時間時，轉換失敗。
唐诗三百首 2014-09-19
- 打赏
- 举报
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) t
select @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) t
select @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 行受影响)
*/
wtujedp 2014-09-19
- 打赏
- 举报
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)
