SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

MCH_ID	MCH_NAME	REG_DATE	BODY1
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-13	2020-12-29 16:23:31.000	U1,B,U2,AQ
10	FAS-10	2020-11-11 11:11:11.000	A1,1,A2,2,A3,3,A4,4,A5,5,A6,6,A7,7,A8,8,A9,9,A10,10,A11,11,A12,12

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

MCH_ID	MCH_NAME	REG_DATE	SYC3K002	JlC2C109	JlC2C110	A1	A2	A3	A4	A5	A6	A7	A8	A9	A10	A11	A12	U1	U2	S2	SY02	SYC002	J9	Jl09	JlC110
10	FAS-10	2020-11-11 11:11:11.000	null	null	null	1	2	3	4	5	6	7	8	9	10	11	12	null	null	null	null	null	null	null	null
13	FAS-13	2020-12-29 16:23:31.000	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	B	AQ	null	null	null	null	null	null
7	FAS-08	2020-12-22 17:57:51.000	8.000	0.000	1.000	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
7	FAS-08	2020-12-24 09:20:35.000	8.000	0.000	0.000	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
7	FAS-08	2020-12-29 14:56:08.000	8.000	0.000	1.000	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
7	FAS-08	2020-12-29 16:23:31.000	8.000	0.000	1.000	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
8	FAS-77	2020-12-22 17:57:51.000	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	8.000	null	null	0.000	1.000
8	FAS-77	2020-12-29 16:23:31.000	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	8.000	null	8.000	0.000	null	null

Warning: Null value is eliminated by an aggregate or other SET operation.