Covert rows into columns

Question

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

Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Pred, Mar 14 '22 at 06:35

RF1991 · Answer 1 · 2022-03-14T07:21:34.237

1

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;

edited Mar 14 '22 at 07:21

answered Mar 14 '22 at 06:47

RF1991

2,037
4
8
17

Can possible to edit my query With. Currently when i run it throwing some error – Vigna Hari Karthik Mar 14 '22 at 06:59
Incorrect syntax near the keyword 'table'. – Vigna Hari Karthik Mar 14 '22 at 07:04
This filter is important "T0.RefDate <= EOMONTH('2022',monthNumber-1)" otherwise the data will be wrong – Vigna Hari Karthik Mar 14 '22 at 07:05
yes, i used that in order get the correct result – Vigna Hari Karthik Mar 14 '22 at 07:07
create month table (1,2,3,4,5,) in your database and use second query – RF1991 Mar 14 '22 at 07:15
Did it worked?? – RF1991 Mar 14 '22 at 07:58
wow i am impressed – Vigna Hari Karthik Mar 14 '22 at 08:47

score 0 · Answer 2 · answered Mar 14 '22 at 06:33

0

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://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

answered Mar 14 '22 at 06:33

Jonathan Allen

68,373
70
259
447

But i am using an With Statement along with cross join – Vigna Hari Karthik Mar 14 '22 at 06:41

Covert rows into columns

2 Answers2