Difficult Data Manipulation

Results 1 to 3 of 3

Thread: Difficult Data Manipulation

  1. #1
    Join Date
    Dec 1969

    Default Difficult Data Manipulation

    Hello,<BR> I need to do a seemingly difficult result query from a sequentially numbered table In SQL 2000. I&#039;ll provide an example but here&#039;s what needs to be done. <BR> I need to split a table in half and then reorder them 1 from each half, incrementing until completely rebuilt. Here&#039;s the scenario:<BR><BR>Say I have a recordset of 100 records. The first half would be 1 to 50 and second half 51-100. I would need to create the following recordset<BR>1<BR>51<BR>2<BR>52<BR>3<BR>53<BR>etc. ...<BR><BR>I&#039;m sure I could do this in a very inefficient way by creating temp. multiple recordsets then manipulating into a resulting set. But I wonder does SQL have any functionality to where this could be done in a single query/ storedprocedure? <BR><BR>All ideas Welcome.<BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default RE: Difficult Data Manipulation

    if no skips in seq_field then:<BR><BR>Select @ half = (Select Count(*) from table) 2<BR><BR>Select seq_field, <BR> shufflesort = <BR> case when @half &#062; seq_field then seq_field <BR> else seq_field - @half <BR>end<BR>from table <BR>order by shufflesort, seq_field<BR><BR>not sure if syntax valid, but you get idea

  3. #3
    Join Date
    Dec 1969

    Default Do you really *need* SQL solution?

    If the point of this is to produce a recordset that can then be displayed in a two column table, there&#039;s another way, assuming you will be using scripting to display the results in HTML.<BR><BR>Just do ORDER BY ID.<BR><BR>Convert the Recordset to a 2D array using ADODB.Recordset.GetRows.<BR><BR>Find the midpoint using <BR>&nbsp; &nbsp; midpt = INT( UBound( theArray, 2 ) )<BR><BR>And then do<BR><BR>&#060;%<BR>For row = 0 To midpt<BR>%&#062;<BR>&nbsp; &nbsp; &#060;TR&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;%= theArray( someColumn, row ) %&#062;&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;%= theArray( someColumn, row+midpt+1 %&#062;&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;/TR&#062;<BR>&#060;%<BR>Next<BR>%&#062;<BR><BR>You have to handle the case where there are an odd number of records, but there are several ways to do that.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts