iT邦幫忙

0

sql 字串切割問題

GJ 2021-07-20 20:04:44902 瀏覽

有GOOGLE了下,都是轉成多行的資料
資料結構如下面的圖

https://ithelp.ithome.com.tw/upload/images/20210720/2001497503jeQrUWhY.jpg

想把BODY1轉成如下
原本的資料的組成是"參數,值,參數,值,參數,值,......"
想把參數轉成欄再帶入值,有甚麼語法可用呢?
https://ithelp.ithome.com.tw/upload/images/20210720/20014975zH9fGEYg0w.jpg

需求是BODY1 欄位的格式可以轉成上面第二張圖的紅框的格式,把參數轉成欄位,數值轉成該參數值
MCH_ID	MCH_NAME	REG_DATE	        
	7	FAS-08	2020-12-22 17:57:51.000
    BODY1(參數,值,參數,值,參數,值)
    SYC3K002,8.000,J1C2C109,0.000,J1C2C110,1.000,J1C3C107,2.000
可以麻煩用文字貼上嗎?我老花看不到那串密密麻麻的字
你的SQL是啥版本的?MSSQL2008?2016+?MYSQL?5.3?8.0?
GJ iT邦研究生 3 級 ‧ 2021-07-21 10:29:14 檢舉
補在上面了,能看到嗎?
是MSSQL2008
1
rogeryao
iT邦大師 1 級 ‧ 2021-07-21 11:37:59
最佳解答

更新

CREATE TABLE TableX
(	
MCH_ID VARCHAR(10),
MCH_NAME VARCHAR(10),	
REG_DATE VARCHAR(23),	
BODY1 VARCHAR(50)
);

INSERT INTO TableX VALUES
('7','FAS-08','2020-12-22 17:57:51.000','SYC3K002,8.000,JlC2C109,0.000,JlC2C110,1.000'),
('7','FAS-08','2020-12-24 09:20:35.000','SYC3K002,8.000,JlC2C109,0.000,JlC2C110,0.000'),
('7','FAS-08','2020-12-29 14:56:08.000','SYC3K002,8.000,JlC2C109,0.000,JlC2C110,1.000'),
('7','FAS-08','2020-12-29 16:23:31.000','SYC3K002,8.000,JlC2C109,0.000,JlC2C110,1.000'),
('8','FAS-77','2020-12-29 16:23:31.000','S2,8.000,J9,0.000'),
('8','FAS-77','2020-12-22 17:57:51.000','SY02,8.000,Jl09,0.000,JlC110,1.000'),
('8','FAS-77','2020-12-29 16:23:31.000','SYC002,8.000'),
('12','FAS-12','2020-12-12 12:12:12.000',Null),
('11','FAS-11','2020-11-11 11:11:11.000',''),
-- 值 : 字串
('13','FAS-77','2020-12-29 16:23:31.000','U1,B,U2,AQ');
Declare @FixFieldName Nvarchar(Max) = ''
Declare @DynFieldCountStr Nvarchar(Max) = ''
Declare @DynFieldCount Int = 0
Declare @DynFieldData Nvarchar(Max) = ''
Declare @FinalFieldData Nvarchar(Max) = ''
Declare @FieldCountSql Nvarchar(Max) = ''
Declare @SqlFrom Nvarchar(Max) = ''
Declare @K Int = 1
Declare @UnionSql Nvarchar(Max) = ''
Declare @FieldHead Nvarchar(Max) = ''
Declare @FieldHeadDist Nvarchar(Max) = ''
Declare @FieldHeadSql Nvarchar(Max) = ''
Declare @TransferSql Nvarchar(Max) = ''

