㈠ 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);