The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Retrieval into new table


I'm trying to retrieve some data from a sql table. My inital data is like this:

Item 1
Item 2
Item 3
Item 4
Item 5
Item 6

I need to get the data into a new sql table in memory like this:

Item 1  Item 2  Item 3
Item 4  Item 5  Item 6

To then allow me to bind my grid to the data and get the required layout, three rows going across the screen.

Can anyone suggest a way to do this or has anyone ever done anything similar?

Many Thanks
Wednesday, May 24, 2006
Assuming that you can do arithmetic on your field somehow, use the DIV function to calculate the row number and the MOD function to calculate the column.
Steve Hirsch Send private email
Wednesday, May 24, 2006
Well, I can imagine why you would want to do that, but here is a suggestion on how to do it.

Maybe you could do it this way:

ALTER TABLE old_table ADD count INT NOT NULL auto_increment;

INSERT INTO new_table SELECT t1.col, t2.col FROM old_table as t1 JOIN old_table as t2 ON t1.count = t2.count - 1; -- etc. with second join

ALTER TABLE old_table DROP count;

To be honest: I have no idea whether this will actually work. An other suggestion is to make a small client in whatever language you are comfortable with and output the content with a new line for every third row. Most SQL servers have an import or copy function that will take a CSV file and include it in the database. (Yes boring, tedious and you won't learn anything from it.)

A third suggestion is to use some stored procedure. I'm confident that the bigger database solutions can handle this problem easily (if SQL alone can't, that is). That of course means you will have to learn stored procedures.

Finally there is the Sitepoint Database forum where a lot of smart people sit around and help other people - f. x. me today.

I hope it helps in some way
Peter Monsson Send private email
Wednesday, May 24, 2006
Hmm, maybe I misread your question. What excatly are you trying to do? Do you want to create a new table in the database or do you just want to output the table to the screen?
Peter Monsson Send private email
Wednesday, May 24, 2006
Let me try again..

I'm going to databind to a datagrid in As the datagrid cannot manage to output the data as I need it, I thought I could 'massage' the data into the correct format by creating a temp table in sql and populating it with the records. This would give me a dataset that I could then databind by datagrid to resulting in a display like so:

row1    row2    row3
row4    row5    row6

Hope this helps
Wednesday, May 24, 2006
Are you using .NET 2.0?  What about putting your data in a collection of custom objects, then binding the grid to an ObjectDataSource?
Kyralessa Send private email
Wednesday, May 24, 2006
This seems to work.  Building upon previous comments, this selects tuples where all three columns are from the same row (itemid / 3) and where they are in order (column n+1 > column n).

If there are blanks at the end, we have to handle those specially.

0|item 1
1|item 2
2|item 3
3|item 4
4|item 5
5|item 6
6|item 7

select  t1.text as col1, t2.text as col2, t3.text as col3
 from mytable t1
  left join mytable t2
      on t2.itemid / 3 = t1.itemid / 3 and t2.itemid > t1.itemid
  left join mytable t3
      on t3.itemid / 3 = t2.itemid / 3 and t3.itemid > t2.itemid
  where t1.itemid % 3 = 0 and (t2.itemid is null or t2.itemid % 3 = 1) and (t3.itemid is null or t3.itemid % 3 = 2)
order by t1.itemid

item 1|item 2|item 3
item 4|item 5|item 6
item 7|NULL|NULL

There a lot of assumptions here:  Item id should be an int, it should start at 0, it should be unique and there should be no gaps in the numbering.

Shoveling your data into a temp table with an autonumbered column would be a good way to get started.
Brian Send private email
Wednesday, May 24, 2006
You talk about SQL but don't mention what vendor's RDBMS you are using?  SQL stands for structured programming language.  There are a ton of vendors out there  and they have slighly different syntax.  (a lot don't have autoincriment, some don't have stored procedures, some have different temp tables - or don't use temp tables)

If you mean MS SQL Server then it would be good to say that and don't equate SQL with MS SQL Server.  Two very different things.
Jim Send private email
Wednesday, May 24, 2006
Yes, this is definitely doable.  There might be a fancier way, but just off the top of my head...

I'm assuming Oracle here since I'm comfortable with that, and I'm leaving the exact details as an exercise since I don't have a database in front of me to test with.

We have a table t consisting of one column c containing values v1, v2, ... vn


Now, we want to transform this into a new table t' with column c1, c2, c3 to that it looks like

c1  c2  c3
v1  v2  v3
v4  v5  v6
vn-2 vn-1 vn

Let's start...

We'll first need to get a unique sequential identifier for each row

select c, rownum seq from t

Here we're using Oracle's rownum to get an identifier and aliasing it to seq which we'll use later.

Now, we want to "kick" values over and it would be nice to assign each triplet a unique value

(case when seq mod 3 = 1 then c else null) c1,
(case when seq mod 3 = 2 then c else null) c2,
(case when seq mod 3 = 0 then c else null) c3,
ceil(seq/3) trip_id
( previous query )

Yes, I'm fuzzy on the syntax of the case and mod statements, but look them up.

This gives us...

c1    c2    c3    trip_id
v1    null  null  1
null  v2  null  1
null  null  v3    1
v4    null  null  2
null  v5    null  2
null  null  v6    2

Now, all we need to do is group by trip_id and use an aggregate function that will kick out the nulls.  Again, I don't have a manual but let's pretend that max will do it for us.

select max(c1) c1, max(c2) c2, max(c3) c3
from (previous query)
group by trip_id

And we're done.  You have the table t' that you want in your query.

Yes, I have done this and other things you shouldn't do with SQL before.
Perl Solution
Wednesday, May 24, 2006
P.S.  The SQL gymnastics I posted above were performed for my own amusment, I strongly recommend you just read the raw data into your program and populate the grid from that, an approach which seems much easier to understand and maintain.
Brian Send private email
Wednesday, May 24, 2006
Yes, you should read the original table data directly into the client and populate the grid from that.

You are trying to solve a presentation problem (displaying data in three rows) by rearranging the stored data, essentially. I have no idea why - perhaps it is a design limitation of ASP database-aware grid, or you don't know about some feature thereof that would do it - but this approach is a hydra with potential to grow endless heads that will be biting you in the backside forever. In the least opportune moments.
Thursday, May 25, 2006
Thanks everyone!

The main reason for doing the rearranging of the data in sql is that the grid can do paging and only get the records required each time. If I do the rearranging of the data on the client, I will have to retrieve all records in one go.
Does that makes sense?

Thanks again
Thursday, May 25, 2006
Does not. Retrieve only the subset you need, then populate the grid.

Confusing storage with presentation is a bad practice.
Thursday, May 25, 2006
> SQL stands for structured programming language

couldn't let that one slide ;-)

I'll second ping.  Don't screw up the database just because it's an easy way to present it the way you want.  What possible names can you come up with for the columns in this new table?  You're going to break any relations you have, and generally turn your database into a spreadsheet.  Don't do it.
bmm6o Send private email
Friday, May 26, 2006
What was that noise?  Oh wait, it's just E.F. Codd rolling over in his grave...
Friday, June 02, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz