Turning column stored data into one row

[blom0344]

Dear all,

I'm trying to work out a procedure to create a concatenated string from an x (1< x< 10) number of rows which contain a host of options related to products.

The source-table looks like this:

ITEM OPTION
**** ******
A Blue
A Long
A Cheap
B Red
B Short
B Expensive
B $45

An item can have up to 10 options, with a minimum of 1

I need as output something like this:

ITEM STRING
**** ******
A Blue,Long,Cheap
B Red,Short,Expensive,$45

Attempts with SQL have stranded, because performance was terrible (table is quite large). I did find some VBA code that does the trick, but then just for 1 item. I need to transform the entire table.

Anyone any idea how to solve this in a VBA module??

Thanx T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Mongooses]

You can do it quite easily by opening a recordset and order by Item. Once you have an ordered recorset - you loop through the data while current item = previous item and concatenate the option field into a temp.
something like:
prevItem = rs.Fields(&quot;Item&quot.Value
rs.MoveNext
Do while Not rs.EOF
Do While preItem = rs.Fields(&quot;Item&quot.Value
strTemp = strTemp & &quot;,&quot; & rs.Fields(&quot;Option&quot.Value
prevItem = rs.Fields(&quot;Item&quot.Value
rs.MoveNext
Loop
Write data somewhere
prevItem = rs.Fields(&quot;Item&quot.Value
Loop

There's probably a little more logic (i.e. need to write the last record after EOF is hit etc) but this should get you started.

HTH,
JC

 

[MichaelRed]

Have you tried to use Crosstab queries or (New w/ Ms. A. 2K) the Piviot Table?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[blom0344]

Hello Mongooses / Michael Red,

A crosstabquery seems to be not in order, since there is no field you can set as a column in the structure. Anyway I want the output as a new physical table to work on, not as a query result.

The loop thing looks more promising, but since my knowledge of VBA is very limited: How do I create a module out of it?
Can I create it as a function? How about that temp table?
What do you mean by &quot; Write data somewhere&quot;.

Some additional info will be mightily appreciated! T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com