visual foxpro query

Hai,

i have a table with the following records.

month,dealercode,branch,netqty

can anybody kindly give me SQL Command which will sum netqty group by dlrcod and month but for each dealer monthqty should appear vertically.

select dlrcod,branch,sum(netqty) from <table name> group by dlrcod,month

with this query i get month totals horizontally but i want months to appear vertically.

if any body can kindly give me a query, i would be greatful

eg.

mnth delaer code branch netqty

january a0001 blr 1000

april a0001 blr 500

feb a0001 blr 300

may b0001 blr 400

june b0001 blr 300

january a0001 blr 200

march c0001 blr 100

april d0001 blr 200

may d0001 blr 100

may e0001 blr 50

i want an output table in this format. can any body kindly give me the sql command to do that.

dlrcod branch january feb march april may june

a0001 blr 1200 300 500

a0001 blr 200

b0001 blr 400 300

c0001 blr 100

d0001 blr 200 100

e0001 blr 50

Thanks

(Lakshminarayana R)

Visual FoxPro2

Hi,

Please refer Cross-tab wizard... It'd help your cause.

Regards,

Markish

u cant do in for 1 sql .

you need more coding.

select month, dlrcod,branch,sum(netqty) as netqty from <table name> group by dlrcod,month into cursor ctemp

alter table add jan until june with char data type.

create a new table with all data type u wan (the output u want)

sele ctemp

go top

do while !eof()

sele new table

append blank

selec ctemp

do case

case ctemp.month = "JANUARY"

repl januaury with ctemp.netqty

...... and so on ..

skip

enddo

HTH

SELECT dlrcod + " " + branch as dlrbranch, month, netqty from (source) into cursor temp group by 1,2,3
DO (_GENXTAB)

If the month is a date value, you can use CMonth() for that column, or a UDF that returns the month name.

The main thing is to run a query with ONE column for the values down the left, ONE column for the values across the top, and ONE column for the intersection data. Then the Crosstab program that ships with VFP will finish the job for you.

Sir,

Thank You very much. but if i have another table which will have distint(month) from the input table. now can i join input table with the new distinct month table.

eg.

select distinct(month) from dlrtable into dbf mnthuniq.dbf

then can i join dlrtable and mnthuniq

something like

sele a.dlrcod,a.branch,mnth,sum(a.qty) as 'janqty',b.mnth from dlrtable a,mnthuniq b where a.mnth = b.mnth

like that with one query can i get all the months value