sqlserver行变列

目标：将多行某列数据连接起来变成一个变量

col1

2
3
4

得到2 3 4

 

CREATE TABLE #temp2
(
  MaterialClassCode VARCHAR(100),
  MaterialClassName VARCHAR(100), 
)

INSERT INTO #temp2(MaterialClassCode,MaterialClassName)
SELECT DISTINCT(C.Code),c.[ClassName] FROM [SH_StoreHouseBillDetail] A
LEFT JOIN [SH_Material] B ON A.[MaterialID]=B.[ID]
LEFT JOIN [SH_MaterialClass] C ON B.[MaterialClassID]=C.[ID]
WHERE a.[StoreHouseBillID]=@MaterialInBillID

DECLARE @MaterialClassCode VARCHAR(1000)
DECLARE @MaterialClassName VARCHAR(1000)
SET @MaterialClassCode=''
SET @MaterialClassName=''
SELECT @MaterialClassCode=@MaterialClassCode+'&nbsp'+MaterialClassCode FROM #temp2
SELECT @MaterialClassName=@MaterialClassName+'&nbsp'+MaterialClassName FROM #temp2

使用临时表是为了distinct过滤重复
关键是：SELECT @MaterialClassCode=@MaterialClassCode+