62,040
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #a(P_Code nvarchar(20))
INSERT #a SELECT 'A01'
UNION ALL SELECT 'A02'
UNION ALL SELECT 'B01'
UNION ALL SELECT 'B02'
CREATE TABLE #b(Con_Code nvarchar(20),Con_Name nvarchar(20))
INSERT #b SELECT 'W01','一号柜'
UNION ALL SELECT 'W02','二号柜'
UNION ALL SELECT 'W03','三号柜'
CREATE TABLE #c(Con_Code nvarchar(20),P_Code nvarchar(20),P_Num int)
INSERT #c SELECT 'W01','A01',10
UNION ALL SELECT 'W01','A02',20
UNION ALL SELECT 'W02','B01',30
UNION ALL SELECT 'W03','B02',40
UNION ALL SELECT 'W03','A01',100
DECLARE @case nvarchar(2000)
DECLARE @sql nvarchar(4000)
SELECT @case=ISNULL(@case,'')+'CASE Con_Name WHEN '''+Con_Name+''' THEN P_Num ELSE 0 END ['+Con_Name+'],' FROM #b ORDER BY Con_Code
PRINT @case
SET @case=SUBSTRING(@case,1,LEN(@case)-1)
SET @sql='SELECT P_Code,'+@case+' FROM (SELECT a.P_Code,c.P_Num,Con_Name FROM #c c INNER JOIN #a a ON c.P_Code=a.P_Code INNER JOIN #b b ON c.Con_Code=b.Con_Code)t'
EXEC(@sql)
declare @sql varchar(1000)
set @sql='select c.P_Code'
select @sql=@sql+',max(case Con_Name when '''+[Con_Name]+''' then P_Num else 0 end) ['+[Con_Name]+']'
from #b b inner join #c c on b.Con_Code=c.Con_Code group by [Con_Name]
set @sql=@sql+'from #c c inner join #a a on c.P_Code=a.P_Code inner join #b b on c.Con_Code=b.Con_Code group by c.P_Code'
exec(@sql)
/*
P_Code 二号柜 三号柜 一号柜
A01 0 100 10
A02 0 0 20
B01 30 0 0
B02 0 40 0
*/
--在case上加上sum,最后聚合一下就行了
DECLARE @case nvarchar(2000)
DECLARE @sql nvarchar(4000)
SELECT @case=ISNULL(@case,'')+'SUM(CASE Con_Name WHEN '''+Con_Name+''' THEN P_Num ELSE 0 END) ['+Con_Name+'],' FROM #b ORDER BY Con_Code
SET @case=SUBSTRING(@case,1,LEN(@case)-1)
SET @sql='SELECT P_Code,'+@case+' FROM (SELECT a.P_Code,c.P_Num,Con_Name FROM #c c INNER JOIN #a a ON c.P_Code=a.P_Code INNER JOIN #b b ON c.Con_Code=b.Con_Code)t GROUP BY P_Code'
EXEC(@sql)
/*
P_Code 一号柜 二号柜 三号柜
-------------------- ----------- ----------- -----------
A01 10 0 100
A02 20 0 0
B01 0 30 0
B02 0 0 40
(4 行受影响)
*/