是否有一条SQL语句将2个长列分成几对列? [英] Is there a SQL statement that will break what would be 2 long columns into several pairs of columns?

问题描述

我的数据在正常"查询中显示如下:

I have data that, in a "normal" query, would appear like this:

Val1    Val2
----    ----
1   2
2   [blank]
3   2
4
5   1
6   3
..
96  1

不过，我想要的是这样的(我需要将行数限制为12):

What I want, though, is something like this (I need to limit the number of rows to 12):

Val1    Val2    Val1    Val2    Val1    Val2    ... Val1    Val2
----    ----    ----    ----    ----    ----
1   2   13  1   25  [blank]  ...    85  1
2   [blank] 14  1   26  3   ... 86  [blank]
..  ... ... ... ... ... ... ... ...
12  1   24  [blank] 36  2   ... 96  3

是否有一条选择语句可以给我?我不是SQL专家，但我正在按照以下思路(似乎)在考虑一些问题:

Is there a select statement that would give me that? I'm no SQL expert, but I'm thinking something (semantically) along these lines:

select (select val1, val2 from dbtable where val1 < 13),
(select val1, val2 from dbtable where val1 > 12 and val1 < 25),
...
(select val1, val2 from dbtable where val1 > 84)
from dbtable

更新

响应dfb的sql示例:

UPDATE

In response to dfb's sql example:

当我这样做时:

SELECT t1.Val1, t1.Val2 FROM 
(SELECT Val1, Val2, rownum() as rownum FROM dbTable) t1 
INNER JOIN (SELECT Val1, Val2, rownum() as rownum FROM dbTable) t2 
ON t1.rownum/2 == t2.rownum/2

...我得到未在预期的位置找到FROM关键字"

当我这样做时(删除"rownum()"的东西):

And when I do this (remove the "rownum()" stuff):

SELECT t1.Val1, t1.Val2 FROM 
(SELECT Val1, Val2 FROM dbTable) t1 
INNER JOIN (SELECT Val1, Val2 FROM dbTable) t2 
ON t1.rownum/2 == t2.rownum/2

...我得到"ORA-01747:无效的user.table.column，table.column或列 规范"

...I get "ORA-01747: invalid user.table.column, table.column, or column specification"

Sully的示例是最接近的示例，尽管我希望UNION SQL可以工作-如果可以在不降低有效值的情况下完成操作，则更好.照原样，我有正确的布局，但是val并没有显示在16X12布局中我需要它们的位置.无论如何，为了后代，这是动态创建行和列的方式(如下代码所示，彼此之间并不相同):

Sully's example came the closest, although I wish the UNION SQL would work - it would be better if it could be done without pushing down the valid values. As it is, I have the right layout but the vals are not appearing just where I need them to within that 16X12 layout. At any rate, for posterity's sake, here's how the Rows and Columns are dynamically created (not as shown in the code below, and not identical to each other):

//prebuild 12 rows in outputDt 
int iRows = 12;
while (iRows > 0)
{
    DataRow row = outputDt.NewRow();
    outputDt.Rows.Add(row);
    iRows -= 1;
}

//prebuild 16 cols in outputDt 
int iCols = 16;
while (iCols > 0) {
    DataColumn col = new DataColumn();
    outputDt.Columns.Add(col);
    iCols -= 1;
}

最终更新

使其正常工作.参见是否可以通过交替填充DataGridView垂直列?

推荐答案

这样做的好处是，如果最终获得更多数据，它只会根据需要构建更多的水平列，但绝不会超过12行数据.如果您需要显示更多数据，则"in-SQL"方式将需要更改代码.

Bonus to this is if you end up with more data, it'll just build more horizontal columns as needed but never go over 12 rows of data. The "in-SQL" way will require code changes if you ever need to display more data.

免责声明:这完全是现成的(因为我曾经使用过C#).可能有更好的方法来执行此操作(Linq?)，逻辑应该很接近，但这给了您灵活性，可以将数据列表用于其他目的而不是狭focused的显示.

Disclaimer: This is totally off-the-cuff (C# as that's what I'm used to). There are probably much better ways to do this (Linq?) The logic should be pretty close, but this gives you the flexibility to use that list of data for other purposes than this very narrowly focused display.

DataTable dt = ResultsFromSproc();
DataTable outputDt = new DataTable();

//prebuild 12 rows in outputDt
int iRows = 12;
while(iRows > 0) {
    outputDt.Rows.Add(new DataRow());
    iRows-=1;
}

int outputColumn = 0;
for(int i = 0; i < dt.Rows.Count; i+=1){
    DataRow dr = dt.Rows[i];

    if(i % 12 == 0 && i > 0) { 
        //add two more columns to outputDt
        outputDt.Columns.Add() //Not sure but you might need to give it a name. (outputColumn+2).ToString() should work
        outputDt.Columns.Add() //Not sure but you might need to give it a name. (outputColumn+3).ToString() should work
        outputColumn += 1;
    }
    outputDt.Rows[i%12][outputColumn] = dr[0];
    outputDt.Rows[i%12][outputColumn + 1] = dr[1];
}
//Step2: Bind to outputDt. Step 3: Profit!

备用版本:要求val1 == 48进入单元格48(请参见注释)

ALTERNATE version: For requirement that val1 == 48 goes in cell 48 (see comments)

DataTable dt = ResultsFromSproc();
DataTable outputDt = new DataTable();

//prebuild 12 rows in outputDt
int iRows = 12;
while(iRows > 0) {
    outputDt.Rows.Add(new DataRow());
    iRows-=1;
}

int outputColumn = 0;
int iMaxCell = (int)dt.Select("MAX(Val1)")[0][0];
//ASSUMING YOU HAVE ALREADY DONE AN ORDER BY Val1 in SQL (if not you need to sort it here first)
for(int i = 0; i < iMaxCell; i+=1){
    DataRow dr = dt.Rows[i];

    if(i % 12 == 0 && i > 0) { 
        //add two more columns to outputDt
        outputDt.Columns.Add() //Not sure but you might need to give it a name. (outputColumn+2).ToString() should work
        outputDt.Columns.Add() //Not sure but you might need to give it a name. (outputColumn+3).ToString() should work
        outputColumn += 2;
    }
    //compare to i+1 if your data starts at 1
    if((int)dr[0] == (i+1)){
        outputDt.Rows[i%12][outputColumn] = dr[0];
        outputDt.Rows[i%12][outputColumn + 1] = dr[1];
    }
}
//Step2: Bind to outputDt. Step 3: Profit!

这篇关于是否有一条SQL语句将2个长列分成几对列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！