iT邦幫忙

0

請問SQL指令該如何寫?

sql
XYZ 2020-10-08 17:43:211181 瀏覽

大家好,

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

表一:
TYPE MODE
R 1101111
Q 1111000
T 1110111

表二:
https://ithelp.ithome.com.tw/upload/images/20201008/2010815784qTHcF2lg.jpg

表三:
TYPE MODE2
R ABDEFG
Q HIJK
T PQRXYZ

恩??對應怪怪的..不是這樣嗎?
TYPE MODE2
R ABDEFG
Q HIJK
T PQRXYZ
Zed_Yang iT邦新手 3 級 ‧ 2020-10-08 18:15:03 檢舉
你之前的發問都有被解決了嗎?
XYZ iT邦新手 5 級 ‧ 2020-10-12 09:50:19 檢舉
to 純真的人
謝謝您的糾正,已更新

1 個回答

0
rogeryao
iT邦大師 1 級 ‧ 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

看更多先前的回應...收起先前的回應...
XYZ iT邦新手 5 級 ‧ 2020-10-12 11:57:01 檢舉

抱歉 第3個圖的指令, 我幾乎看不太懂耶

XYZ iT邦新手 5 級 ‧ 2020-10-12 11:57:37 檢舉

to rogeryao
抱歉 第3個圖的指令, 我幾乎看不太懂耶

rogeryao iT邦大師 1 級 ‧ 2020-10-12 12:19:15 檢舉

請參閱簡要說明

另外,請 Google 以下關鍵字的用法 :
1.REPLACE
2.STRING_SPLIT
3.CROSS APPLY
4.ROW_NUMBER() OVER
5.STRING_AGG

rogeryao iT邦大師 1 級 ‧ 2020-10-13 21:03:10 檢舉

簡要說明我已更新了.
另外,蠻好奇的,這個問題會應用在何處 ?

XYZ iT邦新手 5 級 ‧ 2020-10-28 11:40:42 檢舉

其實還是不太懂,還要研究中,不過最近較忙, 只能先放著,用在廠商開發的套裝軟體寫入DB資料,我要整合成USER看的懂的報表

我要發表回答

立即登入回答