如何将SQL查询返回的值拆分为SQL SERVER中的多个列？

Question

我有调用时返回以下信息的功能：

select * from functionName(null, '1 jan 2016', '30 dec 2016') 

输出：

pcode  PayoutDate 

    100   2016-02-28 00:00:00:000 
    100   2016-05-31 00:00:00:000 
    100   3016-08-31 00:00:00:000 
    100   3016-11-30 00:00:00:000 
    103   2016-02-28 00:00:00:000 
    103   2016-05-31 00:00:00:000 
    103   3016-08-31 00:00:00:000 
    103   3016-11-30 00:00:00:000 

我们在二月，五月，八月和十一月底做支付给我们的客户。

所以我想实现是有每个月都有自己的日期，如下图所示：

pcode May      August    November 

100 2016-05-31 00:00:00:000 3016-08-31 00:00:00:000 3016-11-30 00:00:00:000 
103 2016-05-31 00:00:00:000 3016-08-31 00:00:00:000 3016-11-30 00:00:00:000 

我如何拆分数据集，以反映如上图所示？

我真的不知道如何解决这个问题，任何人有任何想法？

Answer 1

使用PIVOT

SELECT * FROM 
(SELECT pcode, datename(month, PayoutDate) AS Month, PayoutDate FROM yourtable) a 
PIVOT 
(MIN(PayoutDate) FOR Month IN ([May], [August], [November]))b 

输出

pcode May     August    November 
100 2016-05-31T00:00:00Z 3016-08-31T00:00:00Z 3016-11-30T00:00:00Z 
103 2016-05-31T00:00:00Z 3016-08-31T00:00:00Z 3016-11-30T00:00:00Z 

SQL小提琴：http://sqlfiddle.com/#!6/9ed49/11/0

Answer 2

可以进入功能结果到临时表。 这里的示例表名称：SOF_Pcode

select distinct pcode , (select P1.PayoutDate from SOF_Pcode P1 where P1.pcode =P.pcode and DATEPART(month,P1.PayoutDate) =5) As May 
, (select P1.PayoutDate from SOF_Pcode P1 where P1.pcode =P.pcode and DATEPART(month,P1.PayoutDate) =8) As August 
    , (select P1.PayoutDate from SOF_Pcode P1 where P1.pcode =P.pcode and DATEPART(month,P1.PayoutDate) =11) As November from SOF_Pcode P