-- 計算 BODY1 內要分拆的欄位最大值
Set @FieldCountSql = 'Select @DynFieldCountStr = Max(Len(BODY1)-Len(Replace(BODY1,'','',''''))) From TableX'
Exec Sp_executesql @FieldCountSql, N'@DynFieldCountStr Nvarchar(Max) Out', @DynFieldCountStr Out

Set @DynFieldCount = Cast(Isnull(@DynFieldCountStr,'0') As Int)
If @DynFieldCount > 0
Begin
  Set @DynFieldCount = @DynFieldCount + 1
  -- Print @DynFieldCount

  -- BODY1 以外的固定欄位
  Set @FixFieldName = 'Select MCH_ID,MCH_NAME,REG_DATE,'
  -- BODY1 用 Xml 方式分割
  Set @SqlFrom = ' From (' + @FixFieldName +
  'Cast(''<Tr><Td>'' + Replace(BODY1, '','', ''</Td><Td>'') + ''</Td></Tr>'' As Xml) Vals
  From TableX) As D '

  While @K<=@DynFieldCount
  Begin
    -- 產生新的欄位名稱(FName)及資料(FData)
    If (@K > 1)
    Begin
      Set @UnionSql = 'Union All '
    End
    Select @DynFieldData =
    'D.Vals.value(''(/Tr/Td)[' + Cast(@K As Nvarchar(4)) + ']'',''Nvarchar(Max)'')' + ' As [FName],' +
    -- 值 : 全部數字
    -- 'D.Vals.value(''(/Tr/Td)[' + Cast(@K + 1 As Nvarchar(4)) + ']'',''FLOAT'')' + ' As [FData],'
    -- 值 : 含有字串
    'D.Vals.value(''(/Tr/Td)[' + Cast(@K + 1 As Nvarchar(4)) + ']'',''Nvarchar(Max)'')' + ' As [FData],'
    Set @DynFieldData = Left(@DynFieldData, Len(@DynFieldData) - 1)
    Set @FinalFieldData = @FinalFieldData + @UnionSql + @FixFieldName + @DynFieldData + @SqlFrom
    Set @K = @K + 2
  End

  -- 產生新的 Table : MCH_ID,MCH_NAME,REG_DATE,FName,FData
  Set @FinalFieldData = 'Select * ' +
  'From (' + @FinalFieldData + ') As M ' +
  'Where Len(Trim([FName])) > 0 '
  -- 指定顯示欄位 : SYC3K002 , JlC2C109 , JlC2C110
  -- + 'And [FName] In (''SYC3K002'',''JlC2C109'',''JlC2C110'') '
  -- Exec (@FinalFieldData)

  -- 重組新產生的欄位名稱
  Set @FieldHeadDist = 'Select ' + ''','' + ' + '[FName]' + ' '+
  'From (' + @FinalFieldData + ') As U '+
  'Group By [FName] ' +
  'For Xml Path('''') '

  Set @FieldHeadSql = 'Select @FieldHead = (' + @FieldHeadDist + ')'
  Exec Sp_executesql @FieldHeadSql, N'@FieldHead Nvarchar(Max) Out', @FieldHead Out
  -- Print (@FieldHead)
  
  -- 動態行轉列
  -- 值 : 全部數字
  -- Set @TransferSql = 'Select * From (' + @FinalFieldData + ') As F Pivot(Sum(FData) For [FName] In ('
  -- 值 : 含有字串
  Set @TransferSql = 'Select * From (' + @FinalFieldData + ') As F Pivot(Max(FData) For [FName] In ('
  Set @FieldHead = (Select Stuff(@FieldHead, 1, 1, ''))
  Set @TransferSql = @TransferSql + @FieldHead + '))' + ' K '
  -- 篩選條件
  -- + 'Where JlC2C110 = 0 '

  Exec (@TransferSql)
End
Else
Begin
  -- BODY1 內完全無資料不處理
  Exec ('Select * From TableX')
End

Demo

看更多先前的回應...收起先前的回應...
GJ iT邦研究生 3 級 ‧ 2021-07-21 13:38:09 檢舉

謝謝,那個參數值有近一百個,格式是固定的
我可能要請USER列一下需要的再抓了

rogeryao iT邦大師 1 級 ‧ 2021-07-26 23:16:10 檢舉

你要的報表應該就是這樣吧

GJ iT邦研究生 3 級 ‧ 2021-07-27 09:33:31 檢舉

感謝/images/emoticon/emoticon02.gif
這兩天問了user確認參數,主要就兩三套在產
每一套是100多個這樣,正嘗試用if判斷初始來確認後再轉換
沒定義報表要長怎麼樣只有先轉換body再來看,會參考您的看看
試出來後幫您選為解答

GJ iT邦研究生 3 級 ‧ 2021-07-29 13:26:11 檢舉

TO rogeryao

--在Fdata 產生遇到有文字的參數時有方法可解嗎?
--Error converting data type nvarchar to float.

 'D.Vals.value(''(/Tr/Td)[' + Cast(@K + 1 As Nvarchar(4)) + ']'',''FLOAT'')' + ' As [FData],'
rogeryao iT邦大師 1 級 ‧ 2021-07-29 13:51:38 檢舉

已更新

rogeryao iT邦大師 1 級 ‧ 2021-08-17 19:23:58 檢舉

欄位依 BODY1 內的【參數】順序

Declare @FixFieldName Nvarchar(Max) = ''
Declare @DynFieldCountStr Nvarchar(Max) = ''
Declare @DynFieldCount Int = 0
Declare @DynFieldData Nvarchar(Max) = ''
Declare @FinalFieldData Nvarchar(Max) = ''
Declare @FinalFieldDataTempA Nvarchar(Max) = ''
Declare @FinalFieldDataTempB Nvarchar(Max) = ''
Declare @FieldCountSql Nvarchar(Max) = ''
Declare @SqlFrom Nvarchar(Max) = ''
Declare @K Int = 1
Declare @UnionSql Nvarchar(Max) = ''
Declare @FieldHead Nvarchar(Max) = ''
Declare @FieldHeadDist Nvarchar(Max) = ''
Declare @FieldHeadSql Nvarchar(Max) = ''
Declare @TransferSql Nvarchar(Max) = ''
Declare @FixFieldNameSql Nvarchar(Max) = ''
Declare @TempSql Nvarchar(Max) = ''

-- 計算 BODY1 內要分拆的欄位最大值
Set @FieldCountSql = 'Select @DynFieldCountStr = Max(Len(BODY1)-Len(Replace(BODY1,'','',''''))) From TableX'
Exec Sp_executesql @FieldCountSql, N'@DynFieldCountStr Nvarchar(Max) Out', @DynFieldCountStr Out

