請問SQL指令該如何寫?

sql

XYZ 2020-10-08 17:43:21 ‧ 1582 瀏覽

分享至

大家好,

如下表一的資料,我想轉換成下表三的結果,下表三的MODE2欄位資料是根據下表二的明細欄位代碼資料, 請問SQL要如何寫?
備註:表二沒寫入DB

表一:
TYPE MODE
R 1101111
Q 1111000
T 1110111

表二:

表三:
TYPE MODE2
R ABDEFG
Q HIJK
T PQRXYZ

純真的人 iT邦大師 1 級 ‧ 2020-10-08 17:52:47 檢舉

恩??對應怪怪的..不是這樣嗎?
TYPE MODE2
R ABDEFG
Q HIJK
T PQRXYZ

Zed_Yang iT邦新手 3 級 ‧ 2020-10-08 18:15:03 檢舉

你之前的發問都有被解決了嗎?

XYZ iT邦新手 4 級 ‧ 2020-10-12 09:50:19 檢舉

to 純真的人
謝謝您的糾正,已更新

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 7 級 ‧ 2020-10-08 22:30:42

假設表三誠如純真的人所述

CREATE TABLE [dbo].[TestY] (
[TYPE] varchar(1) NULL ,
[MODE] varchar(7) NULL );
--
INSERT INTO [dbo].[TestY] ([TYPE], [MODE]) VALUES (N'R', N'1101111');
INSERT INTO [dbo].[TestY] ([TYPE], [MODE]) VALUES (N'Q', N'1111000');
INSERT INTO [dbo].[TestY] ([TYPE], [MODE]) VALUES (N'T', N'1110111');

CREATE TABLE [dbo].[TestZ] (
[TYPE] varchar(1) NULL ,
[CODESTRING] varchar(10) NULL ,
[CODE] varchar(1) NULL );
--
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'1xx', N'A');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'2xx', N'B');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'3xx', N'C');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'4xx', N'D');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'5xx', N'E');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'6xx', N'F');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'R', N'7xx', N'G');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'1xx', N'H');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'2xx', N'I');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'3xx', N'J');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'4xx', N'K');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'5xx', N'L');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'6xx', N'M');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'Q', N'7xx', N'N');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'1xx', N'P');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'2xx', N'Q');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'3xx', N'R');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'4xx', N'W');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'5xx', N'X');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'6xx', N'Y');
INSERT INTO [dbo].[TestZ] ([TYPE], [CODESTRING], [CODE]) VALUES (N'T', N'7xx', N'Z');

SELECT D.TYPE,
STRING_AGG(
CASE WHEN value = 1 THEN CODE ELSE '' END,'') 
AS MODE2
--
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY TYPE
) AS ROWID
--
FROM (
SELECT TYPE,
LEFT(
REPLACE(
REPLACE(MODE,'1','1,'),'0','0,'),13) AS TempMode
FROM TestY AS A
) AS B
CROSS APPLY STRING_SPLIT(B.TempMode, ',') AS C
) AS D 
LEFT JOIN TestZ AS E ON E.TYPE=D.TYPE AND LEFT(E.CODESTRING,1)=D.ROWID
GROUP BY D.TYPE
ORDER BY 
CASE WHEN D.TYPE='R' THEN 1
     WHEN D.TYPE='Q' THEN 2
     WHEN D.TYPE='T' THEN 3 
END

Demo