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-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

MCH_ID	MCH_NAME	REG_DATE	J9	Jl09	JlC110	JlC2C109	JlC2C110	S2	SY02	SYC002	SYC3K002	U1	U2
13	FAS-77	2020-12-29 16:23:31.000	null	null	null	null	null	null	null	null	null	B	AQ
7	FAS-08	2020-12-22 17:57:51.000	null	null	null	0.000	1.000	null	null	null	8.000	null	null
7	FAS-08	2020-12-24 09:20:35.000	null	null	null	0.000	0.000	null	null	null	8.000	null	null
7	FAS-08	2020-12-29 14:56:08.000	null	null	null	0.000	1.000	null	null	null	8.000	null	null
7	FAS-08	2020-12-29 16:23:31.000	null	null	null	0.000	1.000	null	null	null	8.000	null	null
8	FAS-77	2020-12-22 17:57:51.000	null	0.000	1.000	null	null	null	8.000	null	null	null	null
8	FAS-77	2020-12-29 16:23:31.000	0.000	null	null	null	null	8.000	null	8.000	null	null	null

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