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+' '+MaterialClassCode FROM #temp2
SELECT @MaterialClassName=@MaterialClassName+' '+MaterialClassName FROM #temp2
使用临时表是为了distinct过滤重复
关键是:SELECT @MaterialClassCode=@MaterialClassCode+