iT邦幫忙

1

MySQL 橫向轉換成直向

我想要把原有TABLE的資料橫向轉直向

資料類似下方這樣的格式

<原始資料>

產品 文件階段 文件編號
A    DR1      111
A    DR2      222
B    DR1      456
B    DR2      789 
C    DR2      888
D    DR1      999

<需求結果>

產品 DR1文件編號 DR2文件編號
A    111        222
B    456        789
C               888
D    999

產品只會有兩種文件階段(DR1、DR2)
但不一定都會有編號

請問該如何下SQL才可以得到需求結果的樣貌

再請各位指教

謝謝

7
暐翰
iT邦大師 1 級 ‧ 2019-08-30 11:59:00
最佳解答
CREATE TABLE T
	([產品] varchar(1), [文件階段] varchar(3), [文件編號] int)
;
	
INSERT INTO T
	([產品], [文件階段], [文件編號])
VALUES
	('A', 'DR1', 111),
	('A', 'DR2', 222),
	('B', 'DR1', 456),
	('B', 'DR2', 789),
	('C', 'DR2', 888),
	('D', 'DR1', 999)
;

查詢 :

select 
    產品,
    max(case when [文件階段] = 'DR1' then [文件編號] end) as [DR1文件階段],
    max(case when [文件階段] = 'DR2' then [文件編號] end) as [DR2文件階段]
from T
group by 產品

線上測試連結 : here


假如要做動態版本,可以參考我之前在S.O的回答
sql server - Splitting dynamically SQL columns into multiple columns based on a different column value - Stack Overflow

看更多先前的回應...收起先前的回應...
Neish iT邦研究生 3 級 ‧ 2019-08-30 13:23:11 檢舉

感謝暐翰! 方法簡潔明瞭且查詢效能好!
但我不懂為什麼還要加上max?

暐翰 iT邦大師 1 級 ‧ 2019-08-30 13:34:17 檢舉

Neish
因為受限group語法,需要使用min or max
取得分組的第一筆文件編號

小魚 iT邦高手 1 級 ‧ 2019-08-30 13:37:12 檢舉

如果是MySql有可能不用MAX,
因為MySql的GROUP BY預設會取第一筆,
像這個例子應該是只會有一筆,
所以沒差.

MS-SQL就一定要了,
否則會回傳錯誤.

Neish iT邦研究生 3 級 ‧ 2019-08-30 13:39:16 檢舉

我是用MySQL
難怪我想說去除MAX結果還是有跑出來
感謝兩位協助!

不對欸! 我重新確認還是要加MAX
不然只會抓到DR1的編號

1
小魚
iT邦高手 1 級 ‧ 2019-08-30 11:08:08

可以用子查詢
現在沒時間測試,
你試試看

SELECT 產品,
(SELECT IFNULL(文件編號, '') FROM TABLE WHERE 文件階段 = 'DR1' AND 產品 = a.產品) AS DR1文件編號,
(SELECT IFNULL(文件編號, '') FROM TABLE WHERE 文件階段 = 'DR2' AND 產品 = a.產品) AS DR2文件編號
FROM TABLE AS a
GROUP BY 產品
Neish iT邦研究生 3 級 ‧ 2019-08-30 13:21:46 檢舉

感謝小魚! SQL可行! 但效能差了點 最佳解我就選暐翰了

小魚 iT邦高手 1 級 ‧ 2019-08-30 13:35:07 檢舉

我也覺得他的方法比較好.

1
ckp6250
iT邦新手 4 級 ‧ 2019-08-30 11:30:54

這類資料橫向轉直向的需求,
我覺得最好是寫成 prodedure
然後利用類似

SET @query="............";
PREPARE QUERY FROM @query;
EXECUTE QUERY;

的動態方式去組合 @query 處理較妥,
否則,目前只有兩種文件階段(DR1、DR2)當然好處理,
但以後若增加了 DR3,DR4,DR5.......
又若者根本不知道會有幾項時,
您要怎麼辦?

Neish iT邦研究生 3 級 ‧ 2019-08-30 13:21:11 檢舉

感謝ckp6250提醒! 動態方法再來Study一下

我要發表回答

立即登入回答