January 22, 2010 at 1:08 am
i have this select query, all rows are from a unique table
SELECT [IDCLIENTE],[NOMBRECLIENTE],[MONEDA], SUM([CANTIDAD]*[PRECIO1]) AS TOTAL
FROM [ordenesSQL].[dbo].[ORDENES]
WHERE [IDCLIENTE] IN(51,651) AND [FECHANUEVA] BETWEEN '20091231' AND '20100131'
GROUP BY [IDCLIENTE],[NOMBRECLIENTE],[MONEDA]
ORDER BY [NOMBRECLIENTE], [MONEDA]
GO
and i have this result:
IDCLIENTE--------NOMBRECLIENTE----------------------MONEDA----TOTAL
51----------------CIRPRO DE DELICIAS, S.A. DE C.V.---- DOLARES--- 2468.1
51----------------CIRPRO DE DELICIAS, S.A. DE C.V.---- PESOS----- 1295
651---------------FPG MEXICO S DE RL DE CV-----------DOLARES--- 379
651---------------FPG MEXICO S DE RL DE CV-----------PESOS------430
and i want this result:
IDCLIENTE--------NOMBRECLIENTE----------------------PESOS----DOLARES
51----------------CIRPRO DE DELICIAS, S.A. DE C.V.----1295------2468.1
651---------------FPG MEXICO S DE RL DE CV-----------430-------379
and if is possible the total of PESOS and DOLARES at the end
January 22, 2010 at 1:27 am
January 22, 2010 at 1:54 am
OK IT WORKS THIS WAY THANKS A LOT MAN:
USE ordenesSQL
SELECT [NOMBRECLIENTE], [PESOS] AS PESOS, [DOLARES] AS DOLARES
FROM
(SELECT [NOMBRECLIENTE], [MONEDA], [PRECIO1]
FROM [ORDENES] WHERE [IDCLIENTE] IN(51,651) AND [FECHANUEVA] BETWEEN '20091231' AND '20100131') ps
PIVOT
(
SUM ([PRECIO1])
FOR [MONEDA] IN
( [PESOS], [DOLARES])
) AS pvt
, BUT I NEED NO MULTIPLY IN THE SUM OPERATION LIKE THIS SUM ([CANTIDAD]*[PRECIO1])
AND I GET ERRORS EVERYWHERE A LITTLE MORE HELP PLEASE! 😀
January 22, 2010 at 2:36 am
OK MAN IT FINALY WORKS LIKE THIS: 😀
USE ordenesSQL
SELECT [IDCLIENTE], [NOMBRECLIENTE], [PESOS] AS PESOS, [DOLARES] AS DOLARES
FROM
(SELECT [IDCLIENTE], [NOMBRECLIENTE], [MONEDA], SUM([CANTIDAD]*[PRECIO1]) AS TOTAL
FROM [ORDENES] WHERE [IDCLIENTE] IN(51,651) AND [FECHANUEVA] BETWEEN '20091231' AND '20100131' GROUP BY [IDCLIENTE],[NOMBRECLIENTE],[MONEDA]) AS TEMP
PIVOT
(
SUM ([TOTAL])
FOR [MONEDA] IN
( [PESOS], [DOLARES])
) AS PVT
ORDER BY [NOMBRECLIENTE] ASC
THANK YOU, YOUR EXAMPLE WAS VERY HELPFULL
January 22, 2010 at 2:37 am
+1 to you for working it out for yourself. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply