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

I have a function that returns the following information when called:

我有一个函数，在调用时返回以下信息：

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

output:

输出：

    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

We do payouts to our clients at the end of February, May, August and November.

我们在2月，5月，8月和11月底为客户付款。

So what I want to achieve is to have each month have its own date as shown below:

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

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

How can I split the data set to reflect as shown above?

如何分割数据集以反映如上所示？

I don't really know how can this be tackled, Anyone with any idea?

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

2 个解决方案

#1

Use PIVOT

使用PIVOT

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

Output

产量

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

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

#2

Can enter the function result into temporary table . Here example table name : SOF_Pcode

可以将函数结果输入临时表。这里的示例表名称为：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

#1