Really stuck in SQL to populate result

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 2000 Forums

SQL Server Development (2000)

Really stuck in SQL to populate result

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-08-01 : 07:47:16

I'm using SQL Server 2000
My table as follow,
tblTransSales
TransNo | SubBnd | InvYear | InvMonth | Amt
----------------------------------------------------
1 | VMI | 2007 | 1 | 23.00
2 | VMI | 2007 | 1 | 38.88
....
....
90 | KUI | 2007 | 2 | 22.43
91 | KUI | 2007 | 6 | 12.90
....
....
203 | JIL | 2007 | 1 | 32.90
204 | JIL | 2007 | 2 | 12.80
205 | JIL | 2007 | 1 | 11.40
....
....
*InvYear is year. Example is 2007,2008,2009 and so on...
*InvMonth is month. Example 1 is Jan, 2 is Feb, 3 is Mac, 4 is Apr and so on..
*Amt is amount

So far,i've these sql
select SubBnd,InvYear,InvMonth,sum(Amt) as Amt
from tblTransSales
group by SubBnd
order by SubBnd,InvYear,InvMonth

the result as follow
SubBnd | InvYear | InvMonth | Amt
------------------------------------------
VMI | 2007 | 1 | 74600
VMI | 2007 | 2 | 28900
VMI | 2007 | 3 | 79304
.....
.....
KUI | 2007 | 1 | 38700
KUI | 2007 | 2 | 65200
....
....
JIL | 2007 | 1 | 38009
JIL | 2007 | 2 | 47900
JIL | 2007 | 3 | 27699
...
...
*From above result, it's easy to know In Feb 2007, what the total amount for KUI brand.

I'm really stuck to adjust my query to populate the result as below format,
SubBnd | Jan-2007 | Feb-2007 | Mac-2007 | Apr-2007 | May-2007 | Jun-2007 | July-2007 | .... | .... | Jan-2008 | Feb-2008 | Mac-2008 | ..... | Dec-2008
--------------------------------------------------------------------------------------------------------------------------------------------------------------
VMI | 74600 | 28900 | 79304 | .... | .... | .... | ... | .... | .... | .... | .... | ..... | .... | .... | ..... | ...
KUI | 38700 | 65200 | ..... | .... | .... | .... | ... | .... | .... | .... | .... | ..... | .... | .... | .... | ...
JIL | 38009 | 47900 | 27699 | .... | .... | .... | ... | .... | .... | .... | .... | ..... | .... | .... | ... | ....
....
....

Please help me :(.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-01 : 10:13:50

You must use a PIVOT or CROSSTAB query.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 05:49:20

[code]SELECT SubBnd,
SUM(CASE WHEN InvYear=2007 AND InvMonth=1 THEN Amt ELSE 0 END) AS [Jan-2007],
SUM(CASE WHEN InvYear=2007 AND InvMonth=2 THEN Amt ELSE 0 END) AS [Feb-2007],
SUM(CASE WHEN InvYear=2007 AND InvMonth=3 THEN Amt ELSE 0 END) AS [Mar-2007],
SUM(CASE WHEN InvYear=2007 AND InvMonth=4 THEN Amt ELSE 0 END) AS [Apr-2007],
...
SUM(CASE WHEN InvYear=2007 AND InvMonth=12 THEN Amt ELSE 0 END) AS [Dec-2007],
SUM(CASE WHEN InvYear=2008 AND InvMonth=1 THEN Amt ELSE 0 END) AS [Jan-2008],
SUM(CASE WHEN InvYear=2008 AND InvMonth=2 THEN Amt ELSE 0 END) AS [Feb-2008],
SUM(CASE WHEN InvYear=2008 AND InvMonth=3 THEN Amt ELSE 0 END) AS [Mar-2008],
....
SUM(CASE WHEN InvYear=2008 AND InvMonth=12 THEN Amt ELSE 0 END) AS [Dec-2008]
FROM YourTable
GROUP BY SubBnd[/code]

if your years are dynamic then you may need to use dynamic sql.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-08-02 : 06:09:51

tq very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources