Separate two data with two different columns in a row

[TheLazyPig]

I have this table...

policy name sound
A12345 rabbit awww
A12345 rabbit aaaaa
A12346 hamster wiiiii
A12346 hamster woooo
A12346 hamster weeee

And I want my table to be like this...

policy name sound1 sound2 sound3
A12345 rabbit a

http://www aaaaa

A12346 hamster wiiiii woooo weeee

 

[Mukesh Khandelwal]

Hi

This type of question is already asked in the forum. Please check the below link

https://www.tek-tips.com/viewthread.cfm?qid=1717274

https://www.foxite.com/archives/convert-row-into-column-0000298548.htm

Mukesh Khandelwal

 

[TheLazyPig]

Oopss.. okay thank you I'll check it out.

 

[TheLazyPig]

Now I'm curious about GENXTAB. I tried to search it but ended up to an image of a tablet.

 

[Mukesh Khandelwal]

you will find this class in C:\Program Files (x86)\Microsoft Visual FoxPro 9\vfpxtab.prg

Mukesh Khandelwal

 

[Olaf Doschke]

You'll find it as the cross tab wizard under Tools->Wozards-All Wizards. So that is very deeply hidden.
But you also have the pivot wizard, if you look into the Tools-Wizards.
Not sure what fits better.

I'd not generate a table like this, as you'll always be limited to 254 columns. Might be enough, but this is a case of using a spreadsheet with its Pivoting function or MSSQL Server with its pivoting data feature.

It's usually only done for reporting, and without computing any aggregate for months, for example, you can easily iterate the rows of a table and put the values in columns of a spreadsheet or word table.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[mm0000]

select 0
use tablex [b]&& change this to the actual table name. All fields sound1,sound2,sound3.... should be created in this table before running the program.[/b]
copy structure to tabley

Select distinct policy,name from tablex into cursor temp order by policy,name

select 0
USE tabley
append from DBF('temp') 
index on policy+name to tableyindex

SELECT tablex
set relation to policy+name into tabley

LOCATE

do while not eof()
   m.policy = policy
   m.name = name
   m.count = 1
   do while policy = m.policy and name = m.name AND NOT EOF()
      m.sound = sound
      xfield1 = 'sound'+allt(str(m.count))
      replace tabley->&xfield1 with m.sound
      m.count = m.count + 1
      skip
   ENDDO
enddo