PIVOT HELP required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT HELP required

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 03:16:12

SELECT st_code, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
FROM (
SELECT st_code, MONTH(do_date) as do_date, SUM(quantity-qtt_out) as balance
FROM st_trx)a
PIVOT(SUM(balance) FOR do_date IN('1','2','3','4','5','6','7','8','9','10','11','12'))AS pvt
order by st_code

i wanna have the result in...

st_code 1 2 3 4 5 6 7 8 9 10 11 12
sto1 0 2 8 47 45 8 45 12 48 45 12 48
sto2 7 456 46 456 64 56 4 4 8 126 6
why got error??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 03:32:23

Where is the GROUP BY statement in the derived table a?
Or drop the SUM thingy in the derived table a and just calculate the difference.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 03:34:23

[code]SELECT st_code,
[1],
[2],
[3],
[4],
[5],
[6],
[7],

,
[9],
[10],
[11],
[12]
FROM (
SELECT st_code,
MONTH(do_date) as do_date,
SUM(quantity-qtt_out) as balance
FROM st_trx
GROUP BY st_code,
MONTH(do_date)
) AS a
PIVOT (
SUM(a.Balance)
FOR do_date IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12')
) AS pvt
ORDER BY st_code[/code]

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 03:35:28

SELECT st_code, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
FROM (
SELECT st_code, MONTH(do_date) as do_date, (quantity-qtt_out) as balance
FROM st_trx
GROUP BY st_code, do_date)a
PIVOT(SUM(balance)FOR do_date IN('1','2','3','4','5','6','7','8','9','1','11','12'))AS pvt
order by st_code
do u mean liddat? however the error prompt at
-----------------------------------
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '1'.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 03:39:32

the changes u made also same error like mine >"<
------------------------------------------
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '1'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 03:58:09

FOR do_date IN ([1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12])

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 03:59:08

anyone?

waterduck
Aged Yak Warrior

982 Posts