Set @DynFieldCount = Cast(Isnull(@DynFieldCountStr,'0') As Int)
If @DynFieldCount > 0
Begin
  Set @DynFieldCount = @DynFieldCount + 1
  -- Print @DynFieldCount

  -- BODY1 以外的固定欄位
  Set @FixFieldName = 'MCH_ID,MCH_NAME,REG_DATE'
  Set @FixFieldNameSql = 'Select ' + @FixFieldName + ','
  -- BODY1 用 Xml 方式分割
  Set @SqlFrom = ' From (' + @FixFieldNameSql +
  'Cast(''<Tr><Td>'' + Replace(BODY1, '','', ''</Td><Td>'') + ''</Td></Tr>'' As Xml) Vals
  From TableX
  Where Len(Trim(BODY1)) > 0 ) As D '

  While @K<=@DynFieldCount
  Begin
    -- 產生新的欄位名稱(FName)及資料(FData)
    If (@K > 1)
    Begin
      Set @UnionSql = 'Union All '
    End
    Select @DynFieldData =
    -- BODY1 內相同的 MCH_NAME 有相同欄位名稱用下式
    'D.Vals.value(''(/Tr/Td)[' + Cast(@K As Nvarchar(4)) + ']'',''Nvarchar(Max)'')' + ' As [FName],' +
    -- 若出現錯誤 : 為 'ZK' 指定了資料行 'A' 多次,表示
    -- BODY1 內不同的 MCH_NAME 有相同欄位名稱用下式
    -- 'Replace(MCH_NAME,''-'',''_'')' + '+''_''+' + 'D.Vals.value(''(/Tr/Td)[' + Cast(@K As Nvarchar(4)) + ']'',''Nvarchar(Max)'') ' + ' As [FName],'+
    -- 值 : 全部數字
    -- 'D.Vals.value(''(/Tr/Td)[' + Cast(@K + 1 As Nvarchar(4)) + ']'',''Float'')' + ' As [FData],'
    -- 值 : 含有字串
    'D.Vals.value(''(/Tr/Td)[' + Cast(@K + 1 As Nvarchar(4)) + ']'',''Nvarchar(Max)'')' + ' As [FData],' +
    -- 保持 BODY1 原本欄位順序
    Cast(@K As Nvarchar(4)) +' As [FOrderNum],'
    Set @DynFieldData = Left(@DynFieldData, Len(@DynFieldData) - 1)
    Set @FinalFieldData = @FinalFieldData + @UnionSql + @FixFieldNameSql + @DynFieldData + @SqlFrom
    Set @K = @K + 2
  End

  -- 產生新的 Table : MCH_ID,MCH_NAME,REG_DATE,FName,FData
  Set @TempSql = 'From (' + @FinalFieldData + ') As M ' +
  'Where Len(Trim([FName])) > 0 '
  -- 指定顯示欄位 : SYC3K002 , JlC2C109 , JlC2C110
  -- + 'And [FName] In (''SYC3K002'',''JlC2C109'',''JlC2C110'') '  
  Set @FinalFieldDataTempA = 'Select ' + @FixFieldName + ',FName,FData ' + @TempSql
  -- Exec (@FinalFieldDataTempA)  

  -- 產生新的 Table : MCH_ID,MCH_NAME,REG_DATE,FName,FData,FOrderNum
  Set @FinalFieldDataTempB = 'Select ' + @FixFieldName + ',FName,FData,FOrderNum ' + @TempSql  
  -- Exec (@FinalFieldDataTempB)

  -- 重組新產生的欄位名稱
  Set @FieldHeadDist = 'Select ' + ''','' + ' + '[FName]' + ' '+
  'From (' + @FinalFieldDataTempB + ') As U '+
  -- 依字元排序欄位
  -- 'Group By [FName] ' +
  -- 保持 BODY1 原本欄位順序
  'Group By [MCH_NAME],[FOrderNum],[FName] ' +
  'Order By [MCH_NAME],[FOrderNum],[FName] ' +
  'For Xml Path('''') '

  Set @FieldHeadSql = 'Select @FieldHead = (' + @FieldHeadDist + ')'
  Exec Sp_executesql @FieldHeadSql, N'@FieldHead Nvarchar(Max) Out', @FieldHead Out
  -- Print (@FieldHead)

  -- 動態行轉列
  -- 值 : 全部數字
  -- Set @TransferSql = 'Select * From (' + @FinalFieldDataTempA + ') As F Pivot(Sum(FData) For [FName] In ('
  -- 值 : 含有字串
  Set @TransferSql = 'Select * From (' + @FinalFieldDataTempA + ') As F Pivot(Max(FData) For [FName] In ('
  Set @FieldHead = (Select Stuff(@FieldHead, 1, 1, ''))
  Set @TransferSql = @TransferSql + @FieldHead + '))' + ' ZK '
  -- 篩選條件
  -- + 'Where JlC2C110 = 0 '
  + 'Order by ' + @FixFieldName 

  Exec (@TransferSql)
