Transform rows to columns with Null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Transform rows to columns with Null

Author

Topic

thiyait
Yak Posting Veteran

70 Posts

Posted - 2014-04-23 : 07:57:28

Hi everyone,

I want to transfer the data cloumn to row and result which i get is through below query

Select month(PL.CREATEDDATETIME) as Months,count(PT.PURCHID) as opernorder from purchtable PT where PL.DATAAREAID = 'FPC' and (PL.CREATEDDATETIME >= '2010-01-31' and PL.CREATEDDATETIME <= '2010-12-31') group by month(PL.CREATEDDATETIME)

Month Pos
1 10
2 25
3 45
7 90
12 99

i want list the columns in row fashion and whereever month not available with Null values(later i will insert in to table in the below fashion)

10,25,45,NULL,NULL,NULL,90,NULL,NULL,NULL,NULL,99

Could you please someone help to acheive this.

Thanks in advance

-Thiya.

thiyait
Yak Posting Veteran

70 Posts

Posted - 2014-04-23 : 08:51:03

Have resolved with following query

SELECT [1] AS [January],[2] AS [February],
[3] AS [March],[4] AS [April],[5] AS [May],[6] AS [June],
[7] AS [July],

AS [August],[9] AS [September],
[10] AS [October], [11] AS [November],[12] AS [December]
FROM
(
Select month(PL.CREATEDDATETIME) as Months,count(PT.PURCHID) as opernorder from purchtable PT where PL.DATAAREAID = 'FPC' and (PL.CREATEDDATETIME >= '2010-01-31' and PL.CREATEDDATETIME <= '2010-12-31') group by month(PL.CREATEDDATETIME)

) as BaseQuery

PIVOT(SUM(opernorder) FOR MonthsIN ([1],[2],[3],[4],[5],
[6],[7],

,[9],[10],[11],[12])) AS Pivot

-thiya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources