sqlserver行转列转化实例

以下为实现了数据表行转列，并实现动态列：

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE PROC_BI_XDHPZLGL (
 @P_WLCODE VARCHAR(MAX),
 @P_JYTYPE VARCHAR(20),
 @P_BEGINDATE VARCHAR(8),
 @P_ENDDATE VARCHAR(8)
)
AS
BEGIN
--建立明细数据结果集的临时表
DECLARE @V_SQL NVARCHAR(MAX);
DECLARE @V_SQL_01 NVARCHAR(300);
DECLARE @V_SQL_02 NVARCHAR(300);
DECLARE @V_SQL_03 NVARCHAR(300);

--检验类型条件处理
 IF @P_JYTYPE=0 BEGIN SET @V_SQL_01=' and A.ZLYWBG1_LYLX=0' END
 IF @P_JYTYPE=1 BEGIN SET @V_SQL_01=' and A.ZLYWBG1_LYLX=1'  END
 IF @P_JYTYPE=2 BEGIN SET @V_SQL_01=' and A.ZLYWBG1_LYLX=2'  END
 IF @P_JYTYPE=3 BEGIN SET @V_SQL_01=' and A.ZLYWBG1_LYLX=3'  END
 IF @P_JYTYPE=9 BEGIN SET @V_SQL_01=' and A.ZLYWBG1_LYLX=9'  END
 IF @P_JYTYPE=99 BEGIN SET @V_SQL_01=' '; END
   --	0,到货检验;1,留样检验;2,复检检验;3,有效期检验;9,退货检验
--报告日期条件处理
SET @V_SQL_02=' and A.ZLYWBG1_BGRQ>='+@P_BEGINDATE;
SET @V_SQL_03=' and A.ZLYWBG1_BGRQ<='+@P_ENDDATE;
IF @P_BEGINDATE='' OR LEN(@P_BEGINDATE)=0 BEGIN set @V_SQL_02=' '  END
IF @P_ENDDATE=''   OR LEN(@P_ENDDATE)=0   BEGIN set @V_SQL_03=' '  END
--物料编号条件处理
set @P_WLCODE=''''+replace(@P_WLCODE,'','''')+'''';
set @P_WLCODE=REPLACE(@P_WLCODE,',',''',''');
--结果集
SET @V_SQL='SELECT
	A.ZLYWBG1_LSBH 流水编号,
	A.ZLYWBG1_GSID 核算单位,
	D.MaterialCode 物料编号,
	D.MaterialName 物料名称,
	A.ZLYWBG1_C1  规格,
	A.ZLYWBG1_PCH 批号,
	A.ZLYWBG1_SXRQ 有效期,
	A.ZLYWBG1_BJSL 检验数量,
	A.ZLYWBG1_C3 留样观察方案,
	A.ZLYWBG1_C4 观察周期,
	'' '' 计划考察日期,
	A.ZLYWBG1_BGRQ 考察时间,
	B.ZLYWBG2_FLBH 分录流水,
	A.ZLYWBG1_JYYJ 检验依据,
	C.ZLJYXM_XMMC  检验项目,
    B.ZLYWBG2_JYJG 检验结果,
	A.ZLYWBG1_BGRS 考察人,
	A.ZLYWBG1_FHR 复核人,
	'' '' 原始记录单号,
	A.ZLYWBG1_BGRS 检验人,
	case when A.ZLYWBG1_HGBZ=''01'' then ''合格''
	 else ''不合格'' end as  是否合格,
	case when A.ZLYWBG1_BBH=''1'' then ''是'' 
	 else ''否'' end as 是否一等品,
	A.ZLYWBG1_SJDH 报告单号,
	A.ZLYWBG1_BGRQ 报告日期,
	A.ZLYWBG1_BZ 备注
FROM
	ZLYWBG1 A
LEFT JOIN ZLYWBG2 B ON A.ZLYWBG1_LSBH = B.ZLYWBG2_LSBH
LEFT JOIN ZLJYXM  C  ON C.ZLJYXM_NM=B.ZLYWBG2_XMBH
left join Materials D ON D.MaterialID=A.ZLYWBG1_JYWL
WHERE  C.ZLJYXM_XMMC!='''' AND D.MaterialCode  in ('+@P_WLCODE+')';
SET @V_SQL=@V_SQL+@V_SQL_01+@V_SQL_02+@V_SQL_03;
PRINT(@V_SQL);

--建立临时表
declare @v_sqlstr nvarchar(max);
drop table XDHP_ZLGL;
set @v_sqlstr='select * into XDHP_ZLGL from ('+@v_sql+') as A';
exec(@v_sqlstr);

--进行列转行处理

DECLARE @sql_str NVARCHAR(MAX)
DECLARE @sql_col NVARCHAR(MAX)
DECLARE @tableName SYSNAME       --需要行转列表的表名（或者视图、临时表）
DECLARE @groupColumn SYSNAME     --分组字段
DECLARE @row2column SYSNAME      --行变列的字段
DECLARE @row2columnValue SYSNAME --行变列值的字段
SET @tableName = 'XDHP_ZLGL'
SET @groupColumn = '物料编号,物料名称,规格,批号,有效期,检验数量,留样观察方案,观察周期,计划考察日期,考察时间,检验依据,考察人,复核人,原始记录单号,检验人,是否合格,是否一等品,报告单号,报告日期,备注'
SET @row2column = '检验项目'
SET @row2columnValue = '检验结果'

--从行数据中动态获取列
SET @sql_str = N'
SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME('+@row2column+') 
    FROM '+@tableName+' GROUP BY '+@row2column
PRINT @sql_str
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
PRINT @sql_col

SET @sql_str = N'
SELECT * FROM (
    SELECT '+@groupColumn+','+@row2column+','+@row2columnValue+' FROM '+@tableName+') p PIVOT 
    (max('+@row2columnValue+') FOR '+@row2column+' IN ( '+ @sql_col +') ) AS pvt 
ORDER BY '+@groupColumn+''
PRINT (@sql_str)
EXEC (@sql_str)
END

 

行转列的核心：

SET @sql_str = N'
SELECT * FROM (
    SELECT '+@groupColumn+','+@row2column+','+@row2columnValue+' FROM '+@tableName+') p PIVOT 
    (max('+@row2columnValue+') FOR '+@row2column+' IN ( '+ @sql_col +') ) AS pvt 
ORDER BY '+@groupColumn+''
PRINT (@sql_str)
EXEC (@sql_str)

其中 

@groupColumn         ： 需要分组的字段

@row2column           ：可以进行聚合函数的字段（max，sum等）

@row2columnValue  ：需要进行转列的字段

固定搭配，初学者可以当做公式使用（在sqlserver数据库中适用）。