iT邦幫忙

0

SQL 資料合併的問題

各位老手,請問我有一個表格,想要讓他條件一樣的合併,不知道要如何做到呢
SQL Server 2019

https://ithelp.ithome.com.tw/upload/images/20210618/20122928Xudn4dH73x.jpg
ID欄,要把後面的多餘的字元去掉,然後再
ID跟NO跟DIA條件相同讓他合併再一起
https://ithelp.ithome.com.tw/upload/images/20210618/20122928LdHpA9tSHI.jpg

最終 YN欄 有N的就顯示N沒有N就顯示Y像這樣
https://ithelp.ithome.com.tw/upload/images/20210618/201229283tk2jQw3pj.jpg

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

0
rogeryao
iT邦超人 8 級 ‧ 2021-06-19 08:51:48
最佳解答
CREATE TABLE TableX
(ID varchar(20),
NO varchar(20),
DIA varchar(20),
YN varchar(20)
);

INSERT INTO TableX 
VALUES
('3201-20210103001','1','A','y'),
('3201-20210103001','2','A','y'),
('3201-20210103001_002','1','A','n'),
('3201-20210103001_001','2','B','n'),
('3201-210104001','1','A','n'),
('3201-210104001-ati','1','A','n'),
('3201-210104001_01','1','A','n'),
('3201-210104001_02','2','B','n');
SELECT ID,NO,DIA,
CASE WHEN SUM(CASE WHEN YN='y' THEN 0 ELSE 1 END)=0 THEN 'y' ELSE 'n' END AS YN
FROM (
SELECT 
d.vals.value('(/TR/TD)[1]','VARCHAR(20)') +'-'+
d.vals.value('(/TR/TD)[2]','VARCHAR(20)') 'ID',
NO,DIA,YN
FROM (
SELECT CAST('<TR><TD>' + REPLACE(REPLACE(ID,'_','-'), '-', '</TD><TD>') + '</TD></TR>' AS xml) vals,
NO,DIA,YN
FROM TableX) AS d
) AS M
GROUP BY ID,NO,DIA

Demo

我要發表回答

立即登入回答