April 14, 2018 at 9:53 am
All...
This is the first time that I have tried a PIVOT... I followed the syntax from https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx exactly as shown in the examples... The problem is that the field ACCOUNTNUM shows as an ERROR... I have tried using an ALIAS for the field but still it returns as an ERROR...
What am I missing
SELECT ACCOUNTNUM, [0] AS DEBIT_TRANS, [1] AS CREDIT_TRANS
FROM
(
SELECT ACCOUNTNUM, CREDITING, AMOUNTMST
from [dbo].[LEDGERTRANS]
where ACCOUNTNUM between '400000' and '999999'
and transdate between cast('2015-02-22' as date)
and cast('2015-03-28' as date)
and POSTING != '19'
) L
PIVOT
(
SUM(AMOUNTMST)
FOR ACCOUNTNUM IN
( [0], [1] )
) AS pvt
ORDER BY pvt.ACCOUNTNUM;
April 14, 2018 at 11:23 am
I was using the incorrect Column for the PIVOT assignment...
SELECT ACCOUNTNUM, [0] AS DEBIT_TRANS, [1] AS CREDIT_TRANS
FROM
(
SELECT ACCOUNTNUM, CREDITING, AMOUNTMST
from [dbo].[LEDGERTRANS]
where ACCOUNTNUM between '400000' and '999999'
and transdate between cast('2015-02-22' as date)
and cast('2015-03-28' as date)
and POSTING != '19'
) L
PIVOT
(
SUM(AMOUNTMST)
FOR CREDITING IN
( [0], [1] )
) AS pvt
) p
ORDER BY p.ACCOUNTNUM;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply