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

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

我们在2月底、5月底、8月底和11月底向客户支付款项。

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

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 Fiddle：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