如何使用透视查询使用自定义显示

Question

我目前有一个使用PIVOT生成的查询，生成的表如下：

Name |Total |01-10-2014  |02-10-2014    
dd   |500   | 300        |200    
bb   |400   | 200        |200    
ff   |150   | 100        |50 

查询类似于

select * from (
   select 
      COALESCE(RNAME, 'NO NAME') as Name,
      Total,
      CONVERT(varchar(10), date1,105) as Date,
      BILLAMT 
   from #temp 
   where date1 >= '2014-10-01' and date1 <= '2014-10-02'
) as D 
PIVOT(SUM(BILLAMT) for Date in ([01-10-2014],[02-10-2014])) as P 

我需要CCD_ 1。以及右端的总和。。任何帮助都将不胜感激。。。

Answer 1

如果你知道列列表，那么试试这个，否则你需要将其更改为动态枢轴。

SELECT Name,
       [01-10-2014],
       [02-10-2014],
       Total
FROM   (SELECT COALESCE(RNAME, 'NO NAME')       AS Name,
               Total,
               CONVERT(VARCHAR(10), date1, 105) AS Date,
               BILLAMT
        FROM   #temp
        WHERE  date1 >= '2014-10-01'
               AND date1 <= '2014-10-02') AS D
       PIVOT(Sum(BILLAMT)
            FOR Date IN ([01-10-2014],
                         [02-10-2014])) AS P
ORDER  BY Name