sql server行转列

把多行记录放到一列：

用 for xml

create table #temp
(
OrderID int identity(1,1),
ProdCode varchar(20),
BuyThisProdName varchar(200)
)

insert into #temp values('Apple','Li Ming') ,
('Apple','Zhang San'),
('Apple','Xiao SI') ,
('Apple','Hon Bbb Test') ,
('Apple','Mr Test Traveller') ,
('HuaWei','Hon echo threetest') ,
('HuaWei','Prof Traveller with credit test') ,
('HuaWei','Dr echoosabre test')

select ProdCode,
(STUFF((SELECT ',' + BuyThisProdName
FROM #temp
WHERE ProdCode= Test.ProdCode
FOR
XML PATH('')
),1,1,'')) as BuyerList
from #temp AS Test
GROUP BY ProdCode
DROP TABLE #temp

 

 

这样我们在进行拆分，拆成一列一列的样子，但是有个问题是，在生产环境中我们并不知道有同一类型（apple）有多少人购买，因此我们无法估计拆分时的最大列有多少个（其实可以找到，这里就不写了）

拆分函数：

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[fn_find]') AND Objectproperty(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[fn_find]
GO
create function fn_find(@find varchar(8000), @str varchar(8000), @n smallint)
returns int
as
begin
if @n < 1 return (0)
declare @start smallint, @count smallint, @index smallint, @len smallint
set @index = charindex(@find, @str)
if @index = 0 return (0)
else select @count = 1, @len = len(@find)
while @index > 0 and @count < @n
begin
set @start = @index + @len
select @index = charindex(@find, @str, @start), @count = @count + 1
end
if @count < @n set @index = 0
return (@index)
end
go

 

动态获取：

create table #temp
(
OrderID int identity(1,1),
ProdCode varchar(20),
BuyThisProdName varchar(200)
)

insert into #temp values('Apple','Li Ming') ,
('Apple','Zhang San'),
('Apple','Xiao SI') ,
('Apple','Hon Bbb Test') ,
('Apple','Mr Test Traveller') ,
('HuaWei','Hon echo threetest') ,
('HuaWei','Prof Traveller with credit test') ,
('HuaWei','Dr echoosabre test')

Declare @NumMAXPermission int
select @NumMAXPermission =max(percount)
from (
select count(*) as percount
from #temp
group by ProdCode ) as t

if @NumMAXPermission >0
Begin
declare @InConditionPermission varchar(4000) , @j int = 1
while @j<= @NumMAXPermission
Begin
set @InConditionPermission = isnull(@InConditionPermission,'') + ',' + 'Buyer' + convert(varchar(5),@j)
set @j = @j + 1
continue
End
End
set @InConditionPermission = substring(@InConditionPermission ,2,4000)
print @InConditionPermission

select 'Buyer' + convert(varchar(4),ROW_NUMBER() over (partition by ProdCode order by OrderID)) as aa ,
ProdCode as ProdCode,
BuyThisProdName as MergeInfo
into ##tempMergePermission
from #temp

Declare @sql varchar(max)
set @sql = 'select *
from ##tempMergePermission as P
pivot
(max(MergeInfo) for P.aa in (' + @InConditionPermission + ')
) as T'

print @sql
exec (@sql)

drop table #temp
drop table ##tempMergePermission