End
Else
Begin
  -- BODY1 內完全無資料不處理
  Exec ('Select * From TableX')
End

Demo

2
純真的人
iT邦大師 1 級 ‧ 2021-07-20 20:23:48

MSSQL的話~
T-SQL可以辦到@@...

需要的話~你用個文字敘述表達@@..

好了~那你改一下你自己的欄位...參數欄位是動態帶出的@@..

declare @Tmp table(
	MCH_ID int
	,MCH_NAME nvarchar(50)
	,MCH_DATE datetime
	,BODY1 nvarchar(max)
)

insert into @Tmp
values('1','FAS-08','2020-12-22 17:57:50','test1,8.00,test2,0.00,test3,1.00')
,('1','FAS-08','2020-12-23 17:57:50','test1,7.00,test2,1.00,test3,0.00')

declare @i int = 0,@count int = 0,@BODY nvarchar(max)
declare @j int = 0,@Sub_count int = 0,@BODYHead nvarchar(max)='',@BODYNum nvarchar(max)=''
declare @k int = 0,@Table nvarchar(max)=''
declare @MCH_ID int,@MCH_NAME nvarchar(50),@MCH_DATE datetime
select @count=count(0)
from @Tmp

set @Table = 'select * '
set @Table = @Table + ' from (('
while(@i<@count)
begin
	set @i = @i + 1
	select @MCH_ID=MCH_ID
	,@MCH_NAME=MCH_NAME
	,@MCH_DATE=MCH_DATE
	,@BODY=BODY1
	from (
		select Row_Number()Over(order by MCH_DATE) Sort
		,*
		from @Tmp
	) k
	where Sort = @i

	if(@i>1)
	begin
		set @Table = @Table + ')union all('
	end
	set @Table = @Table + ' select '
	set @Table = @Table + Convert(varchar,@MCH_ID) + ' MCH_ID, '
	set @Table = @Table + '''' + @MCH_NAME + ''' MCH_NAME, '
	set @Table = @Table + '''' + Convert(varchar,@MCH_DATE,120) + ''' MCH_DATE, '

	--print @BODY
	set @BODYHead = ''
	set @BODYNum = ''
	set @j = 0
	set @k = 0

	set @Sub_count = len(@BODY)
	while(@j<@Sub_count)
	begin
		set @j = @j + 1
		if(substring(@BODY,@j,1)=',')
		begin
			set @k = @k + 1
		end
		if(@k % 2 = 0 or @k = 0)
		begin
			if(substring(@BODY,@j,1) = ',')
			begin
				--print @BODYNum + ' ' + @BODYHead
				set @Table = @Table + @BODYNum + ' ' + @BODYHead + ','
				set @BODYHead = ''
			end
			else
			begin
				set @BODYHead = @BODYHead + substring(@BODY,@j,1)
			end
		end
		else
		begin
			if(substring(@BODY,@j,1) = ',')
			begin
				set @BODYNum = ''
			end
			else
			begin
				set @BODYNum = @BODYNum + substring(@BODY,@j,1)
			end
		end
	end
	--print @BODYNum + ' ' + @BODYHead
	set @Table = @Table + @BODYNum + ' ' + @BODYHead

