可以使用 PIVOT
使用 PIVOT 和 UNPIVOT

你參考~動態欄位~直轉換橫~
MSSQL
T-SQL語法

declare @Tmp table(
	單號  nvarchar(50)
	,品名 nvarchar(50)
	,品號 nvarchar(50)
)

insert into @Tmp
values('540120200818005','A1','CZ0017011')
,('540120200818005','B1','DBB4031301')
,('540120200818005','C1','DCA4031303')
,('540120200818006','D1','CZ0017011')
,('540120200818006','F1','DBB4031302')
,('540120200818006','A2','DCA4031304')
,('540120200818006','A4','DCA4031305')

declare @Max_Count int =0
select top 1 @Max_Count=count(0)
from @Tmp
group by 單號
order by count(0) desc

declare @i int = 0,@count int = 0
declare @j int = 0,@Sub_count int = 0
declare @k int = 0,@Table nvarchar(max)=''
declare @單號 nvarchar(50),@品名 nvarchar(50),@品號 nvarchar(50)
select @count=count(0)
from (
	select 單號
	from @Tmp
	group by 單號
) k

set @Table = 'select * '
set @Table = @Table + ' from (('
while(@i<@count)
begin
	set @i = @i + 1
	select @單號=單號
	from (
		select Row_Number()Over(order by 單號) Sort
		,單號
		from @Tmp
		group by 單號
	) k
	where Sort = @i

	if(@i>1)
	begin
		set @Table = @Table + ')union all('
	end
	set @Table = @Table + ' select '
	set @Table = @Table + '''' + @單號 + ''' 單號 '

	set @j = 0
	set @k = 0

	select @Sub_count=count(0)
	from @Tmp
	where 單號 = @單號

	while(@j<@Max_Count)
	begin
		set @j = @j + 1

		set @品名 = ''
		set @品號 = ''

		select @品名=品名
		,@品號=品號
		from (
			select Row_Number()Over(order by 品名) Sort
			,*
			from @Tmp
			where 單號 = @單號
		) k
		where Sort = @j

		set @Table = @Table + ',N''' + @品名 + '''  品名' + Convert(varchar,@j) + ', N''' + @品號 + ''' 品號' + Convert(varchar,@j)

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

--print @Table
EXEC (@Table);