piarno qty dozqty stone item item8 workflow worktime
PIAR006644 720 60 7320 B602655ZK0AA-001 B602655Z A01 0.2571
PIAR006644 720 60 7320 B602655ZK0AA-001 B602655Z A02 0.2399
PIAR006645 1320 110 7320 B602655ZK0AA-002 B602655Z I02 0.1714
PIAR006645 1320 110 7320 B602655ZK0AA-002 B602655Z I03 0.0428
PIAR006645 1320 110 7320 B602655ZP000 B602655Z A01 0.2571
PIAR006645 1320 110 7320 B602655ZP000 B602655Z A02 0.2399
PIAR006645 1320 110 7320 B602655ZP000 B602655Z B01 0.2828
PIAR006645 1320 110 7320 KC101360K0AA KC101360 A01 0.1714
PIAR006645 1320 110 7320 KC101360K0AA KC101360 A02 0.2828
PIAR006645 1320 110 7320 KC101360K0AA KC101360 A02 0.1885
PIAR006645 1320 110 7320 KC101360K0AA KC101360 A02 0.1885
PIAR006645 1320 110 7320 B602655ZC111 B602655Z G01 1.1311
PIAR006645 1320 110 7320 B602655ZC111 B602655Z I01 0.0857
PIAR006645 1320 110 7320 B602655ZC111 B602655Z I02 0.1714
PIAR006645 1320 110 7320 B602655ZC111 B602655Z I03 0.0428
PIAR006645 1320 110 7320 KC101340C0AA-001 KC101340 A01 0.3428
PIAR006645 1320 110 7320 KC101340C0AA-002 KC101340 G01 0.6855
PIAR006645 1320 110 7320 KC101340C0AA-002 KC101340 G02 0.5141 现在我要得到的结果如下:
piarno qty dozqty kuanshu stone A01 A02 B01
PIAR006644 720 60 1 7320 0.2571 0.2399 0
PIAR006645 1320 110 3 7320 0.7713 0.8997 0.2828
kuanshu 是指同一个piarno下 item8相同的数量,A01是指同piarno下A01的worktime和,A02是指同piarno下A02的worktime和,B01是指同piarno下B01的worktime和
PIAR006644 720 60 7320 B602655ZK0AA-001 B602655Z A01 0.2571
PIAR006644 720 60 7320 B602655ZK0AA-001 B602655Z A02 0.2399
PIAR006645 1320 110 7320 B602655ZK0AA-002 B602655Z I02 0.1714
PIAR006645 1320 110 7320 B602655ZK0AA-002 B602655Z I03 0.0428
PIAR006645 1320 110 7320 B602655ZP000 B602655Z A01 0.2571
PIAR006645 1320 110 7320 B602655ZP000 B602655Z A02 0.2399
PIAR006645 1320 110 7320 B602655ZP000 B602655Z B01 0.2828
PIAR006645 1320 110 7320 KC101360K0AA KC101360 A01 0.1714
PIAR006645 1320 110 7320 KC101360K0AA KC101360 A02 0.2828
PIAR006645 1320 110 7320 KC101360K0AA KC101360 A02 0.1885
PIAR006645 1320 110 7320 KC101360K0AA KC101360 A02 0.1885
PIAR006645 1320 110 7320 B602655ZC111 B602655Z G01 1.1311
PIAR006645 1320 110 7320 B602655ZC111 B602655Z I01 0.0857
PIAR006645 1320 110 7320 B602655ZC111 B602655Z I02 0.1714
PIAR006645 1320 110 7320 B602655ZC111 B602655Z I03 0.0428
PIAR006645 1320 110 7320 KC101340C0AA-001 KC101340 A01 0.3428
PIAR006645 1320 110 7320 KC101340C0AA-002 KC101340 G01 0.6855
PIAR006645 1320 110 7320 KC101340C0AA-002 KC101340 G02 0.5141 现在我要得到的结果如下:
piarno qty dozqty kuanshu stone A01 A02 B01
PIAR006644 720 60 1 7320 0.2571 0.2399 0
PIAR006645 1320 110 3 7320 0.7713 0.8997 0.2828
kuanshu 是指同一个piarno下 item8相同的数量,A01是指同piarno下A01的worktime和,A02是指同piarno下A02的worktime和,B01是指同piarno下B01的worktime和
set @sql=''
select @sql=@sql+',sum(case workflow when '''+workflow+''' then worktime else 0 end) as ['+workflow+']'
from tab
group by workflow
order by workflowexec('select piarno,qty,dozqty,count(distinct Item8) as kuanshu,stone'+@sql+' from tab group by piarno,qty,dozqty,stone')
qty,
dozqty,
kuanshu = count(1),
stone,
A01 = sum(case piarno when 'A01' then worktime else 0 end),
A02 = sum(case piarno when 'A02' then worktime else 0 end),
B01 = sum(case piarno when 'B01' then worktime else 0 end)
group by piarno,
qty,
dozqty,
stone
qty,
dozqty,
kuanshu = count(distinct Item8),
stone,
A01 = sum(case piarno when 'A01' then worktime else 0 end),
A02 = sum(case piarno when 'A02' then worktime else 0 end),
B01 = sum(case piarno when 'B01' then worktime else 0 end)
group by piarno,
qty,
dozqty,
stone
qty,
dozqty,
kuanshu = count(distinct Item8),
stone,
A01 = sum(case workflow when 'A01' then worktime else 0 end),
A02 = sum(case workflow when 'A02' then worktime else 0 end),
B01 = sum(case workflow when 'B01' then worktime else 0 end)
group by piarno,
qty,
dozqty,
stone
---行列
create table A(id char(3), num1 int, num2 int, num3 int, num4 int)
insert A select '001', 80, 90, 50, 60
insert A select '002', 84, 70, 60, 82go
--SQL2005实现方法:
select
*
from
A
unpivot
(num for col in([num1],[num2],[num3],[num4]))T2
)tmp--SQL2000实现:---调用系统表动态生态
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name,'''')+',Qty='+quotename(Name)+' from A'
from syscolumns
where Name!=N'ID' and ID=object_id('A')--表名A,不包含列名为ID的其它列
order by colid asc
exec(@s+' order by ID asc,[num] asc')--生成的静态语句
select ID,[num]='num1',Qty=[num1] from A union all
select ID,[num]='num2',Qty=[num2] from A union all
select ID,[num]='num3',Qty=[num3] from A union all
select ID,[num]='num4',Qty=[num4] from A
order by ID asc,[num] asc/*ID num Qty
---- ---- -----------
001 num1 80
001 num2 90
001 num3 50
001 num4 60
002 num1 84
002 num2 70
002 num3 60
002 num4 82
------------------------------
*/
--动态方法:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name)+' from A'
from syscolumns
where Name!=N'ID' and ID=object_id('A')
order by colid asc
exec(@s+' order by ID asc')--生成的语句如下:select ID,[num]=[num1] from A union all
select ID,[num]=[num2] from A union all
select ID,[num]=[num3] from A union all
select ID,[num]=[num4] from A
order by ID asc,[num] asc/*
ID num
---- -----------
001 80
001 90
001 50
001 60
002 82
002 60
002 70
002 84
----------------------------05行列转换
--pivot,UNPIVOT,APPLY的应用declare @t table(name varchar(10),question varchar(10),answer varchar(10))
insert into @t select '小李','问题','答案'
insert into @t select '小李','问题','答案'
insert into @t select '小李','问题','答案'--注版权:charry0110
--PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,
--并在必要时对最终输出中所需的任何其余列值执行聚合.
--UNPIVOT 与PIVOT 执行相反的操作,将表值表达式的列转换为列值.1--一列转多列,多行转一行
select name,问题,问题,问题from @t
pivot
(max(answer) For question in --//可以使用相应的聚合函数
([问题],[问题],[问题])
) as pvt--result
--name 问题 问题 问题
------------ ---------- ---------- ----------
--小李 答案 答案 答案2--多列转一列,一行转多行
SELECT name,question,answer
FROM
(SELECT name,问题,问题,问题
FROM (
--一列转多列,多行转一行
select name,问题,问题,问题from @t
pivot
(max(answer) For question in
--count(answer),sum(answer)
([问题],[问题],[问题])
) as pvt) t
--一列转多列,多行转一行
) p
UNPIVOT
(answer FOR question IN
(问题,问题,问题)
)AS unpvt--result
--小李 问题 答案
--小李 问题 答案
--小李 问题 答案3--多行转一行
select replace ((SELECT question FROM @t N FOR XML AUTO),'<N question="',',')--result
--,问题"/>,问题"/>,问题"/>select REPLACE(replace ((SELECT question FROM @t N FOR XML AUTO),'<N question="',','),'"/>', '')--result
--,问题,问题,问题-- 查询处理
SELECT * FROM(SELECT DISTINCT name FROM @t)A
OUTER APPLY(SELECT
[values]= STUFF(REPLACE(REPLACE((SELECT question FROM @t N WHERE name = A.name FOR XML AUTO)
,'<N question="',','),'"/>', ''),1, 1, ''))N--result
--小李 问题,问题,问题