Sql 不确定列 行转列操作

做项目时，用到了汇总统计的行转列，且

表结构：

 

 具体存储过程脚本如下：

-- =============================================
-- Author:  
-- Create date:  
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[cp_Material_PutOutCostStat_StatDay_GetFeedConsumeList]
@Page INT ,
@PageSize INT ,
@LineID VARCHAR(512),
@StartDate DATE,
@EndDate DATE,
@OrderColumn VARCHAR(512),
@RowCount INT OUTPUT
AS
BEGIN
DECLARE @iBeginID INT
DECLARE @iEndID INT

SET NOCOUNT ON
SET @iBeginID = ( @Page - 1 ) * @PageSize + 1
SET @iEndID = @iBeginID + @PageSize - 1
SET @RowCount = 0;
--汇总项：猪阶段类型(IN)
DECLARE @PigStageType VARCHAR(128);
--查询 增加Isnull
DECLARE @PigStageTypeSelect VARCHAR(256);
--单行合计
DECLARE @PigStageTypeRowSum VARCHAR(256);
--整列合计
DECLARE @PigStageTypeColSum VARCHAR(256);
SET @PigStageType = (SELECT '[' + CONVERT(VARCHAR(10), ID) + '],' FROM View_PigMGT_Dict_Dict_PigStage WITH(NOLOCK) FOR XML PATH(''));
SET @PigStageTypeSelect = (SELECT 'ISNULL([' + CONVERT(VARCHAR(10),ID) + '],0) AS [' + CONVERT(VARCHAR(10), ID)+ '],' FROM View_PigMGT_Dict_Dict_PigStage WITH(NOLOCK) FOR XML PATH(''));
SET @PigStageTypeRowSum = (SELECT 'ISNULL([' + CONVERT(VARCHAR(10),ID) + '],0) +' FROM View_PigMGT_Dict_Dict_PigStage WITH(NOLOCK) FOR XML PATH(''));
SET @PigStageTypeColSum = (SELECT 'SUM(ISNULL([' + CONVERT(VARCHAR(10),ID) + '],0)) AS [' + CONVERT(VARCHAR(10), ID)+ '],' FROM View_PigMGT_Dict_Dict_PigStage WITH(NOLOCK) FOR XML PATH(''));

SET @PigStageType = SUBSTRING(@PigStageType,0,LEN(@PigStageType));
SET @PigStageTypeSelect = SUBSTRING(@PigStageTypeSelect,0,LEN(@PigStageTypeSelect));
SET @PigStageTypeRowSum = SUBSTRING(@PigStageTypeRowSum,0,LEN(@PigStageTypeRowSum));
SET @PigStageTypeColSum = SUBSTRING(@PigStageTypeColSum,0,LEN(@PigStageTypeColSum));
DECLARE @SQL_WHERE NVARCHAR(MAX) = N'';
DECLARE @SQL_CONTENT NVARCHAR(MAX) = N'';

SET @SQL_WHERE += N' WHERE 1=1 ';
IF(LEN(@LineID) > 0)
BEGIN
SET @SQL_WHERE += N'
AND t1.LineID IN(SELECT * FROM [dbo].[Split] (@LineID))
';
END

SET @SQL_WHERE += N'
AND t1.StatDate BETWEEN @StartDate AND @EndDate';

--如果没有传递排序参数,默认根据数量降序
IF (@OrderColumn ='')
SET @OrderColumn='LineID ASC'

SET @SQL_CONTENT = N'
SELECT @RowCount = COUNT(C.MaterialID)
FROM (
SELECT * FROM
(
SELECT
t1.LineID, 
t1.MaterialID
FROM Material_PutOutCostStat_StatDay AS t1 WITH ( NOLOCK )
LEFT JOIN [dbo].[View_PigMGT_Material_Info] AS t2 WITH (NOLOCK) ON t2.ID = t1.MaterialID
@WHERE
) AS t1
PIVOT( SUM(t1.StatNum)
FOR PigStageID IN ('+@PigStageType+' )
) A
)C

IF( @RowCount>0)
BEGIN
;WITH t AS (
SELECT ROW_NUMBER() OVER (ORDER BY '+ @OrderColumn +' ) AS R_Number, C.*
FROM (
SELECT
LineID,
LineName,
MaterialID,
[MaterialName],
'+@PigStageTypeSelect+',
'+@PigStageTypeRowSum+' AS SumStat --汇总行
FROM
(
SELECT
t1.LineID,
t1.MaterialID ,
t2.[MaterialCode],
t1.PigStageID,
ISNULL(t1.StatNum,0) AS StatNum
FROM Material_PutOutCostStat_StatDay AS t1 WITH(NOLOCK )
LEFT JOIN [dbo].[View_PigMGT_Material_Info] AS t2 WITH(NOLOCK) ON t2.ID = t1.MaterialID
 
@WHERE
) AS t1
PIVOT( SUM(t1.StatNum) FOR PigStageID IN ('+@PigStageType+' )
) A
)C)

--添加汇总行
SELECT * INTO #table FROM t
UPDATE #table SET R_Number=R_Number+1

INSERT INTO #table
SELECT
1,
0,
''合计'',
'''',
'''',
'''',
'''',
'''',
'''',
'''',
' + @PigStageTypeColSum + ',
SUM([SumStat])
FROM #table

SELECT * FROM #table
WHERE R_Number BETWEEN @iBeginID AND @iEndID
ORDER BY R_Number ;

END
'
SET @SQL_CONTENT = REPLACE(@SQL_CONTENT, '@WHERE', @SQL_WHERE)
PRINT @SQL_CONTENT
EXECUTE sp_executesql @SQL_CONTENT,
N'@RowCount INT OUTPUT,@iBeginID INT,@iEndID INT, @LineID VARCHAR(512),@StartDate DATE,@EndDate DATE,@OrderColumn VARCHAR(512) ',
@RowCount = @RowCount OUTPUT, @iBeginID = @iBeginID, @iEndID = @iEndID,@LineID = @LineID ,
@StartDate = @StartDate, @EndDate = @EndDate,@OrderColumn = @OrderColumn;

END

查询效果如下：

页面展示如下：

 

转载于:https://www.cnblogs.com/wangshitou/p/10214411.html