How can I split values returned by sql query into multiple columns in SQL SERVER?

Question

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.

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?

Matt · Accepted Answer · 2017-07-27 09:23:35Z

2

Use 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

answered Jul 27, 2017 at 9:23

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adikari Nadeesha · Accepted Answer · 2017-07-27 10:13:53Z

1

Can enter the function result into temporary table . Here example table name : 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

answered Jul 27, 2017 at 10:13

Adikari Nadeesha

3123 silver badges13 bronze badges

Collectives™ on Stack Overflow

How can I split values returned by sql query into multiple columns in SQL SERVER?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related