Covert rows into columns-CodePudding

Below is my sql query

With Months(monthNumber) AS (SELECT 1 UNION ALL SELECT month_number   1 FROM [AndriodAppDB].dbo.T WHERE month_number < 12)
(
SELECT
 monthNumber, SUM(T0.Debit-T0.Credit) [Balance Due]
from 
Months
CROSS JOIN dbo.JDT1 T0
INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'
where  T1.CardCode ='C-SGD-0242' and T0.RefDate <= EOMONTH('2022',monthNumber-1)
group by monthNumber
)

This is my results

monthNumber Balance Due
----------- ------------
1           18176.570000
2           17110.380000
3           18832.900000
4           18832.900000
5           18832.900000
6           18832.900000
7           18832.900000
8           18832.900000
9           18832.900000
10          18832.900000
11          18832.900000
12          18832.900000

But i need output like this:

Jan      Feb      Mar      Apr      May      Jun      Jul      Aug     Sep       Oct Nov Dec 
18176.57 17110.38 18832.90 18832.90 18832.90 18832.90 18832.90 18832.90 18832.90 18832.90 18832.90 18832.90

CodePudding user response：

There is no clean way to do that in a generic way. T-SQL assumes that you know all of the column names at compile time, which is something that it can't know if the column names come from the data.

If you do know all of the column names ahead of time, you can use the PIVOT clause.

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

CodePudding user response：

use DateName(M, DateAdd(M, monthNumber, -1)) to change your number to its name (12 =>[December]) then use pivot as follows

---make your query to YOURCTE(with) is your table in use it in pivot
---with

select *
from 
(
 SELECT DateName(M, DateAdd(M, monthNumber, -1)) month1,BalanceDue from YourCTE
) src
pivot
(
  sum(BalanceDue)
  for month1 in ([January], [February], [March],[April],[May],[June],[July],
  [August],[September],[October],[November],[December])
) piv;

or more complicated

select *
from 
(
 SELECT DateName(M, DateAdd(M, monthNumber, -1)) month1,BalanceDue from ( SELECT
 monthNumber, SUM(T0.Debit-T0.Credit) [Balance Due]
from 
Months
CROSS JOIN dbo.JDT1 T0
INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'
where  T1.CardCode ='C-SGD-0242' and T0.RefDate <= EOMONTH('2022',monthNumber-1)
group by monthNumber
) yourquerytable
) src
pivot
(
  sum(BalanceDue)
  for month1 in ([January], [February], [March],[April],[May],[June],[July],
  [August],[September],[October],[November],[December])
) piv;