pivot data in a table

[Guest_imported]

I need to create a second table from a table. Table 1 has multiple rows for each product in column 1 and non-numeric data in column 2. Table 2 needs to have 1 row for each distinct value in column 1 and be followed the first 10 values found from column 2. I have placed an example below.

Can I do it in ANSI SQL?

Table 1

Prod# value
A a
A b
A c
A d
B a
B c
B d
B e

Table 2

Prod# val1 val2 val3 val4 val5 . . . . val10
A a b c d . . . . . . .
B a c d e . . . . . . .
[sig][/sig]

 

[karluk]

I can't think of any way to do this in a single sql statement. However you could probably put something together that would do one column at a time.

insert into table2
(select prod#, min(value)
,null,null,null,null,null,null,null,null
from table1 group by prod#)

update table2
set val2=(select min(value) from table1
where table1.prod#=table2.prod#
and table1.value>table2.val1)

There would be eight similar update statements to populate val3 through val10.

Take this suggestion for what it's worth. I haven't tested it, and there is no provision for dealing with duplicates in table1.value. [sig][/sig]