SQL problem

Baz · 13 Dec 2006 at 15:05

I have a table with information in, where multiple lines refer to the same item, each line has a unique identity to it.
I want to insert 2 items from each line into a (temporary) table on one line, and have the new table only have as many columns as i need, so if i need 10 items, it only creates 10 columns, if i have 14 items, it creates 14 columns

possible or not?

rpstewart · 13 Dec 2006 at 16:18

It's do-able but it's not going to be simple because you'd need to work out each time what fields are required in the temp table and then what the field types are so that you can create the correct table.

Why does the temp table need to have a varying number of columns?

Surely it's going to be easier to create the temp table with the same structure as the base table and then only query on the 10/14/whatever columns that you need in the output.

This then raises the question of why the temp table? Can you not just query the base table straight off?

Baz · 13 Dec 2006 at 16:20

Maybe I can query the table, I am looking at Cross-Tabs at the moment, and possibly a cube, getting complicated

McMuffin · 13 Dec 2006 at 18:00

Could you not create a view??

Baz · 13 Dec 2006 at 18:03

In pratice, that is what I am trying to extract data from, I have already made a view from several tables.