27,579
社区成员
发帖
与我相关
我的任务
分享
create table kt
(month varchar(20),isvs varchar(20),dep varchar(20),times varchar(20),qty varchar(20),ngqty varchar(20), ngl varchar(20))
insert into kt
select 'May-16', '內部重工', 'AB', ' 54.5', '48730', '526', '0.0107' union all
select 'May-16', '內部重工', '電木 ', '231.5', '210260', '0', '0' union all
select 'May-16', '加工全檢 ', 'C', '50', '9503', '173', '0.0179'
select * from kt
/*
month isvs dep times qty ngqty ngl
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
May-16 內部重工 AB 54.5 48730 526 0.0107
May-16 內部重工 電木 231.5 210260 0 0
May-16 加工全檢 C 50 9503 173 0.0179
(3 row(s) affected)
*/
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')+'['+rtrim(number)+']'
from master.dbo.spt_values
where type='P'
and number between 1 and (select count(1) from kt)
select @tsql='with v as(
select rn,col,val,case col when ''month'' then 1 when ''isvs'' then 2 when ''dep'' then 3
when ''times'' then 4 when ''qty'' then 5 when ''ngqty'' then 6
when ''ngl'' then 7 else 8 end ''coo''
from (select row_number() over(order by getdate()) ''rn'',* from kt) t
unpivot(val for col in(month,isvs,dep,times,qty,ngqty,ngl)) u)
select cast(col as varchar(20)) ''col'','+@tsql
+' from v
pivot(max(val) for rn in('+@tsql+')) p
order by coo '
exec(@tsql)
/*
col 1 2 3
-------------------- -------------------- -------------------- --------------------
month May-16 May-16 May-16
isvs 內部重工 內部重工 加工全檢
dep AB 電木 C
times 54.5 231.5 50
qty 48730 210260 9503
ngqty 526 0 173
ngl 0.0107 0 0.0179
(7 row(s) affected)
*/