行列转换

可以用select代替exec看到具体数据库具体执行了什么语句

思路有点特别是值与值进行比对，不是变量对值

declare
@sql as varchar(1000),@sql1 as varchar(1000),
    @i_Process          as NVARCHAR(20),
    @i_WD_CD            as NVARCHAR(20),
    @i_EMP_NM           as NVARCHAR(20)
begin
select @sql=ISNULL(@sql+'],[','')+WD_ITEM_CD FROM TB_WD_QA_ITEM GROUP BY WD_ITEM_CD;
select @sql1=ISNULL(@sql1+'","','')+WD_ITEM_CD FROM TB_WD_QA_ITEM GROUP BY WD_ITEM_CD;
set @sql1='"'+@sql1+'"'
select @sql1
set @sql='['+@sql+']';
  SET @i_Process ='ALL';
  SET @i_WD_CD='';
  SET @i_EMP_NM='刘';
select @sql

exec ('select * from (
select A.WD_ST_CD,D.EMP_NM,A.WD_CD,A.Prosition,A.Thickness,A.FNO,A.PNO,A.RANGE,B.WD_CRT_RANG,B.WD_ITEM_CD,A.WPQR_NO,CONVERT(varchar(10),A.CERT_DATE,120) CERT_DATE,
 E.CODE_CNAME
 from TB_WD_APLLY A,TB_WD_RESULT B,TB_WD_QA_ITEM C,TB_EMP D,TB_CODE_MST E
 WHERE A.WD_ST_CD=B.WD_ST_CD AND A.CRT_NO=B.CRT_NO AND A.EMP_CD=B.EMP_CD
 AND B.WD_ST_CD=C.WD_ST_CD AND B.WD_ITEM_CD=C.WD_ITEM_CD
 AND A.EMP_CD=D.EMP_CD and  E.CODE_NO=A.CERT_GBN AND
  E.AFF_DVN=''QM'' AND E.CODE_DVN=''008'' AND E.CODE_NO<>''001''
   AND   A.WD_ST_CD =CASE '''+@i_Process+''' when ''ALL'' THEN A.WD_ST_CD
                 ELSE '''+@i_Process +'''
                 END
  AND B.WD_CD = CASE '''+@i_WD_CD +''' WHEN NULL THEN B.WD_CD
                               WHEN '''' THEN B.WD_CD
                               ELSE  '''+@i_WD_CD+'''
                               END
  AND   D.EMP_NM LIKE CASE  WHEN '''+@i_EMP_NM+''' = NULL THEN   D.EMP_NM
                       WHEN '''+@i_EMP_NM+''' = '''' THEN D.EMP_NM
                       ELSE  ''%'''+ '+''' + '' + @i_EMP_NM+ '''+'+'' +'''%''
                        END) p
    PIVOT(
       MAX(WD_CRT_RANG)
       FOR WD_ITEM_CD IN ('+@sql+')
          ) as Q')
         end        

     可以参考下这个代码