pivot函数，行转列数据没合并是什么原因

[小野马1209]

请问下用pivot函数，行转列数据没合并是什么原因

--Leave  01事假  ，02病假
GO
Create Table hrmLeaveBill(BillNo varchar(20) Primary key ,BillDate int not Null,Name varchar(20) not null,Leave varchar(20),
                          DaysNo numeric(19,9))
Insert into hrmLeaveBill
select 'QJ20170701001','20170701','张三','01',2 Union All
select 'QJ20170701002','20170701','李四','01',1 Union All
select 'QJ20170701003','20170701','张三','02',5 Union All
select 'QJ20170801001','20170801','李四','02',3 Union All
select 'QJ20170802001','20170802','李四','01',1.5
GO


---PIVOT
SELECT Name,LEFT(BillDate,6) as '年月',isNull([01],0)as '事假',isNull([02],0) as '病假'
FROM hrmLeaveBill 
PIVOT
(
    SUM(DaysNo) for Leave in([01],[02])
    )
AA
where LEFT(BillDate,6)=201707

当前结果
Name 年月 事假 病假
张三 201707 2.000000000 0.000000000
李四 201707 1.000000000 0.000000000
张三 201707 0.000000000 5.000000000
想要的结果
Name 年月 事假 病假
张三 201707 2.000000000 5.000000000
李四 201707 1.000000000 0.000000000

[二月十六]

SELECT  Name ,
        LEFT(BillDate, 6) AS '年月' ,
        ISNULL([01], 0) AS '事假' ,
        ISNULL([02], 0) AS '病假'
FROM    ( SELECT    BillDate ,
                    Name ,
                    Leave ,
                    DaysNo
          FROM      hrmLeaveBill
        ) a PIVOT
( SUM(DaysNo) FOR Leave IN ( [01], [02] ) ) AA
WHERE   LEFT(BillDate, 6) = 201707

[听雨停了]

--测试数据
IF OBJECT_ID('tempdb..#hrmLeaveBill') IS NOT NULL 
DROP TABLE #hrmLeaveBill
Create Table #hrmLeaveBill(
	BillNo varchar(20) Primary key ,
	BillDate int not Null,
	Name varchar(20) not null,
	Leave varchar(20),
	DaysNo numeric(19,9)
)
Insert into #hrmLeaveBill
select 'QJ20170701001','20170701','张三','01',2 Union All
select 'QJ20170701002','20170701','李四','01',1 Union All
select 'QJ20170701003','20170701','张三','02',5 Union All
select 'QJ20170801001','20170801','李四','02',3 Union All
select 'QJ20170802001','20170802','李四','01',1.5
GO
--测试数据结束
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+Leave+']'  from #hrmLeaveBill for xml PATH('')),1,1,'')
SET @sql='with cte as (select left(billdate,6) as billdate,name,leave,daysno from #hrmLeaveBill)'
set @sql =@sql+'SELECT * from cte pivot(max(DaysNo)for Leave in('+@name+'))a where left(billdate,6)=''201707'''
PRINT @sql
EXEC( @sql)

billdate     name                 01                                      02
------------ -------------------- --------------------------------------- ---------------------------------------
201707       李四                   1.000000000                             NULL
201707       张三                   2.000000000                             5.000000000

(2 行受影响)

1.建议你实际情况用动态sql去写，就好比我上面一样；如果你有很多日期的话，这样就不用写太多代码； 2.你上面之所以会形成三条数据这种结果，而没有达到自己的理想结果，是因为你直接用原始数据表去操作，当计算max(DaysNo)的时候，因为你的原表还有BillNo这个字段，导致分组的时候这个字段也进去了，所以出来的结果变成了三条； 3.你可以把这个表的主键去掉然后把第三条数据的BillNo改成和第一条一样，再试试用你自己的sql去跑的话出来的结果就是理想的，也就是两条了。之所以会这样就是上面说的

[小野马1209]

引用 1 楼 sinat_28984567 的回复:

SELECT  Name ,
        LEFT(BillDate, 6) AS '年月' ,
        ISNULL([01], 0) AS '事假' ,
        ISNULL([02], 0) AS '病假'
FROM    ( SELECT    BillDate ,
                    Name ,
                    Leave ,
                    DaysNo
          FROM      hrmLeaveBill
        ) a PIVOT
( SUM(DaysNo) FOR Leave IN ( [01], [02] ) ) AA
WHERE   LEFT(BillDate, 6) = 201707

这样是什么原理，后面一定要限定表的字段吗

[二月十六]

SELECT  Name ,
        LEFT(BillDate, 6) AS '年月' ,
        ISNULL([01], 0) AS '事假' ,
        ISNULL([02], 0) AS '病假'
FROM    ( SELECT    BillDate ,
                    Name ,
                    Leave ,
                    DaysNo
          FROM      hrmLeaveBill
        ) a PIVOT
( SUM(DaysNo) FOR Leave IN ( [01], [02] ) ) AA
WHERE   LEFT(BillDate, 6) = 201707

引用 3 楼 kaijie_wu1209 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:]

SELECT  Name ,
        LEFT(BillDate, 6) AS '年月' ,
        ISNULL([01], 0) AS '事假' ,
        ISNULL([02], 0) AS '病假'
FROM    ( SELECT    BillDate ,
                    Name ,
                    Leave ,
                    DaysNo
          FROM      hrmLeaveBill
        ) a PIVOT
( SUM(DaysNo) FOR Leave IN ( [01], [02] ) ) AA
WHERE   LEFT(BillDate, 6) = 201707

这样是什么原理，后面一定要限定表的字段吗[/quote] 因为hrmLeaveBill表中海油BillNo这个字段，这个在分组的时候造成不能合并