end
set @Table = @Table + ' )) k'

--print @Table
EXEC (@Table);

https://ithelp.ithome.com.tw/upload/images/20210721/20061369TvL8XmNLce.png

看更多先前的回應...收起先前的回應...
GJ iT邦研究生 3 級 ‧ 2021-07-21 10:31:44 檢舉

補在上面了,不知能否?謝謝

好了~已更新@@..這是用MSSQL 2008寫的..

GJ iT邦研究生 3 級 ‧ 2021-07-21 13:31:32 檢舉

謝謝~我試試看,再選解答

GJ iT邦研究生 3 級 ‧ 2021-07-22 11:46:07 檢舉

我測試了下ok~但我在MCH_ID前多加幾個欄位
反而亂了,我加在DECLARE 跟下面這邊,不知這樣對嗎?謝謝
https://ithelp.ithome.com.tw/upload/images/20210722/20014975xzmNE3egiE.jpg

只要輸出Union欄位一致就沒有問題唷~

select *
from ((
  select '' xxxx
  ,'' xxxx
  ,'' xxxx
  ,'' xxxx
)union all(
  select '' xxxx
  ,'' xxxx
  ,'' xxxx
  ,'' xxxx
)) k

GJ iT邦研究生 3 級 ‧ 2021-07-23 17:08:44 檢舉

TO 純真的人

請問會因為我的參數有不同組或是某個欄位值不同而無法用嗎?
測試如下這樣就跑不出來

https://ithelp.ithome.com.tw/upload/images/20210723/20014975fzBMSjBusc.jpg

如果兩筆資料的欄位數不同的話~你就先取最大攔位數來顯示~
另外不用擔心欄位長度問題@@..我那個不是抓固定的~
參考這篇回答~
https://ithelp.ithome.com.tw/questions/10204553

rogeryao iT邦大師 1 級 ‧ 2021-07-23 20:49:39 檢舉

TO 純真的人
他的意思是 BODY1 內的第 1,3,5,... 欄內的值會變動;
第 1 欄不是固定的 SYC3K002 會變成 K2
第 3 欄不是固定的 JlC2C109 會變成 K3
第 5 欄不是固定的 JlC2C110 會變成 K994
也就是說當有 200000 筆資料,每一筆資料可能會有【近一百個參數值】
要分拆出來【參數欄位】 = 【值欄位】 = 200000 * 100 /2

的確若不同組使用~會有對應參數異常@@...
不過就是T-SQL多花費一點if判斷來過濾了...(應該if到煩@@)

0
japhenchen
iT邦大師 1 級 ‧ 2021-07-21 11:26:05

2008..........沒split可用,只能用index土法煉鋼做user defined function
(以下為我自己做的Function)

CREATE FUNCTION SUBSTRING_INDEX
(
    @STRING VARCHAR(8000), 
    @DELIMITER CHAR(1), 
    @COUNT INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @I INT,@P INT ,@L INT,@RESULT VARCHAR(8000),@TEMP VARCHAR(256)
	SET @STRING = @STRING + @DELIMITER
	SELECT @I=0
	SELECT @P=0
	SELECT @L=1
	WHILE @I < @COUNT BEGIN
		IF (@P-1)>@L 
			SET @L = @P
		SELECT @P=CHARINDEX(@DELIMITER,@STRING,@P+1) 
		SELECT @I=@I+1 
	END
	IF @P > 0
		SELECT @RESULT=SUBSTRING(@STRING,@L+1,@P-@L-1)
	ELSE
		SELECT @RESULT =''
	RETURN @RESULT
END

用法

SELECT SUBSTRING_INDEX('AAAA,bbbb,cccc,xxyyzzz', ',', 4)

範例的回傳值

xxyyzzz

用來解決你的問題

SELECT 
    NAME,
    ,SUBSTRING_INDEX(body,',',2) as SYC3K002
    ,SUBSTRING_INDEX(body,',',4) as J1C2C110 
    ,...........以此類推
    FROM dataTABLE where ....
GJ iT邦研究生 3 級 ‧ 2021-07-21 13:36:24 檢舉

謝謝,那個參數值有近一百個,這是設備收集的資料庫
我可能要請USER列一下需要的再抓了

我要發表回答

立即登入回答