㈠ plsql pivot怎么使用
"pivot table" or a "crosstab report"
(Note: this page needs to be wikified)
SQL Characteristic Functions: Do it without "if", "case", or "GROUP_CONCAT".
Yes, there is use for this..."if" statements sometimes cause problems
when used in combination.
The simple secret, and it's also why they work in almost all databases, is the
following functions:
o sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively
o abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0
o 1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0
Quick example: sign(-1) = -1, abs( sign(-1) ) = 1, 1-abs( sign(-1) ) = 0
Data for full example:
CREATE TABLE exams (
pkey int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY (pkey)
);
insert into exams (name,exam,score) values ('Bob',1,75);
insert into exams (name,exam,score) values ('Bob',2,77);
insert into exams (name,exam,score) values ('Bob',3,78);
insert into exams (name,exam,score) values ('Bob',4,80);
insert into exams (name,exam,score) values ('Sue',1,90);
insert into exams (name,exam,score) values ('Sue',2,97);
insert into exams (name,exam,score) values ('Sue',3,98);
insert into exams (name,exam,score) values ('Sue',4,99);
mysql> select * from exams;
㈡ sql动态多行转列，PIVOT怎么能转两列
楼上那个用过sqlserver吗？
PIVOT是支持的！！！！！
不过看你的语法，肯定报错。PIVOT最好基于SELECT * 并且先把全部需求的字段转换未列：
select * from
(select CONVERT(varchar(100),年月)+'受理' 栏位,sum(受理数量) 数量 from
表a
GROUP BY 年月
UNION ALL
select CONVERT(varchar(100),年月)+'办结'栏位,sum(办结数量) 数量 from
表a
GROUP BY 年月)v
PIVOT ( MAX(v.数量) FOR v.栏位 IN ( [2019-01受理],
[2019-02受理],
[2019-03受理],
[2019-04受理],
[2019-01办结],
[2019-02办结],
[2019-03办结],
[2019-04办结] )) 网络知道回答用
㈢ SQL中对临时表如何用 PIVOT 进行列转换
1、跟是否临时表没有关系。
2、SELECT列表中的转出字段，也要加上[]
3、加上DQ字段
SELECT DQ
,[42周]
,[43周]
,[44周]
,[45周]
,[46周]
,[47周]
FROM #TEMPDBF
PIVOT(SUM(TEU)FOR
周IN([42周],[43周],[44周],[45周],[46周],[47周])
)b
㈣ sql语句在使用pivot出现的错误提示
select * from #aa a pivot (max(qr_type) for xmname in ('+@sql+') ) as b
修改为
SET @sql = 'select * from #aa a pivot (max(qr_type) for xmname in (' + @sql + ') ) as b';
EXECUTE IMMEDIATE (@sql);