关于动态列传行的问题初步认知

对于一枚菜鸟来讲，在sql中实现动态列传行是比较艰难的。不过经过一番研究，终于有了结果。首先是要对存储过程有比较好的认识，其次是对sqlserver中的相关函数有一定的了解、对如何使用 PIVOT 有比较好的认识，那么接下来就可以展示这几天的成果了：

 DECLARE
   @s VARCHAR (8000) SELECT
    @s = ISNULL(@s + ',', '') + QUOTENAME(code)
   FROM
    (
     SELECT
      ap.code
     FROM
      template_mapping_param tmp
     INNER JOIN analysis_template AT ON [at].code = 'SPITemplate'
     AND [at].id = tmp.template_id
     LEFT JOIN analysis_param ap ON tmp.param_id = ap.id
    ) AS A
    declare @pageIndex varchar(20)
    declare @pageSize varchar(20)
    set @pageIndex=#{pageIndex}-1
    set @pageSize=#{pageSize}
    DECLARE
     @SQL VARCHAR (4000)
    SET @SQL = '
  select * from
    (
    SELECT
      line.guid,
      ap.code,
       ad1.value
    FROM
      saorderline line
    INNER JOIN saorderhead head ON line.valid = 1
    AND head.valid = 1
    AND line.headguid = head.guid
    LEFT JOIN billrelation b ON line.guid = b.slineid AND b.dtype = ''SPN'' and b.valid = 1
    LEFT JOIN sample_analysis_bill sab  ON sab.source_id = b.dheadid and sab.valid =1
    LEFT JOIN analysis_detail ad1 ON ad1.bill_id = sab.id and ad1.valid = 1
    LEFT JOIN analysis_param ap on ap.id = ad1.param_id
    where sab.id is not null
    )AS A PIVOT (
     MAX (VALUE) FOR code IN (' +@s + ')
      ) AS T
  left join
   (SELECT
   line.qty predictCount,
    line.recqty finishCount,
  b.cusname prodEntName,
  c.matname wasteName,
  c.matcode wasteCode,
        CONVERT(varchar(10) , head.datef, 121)datef,
        CONVERT(varchar(10) , head.datet, 121)datet,
  d.name sname,
  line.guid mguid,line.package_type ,
   CASE
  WHEN line.package_type IS NULL
  OR line.package_type = ''ALL'' THEN
   ''不限''
  ELSE
 (
  SELECT
   name
  FROM
   fwdictionary
  WHERE
   code = line.package_type
  AND dictionaryTypeCode = ''PACKTYPE''
 )
 END packageTypeName
   from saorderline line
  LEFT JOIN saorderhead head on head.guid = line.headguid and line.valid=1 and head.valid=1
  LEFT JOIN basecustomer b ON head.cusid = b.guid AND b.valid=1
  LEFT JOIN basematerial c ON line.matid = c.guid AND c.valid = 1
  LEFT JOIN basedealtype d ON line.dealtypeid = d.guid AND d.valid = 1
  ) F on T.guid = F.mguid WHERE
        <if test="startTime != null and startTime != '' ">
            F.datef <![CDATA[ >= ]]> '''+#{startTime}+''' and
        </if>
        1=1
  order by F.mguid
  OFFSET