Select with pivot
-
Hi, everyone. I'm trying to recover data from SQL Server using the pivot operator. My command is:
select isnull([1], 0) as Janeiro,
isnull([2], 0) as Fevereiro,
isnull([3], 0) as Março,
isnull([4], 0) as Abril,
isnull([5], 0) as Maio,
isnull([6], 0) as Junho,
isnull([7], 0) as Julho,
isnull([8], 0) as Agosto,
isnull([9], 0) as Setembro,
isnull([10], 0) as Outubro,
isnull([11], 0) as Novembro,
isnull([12], 0) as Dezembro
from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP, PAGAMENTO P
where PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO AND P.SUPA_NA_CODIGO = 5) Tab1
pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1The way it is above it works fine. Now I need to multiply the sum by a factor, say 0.95. I tried the following: pivot (0.95 * sum(PAPA_RE_VALORPAGAMENTO) ... pivot (sum(0.95 * PAPA_RE_VALORPAGAMENTO) ... 0.95 * pivot (sum(PAPA_RE_VALORPAGAMENTO) ... None of these worked. Can anyone help me? Which is the right way? Thanks.
-
Hi, everyone. I'm trying to recover data from SQL Server using the pivot operator. My command is:
select isnull([1], 0) as Janeiro,
isnull([2], 0) as Fevereiro,
isnull([3], 0) as Março,
isnull([4], 0) as Abril,
isnull([5], 0) as Maio,
isnull([6], 0) as Junho,
isnull([7], 0) as Julho,
isnull([8], 0) as Agosto,
isnull([9], 0) as Setembro,
isnull([10], 0) as Outubro,
isnull([11], 0) as Novembro,
isnull([12], 0) as Dezembro
from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP, PAGAMENTO P
where PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO AND P.SUPA_NA_CODIGO = 5) Tab1
pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1The way it is above it works fine. Now I need to multiply the sum by a factor, say 0.95. I tried the following: pivot (0.95 * sum(PAPA_RE_VALORPAGAMENTO) ... pivot (sum(0.95 * PAPA_RE_VALORPAGAMENTO) ... 0.95 * pivot (sum(PAPA_RE_VALORPAGAMENTO) ... None of these worked. Can anyone help me? Which is the right way? Thanks.
"None of these worked" because the syntax for PIVOT[^] clearly states
Quote:
PIVOT ( ()
So just put the results of the PIVOT into a sub-query or a Common Table Expression or temporary table or a table variable, then manipulate that data. E.g.
;with cte as
(
select isnull([1], 0) as Janeiro,
isnull([2], 0) as Fevereiro,
isnull([3], 0) as Março,
isnull([4], 0) as Abril,
isnull([5], 0) as Maio,
isnull([6], 0) as Junho,
isnull([7], 0) as Julho,
isnull([8], 0) as Agosto,
isnull([9], 0) as Setembro,
isnull([10], 0) as Outubro,
isnull([11], 0) as Novembro,
isnull([12], 0) as Dezembro
from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP
inner join PAGAMENTO P on PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO
where P.SUPA_NA_CODIGO = 5) Tab1
pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1
)
select 0.95 * Janeiro,0.95 * Fevereiro,0.95 * Março,0.95 * Abril,
0.95 * Maio,0.95 * Junho,0.95 * Julho,0.95 * Agosto,
0.95 * Setembro,0.95 * Outubro,0.95 * Novembro,0.95 * Dezembro
from cte;Couple of other points to note - I've changed the JOIN to use an ON clause rather than defining the join using the WHERE clause. Your style is quite old-fashioned and prevents you from using OUTER joins - It's a lot easier to answer questions like this if you supply the table schemas and some sample data along with your expected results. And always be specific - "None of these worked" is not helpful. "I get an error reported 'Incorrect syntax near '0.95'.' might have got you an answer quicker
-
"None of these worked" because the syntax for PIVOT[^] clearly states
Quote:
PIVOT ( ()
So just put the results of the PIVOT into a sub-query or a Common Table Expression or temporary table or a table variable, then manipulate that data. E.g.
;with cte as
(
select isnull([1], 0) as Janeiro,
isnull([2], 0) as Fevereiro,
isnull([3], 0) as Março,
isnull([4], 0) as Abril,
isnull([5], 0) as Maio,
isnull([6], 0) as Junho,
isnull([7], 0) as Julho,
isnull([8], 0) as Agosto,
isnull([9], 0) as Setembro,
isnull([10], 0) as Outubro,
isnull([11], 0) as Novembro,
isnull([12], 0) as Dezembro
from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP
inner join PAGAMENTO P on PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO
where P.SUPA_NA_CODIGO = 5) Tab1
pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1
)
select 0.95 * Janeiro,0.95 * Fevereiro,0.95 * Março,0.95 * Abril,
0.95 * Maio,0.95 * Junho,0.95 * Julho,0.95 * Agosto,
0.95 * Setembro,0.95 * Outubro,0.95 * Novembro,0.95 * Dezembro
from cte;Couple of other points to note - I've changed the JOIN to use an ON clause rather than defining the join using the WHERE clause. Your style is quite old-fashioned and prevents you from using OUTER joins - It's a lot easier to answer questions like this if you supply the table schemas and some sample data along with your expected results. And always be specific - "None of these worked" is not helpful. "I get an error reported 'Incorrect syntax near '0.95'.' might have got you an answer quicker
Hi, as you act on a column within the select "......" statements, you must add "group by" and the column involved. It's from Sql engine, in a step : it gather the columns, in a late step : a rollback is made to apply the 0.95* operation on all selected columns. so the "group by" recall all the 'columns' for sorting the "whole gathered" one by one. It's really inner mechanicals SqlEngine work. Remenber : when operation on columns in the 'select' : as "label" / maths .. , string works add "group by" with the column required.
-
Hi, as you act on a column within the select "......" statements, you must add "group by" and the column involved. It's from Sql engine, in a step : it gather the columns, in a late step : a rollback is made to apply the 0.95* operation on all selected columns. so the "group by" recall all the 'columns' for sorting the "whole gathered" one by one. It's really inner mechanicals SqlEngine work. Remenber : when operation on columns in the 'select' : as "label" / maths .. , string works add "group by" with the column required.
-
I'm really not following this at all. Perhaps if you make the amendments you are speaking about and post that code here I might understand it better? Or if you have a link to the specific documentation that might help?
one other error I see : from -->"tables required"<-- where column-n =<.. ( sub request ). add as fix the table needed for the first query ( the first part ) I think you have to go on taxes early , insert into tax_tab (n_amount, paid) values amount=n_amount , 0.95*n_amount ;;
-
one other error I see : from -->"tables required"<-- where column-n =<.. ( sub request ). add as fix the table needed for the first query ( the first part ) I think you have to go on taxes early , insert into tax_tab (n_amount, paid) values amount=n_amount , 0.95*n_amount ;;