SQL語法問題請教(分割

sql

菜中之菜 2022-07-29 14:58:01 ‧ 1319 瀏覽

分享至

序號   storName               CityCode
1     三立廚房-花蓮店	        hls
2     長青鱒魚客家菜餐廳        hcs,hcc
3     精華牛排	               hcs,hcc
4     維尼斯麵包坊     	     hcs,hcc
5     富華國際皮件有限公司	  hcs,hcc
6     SUM鴻城汽車保修有限公司   tyc
7     隆溢行開運印鑑專門店	  tpc

不好意思排版有點亂
想問一下我該如何利用MS SQL語法
將上方資料 StoreName,CityCode 分割成11筆資料
變成

序號   storName             CityCode
1   三立廚房-花蓮店	       hls
2   長青鱒魚客家菜餐廳        hcs
3   長青鱒魚客家菜餐廳        hcc
4   精華牛排	             hcs
5   精華牛排	             hcc
.....
.....
....

感激不盡

柳丁柚 iT邦新手 1 級 ‧ 2022-07-29 16:06:19 檢舉

看一下這是不是你要的
https://dotblogs.com.tw/rainmaker/2015/04/21/151106

菜中之菜 iT邦新手 4 級 ‧ 2022-08-05 09:29:28 檢舉

感謝大大

登入發表討論

直播研討會

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

直播中

3 個回答

純真的人

iT邦大師 1 級 ‧ 2022-07-29 15:59:46

最佳解答

參考~

Create table Tmp(
	序號 int
	,storName nvarchar(50)
	,CityCode nvarchar(50)
);
insert into Tmp
values(1,N'三立廚房-花蓮店',N'hls')
,(2,N'長青鱒魚客家菜餐廳',N'hcs,hcc')
,(3,N'精華牛排',N'hcs,hcc')
,(4,N'維尼斯麵包坊',N'hcs,hcc')
,(5,N'富華國際皮件有限公司',N'hcs,hcc')
,(6,N'SUM鴻城汽車保修有限公司',N'tyc')
,(7,N'隆溢行開運印鑑專門店',N'tpc');

select *
from Tmp

select Row_Number()Over(order by 序號) as Sort
,storName
,(
  select value
  from (
    select Row_Number()Over(order by value) i
    ,value
    from STRING_SPLIT(CityCode,',')
  ) k
  where i = Sort
) CityCode
from Tmp
,(
	select Row_Number()Over(order by [number]) as Sort
	from master..spt_values
) as k
where Sort <= len(CityCode) - len(Replace(CityCode,',','')) + 1
order by Row_Number()Over(order by 序號)

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6e043aa29c0f7fc10f868da91fc24be3

如果你是低於2016版本~就這樣吧@@...

declare @Tmp table(
	序號 int
	,storName nvarchar(50)
	,CityCode nvarchar(50)
)
insert into @Tmp
values(1,N'三立廚房-花蓮店',N'hls')
,(2,N'長青鱒魚客家菜餐廳',N'hcs,hcc')
,(3,N'精華牛排',N'hcs,hcc')
,(4,N'維尼斯麵包坊',N'hcs,hcc')
,(5,N'富華國際皮件有限公司',N'hcs,hcc')
,(6,N'SUM鴻城汽車保修有限公司',N'tyc')
,(7,N'隆溢行開運印鑑專門店',N'tpc')

select Row_Number()Over(order by 序號) as Sort
,storName
,(
	case Sort
	when 1 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[1]','NVARCHAR(50)') 
	when 2 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[2]','NVARCHAR(50)') 
	when 3 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[3]','NVARCHAR(50)') 
	when 4 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[4]','NVARCHAR(50)') 
	when 5 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[5]','NVARCHAR(50)') 
	when 6 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[6]','NVARCHAR(50)') 
	when 7 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[7]','NVARCHAR(50)') 
	when 8 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[8]','NVARCHAR(50)') 
	when 9 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[9]','NVARCHAR(50)') 
	when 10 then CAST('<TR><TD>' + REPLACE(CityCode, ',', '</TD><TD>') + '</TD></TR>' AS xml).value('(/TR/TD)[10]','NVARCHAR(50)') 
	end
) CityCode
from @Tmp
,(
	select Row_Number()Over(order by [number]) as Sort
	from master..spt_values
) as k
where Sort <= len(CityCode) - len(Replace(CityCode,',','')) + 1

回應 1
分享
檢舉

菜中之菜 iT邦新手 4 級 ‧ 2022-08-05 09:28:01 檢舉

感謝大大,不好意思現在才上來選

登入發表回應

rogeryao

iT邦超人 7 級 ‧ 2022-07-29 19:49:46

CREATE TABLE Tmp (
No int,
storName nvarchar(50),
CityCode nvarchar(50));

INSERT INTO Tmp VALUES 
(1, N'三立廚房-花蓮店', N'hls'),
-- 額外加入 XYZ
(2, N'長青鱒魚客家菜餐廳', N'hcs,hcc,XYZ'),
(3, N'精華牛排', N'hcs,hcc'),
(4, N'維尼斯麵包坊', N'hcs,hcc'),
(5, N'富華國際皮件有限公司', N'hcs,hcc'),
(6, N'SUM鴻城汽車保修有限公司', N'tyc'),
(7, N'隆溢行開運印鑑專門店', N'tpc');

解法一 :

SELECT ROW_NUMBER() OVER (ORDER BY C.No) AS No,C.storName,C.CityCode
FROM (SELECT A.No,A.storName,B.value AS CityCode
FROM Tmp AS A
CROSS APPLY STRING_SPLIT(A.CityCode, ',') AS B) AS C

解法二 :

-- SQL Server 2016 以前不支援 STRING_SPLIT :

;WITH CTE_X0 AS (
SELECT No,storName,CityCode + ',' AS CityCode
FROM Tmp),
CTE_X1 AS (
SELECT No,storName,CityCode,
SUBSTRING(CityCode, 1, CHARINDEX(',', CityCode) - 1) AS STRA,
SUBSTRING(CityCode, CHARINDEX(',', CityCode) + 1, LEN(CityCode) - CHARINDEX(',', CityCode)) AS STRB,
CHARINDEX(',', CityCode) AS NUM
FROM CTE_X0
--
UNION ALL
SELECT No,storName,CityCode,
SUBSTRING(STRB, 1, CHARINDEX(',', STRB) - 1) AS STRA,
SUBSTRING(STRB, CHARINDEX(',', STRB) + 1, LEN(STRB) - CHARINDEX(',', STRB)) AS STRB,
NUM + CHARINDEX(',', STRB) AS NUM
FROM CTE_X1
WHERE LEN(CityCode) - NUM > 0)

SELECT ROW_NUMBER() OVER (ORDER BY C.No) AS No,C.storName,C.STRA AS 'CityCode'
FROM CTE_X1 AS C

Demo

解法參考 : [MSSQL]欄位拆解相關疑問

回應 1
分享
檢舉

菜中之菜 iT邦新手 4 級 ‧ 2022-08-05 09:28:40 檢舉

感謝大大

登入發表回應

一級屠豬士

iT邦大師 1 級 ‧ 2022-07-29 23:17:37

使用 PostgreSQL 14

create table it0729 (
  id int not null primary key
, sname text not null
, citycode text not null
);

insert into it0729 values
(1, '三立廚房-花蓮店', 'hls'),
-- 額外加入 XYZ
(2, '長青鱒魚客家菜餐廳', 'hcs,hcc,XYZ'),
(3, '精華牛排', 'hcs,hcc'),
(4, '維尼斯麵包坊', 'hcs,hcc'),
(5, '富華國際皮件有限公司', 'hcs,hcc'),
(6, 'SUM鴻城汽車保修有限公司', 'tyc'),
(7, '隆溢行開運印鑑專門店', 'tpc');

select id, sname, string_to_table(citycode, ',')
  from it0729;

id 	sname 	string_to_table
1 	三立廚房-花蓮店 	hls
2 	長青鱒魚客家菜餐廳 	hcs
2 	長青鱒魚客家菜餐廳 	hcc
2 	長青鱒魚客家菜餐廳 	XYZ
3 	精華牛排 	hcs
3 	精華牛排 	hcc
4 	維尼斯麵包坊 	hcs
4 	維尼斯麵包坊 	hcc
5 	富華國際皮件有限公司 	hcs
5 	富華國際皮件有限公司 	hcc
6 	SUM鴻城汽車保修有限公司 	tyc
7 	隆溢行開運印鑑專門店 	tpc

demo