TSQL选择插入多行到1行，很多列最简单的方法 [英] TSQL select into insert multiple rows into 1 row, many columns simplest way

问题描述

我使用MS SQL Server 2008 R2，我有一个结果表，我希望从属于另一个表的数据填充。

  //结果表定义
 
创建表resultsTable 
（
 RowId int Identity（1,1）Not Null，
 
 sid1 int ，colA1 int，colB1 int，colC1 int，
 sid2 int，colA2 int，colB2 int，colC2 int，
 sid3 int，colA3 int，colB3 int，colC3 int，
 colX bit 
）
 
 //源表定义
 
创建表sourceTable 
（
 RowId int Identity（1,1）Not Null，
 colA int，colB int，colC int，colX bit，
）
  

表中有100行以上的数据，我想使用类似

的方法选择3行。

  select top 3 
 colA，
 colB，
 colC，
 colX 
来自sourceTable 
其中Rowid> = @RowIdVariable 
  

其中@RowIdVariable值来自另一个表，RowsIdFrom

  create table RowIdsFromTable 
（
 id int Identity（1,1）Not Null，
 RowIdFrom int 
）
  
 声明（p $ p> p $ p> 
 
 
 MyCursor游标为
选择RowIdFrom从RowIdsFromTable顺序由RowIdFrom asc 
打开MyCursor 
  
我知道我的结果将类似下面的选择语句
  sid | colA | colB | colC | colX 
 ------------------------- 
 1 1 2 3 0 
 2 4 5 6 0 
 3 7 8 9 1 
  
有了这些结果，我非常想知道如何可以将它们插入我的resultsTable，以便
  select * 
 from resultsTable 
  
 
 
 
应该看起来像下面的结果，并以最有效的方式做：D 
  rowId | sid1 | colA1 | colB1 | colC1 | sid2 | colA2 | colB2 | colC2 | sid3 | colA3 | colB3 | colC3 | colX 
 1 1 1 2 3 2 4 5 6 3 7 8 9 1 
  
位值属于最后一行，如上例所示：
  colA | colB | colC | colX 
 ------------------------- 
 7 8 9 1 
  
感谢任何指针和帮助：D 
 
 
 
 140213编辑：
 
 
 
为了提供更多的信息或更清楚，我需要插入到结果表中返回的行，因为我有另一个表，定义了我应该选择的所有RowId。 
 
 
 
如果RowIdsFromTable包含3个值，1，12和20，则所需的输出可能如下所示：
  rowId | sid1 | colA1 | colB1 | colC1 | sid2 | colA2 | colB2 | colC2 | sid3 | colA3 | colB3 | colC3 | colX 
 1 1 1 2 3 2 4 5 6 3 7 8 9 1 
 2 12 24 9 13 32 13 43 88 14 2 54 23 0 
 3 20 xx xx xx 21 xx xx xx 22 xx xx xx 1 
  
其中xx表示来自sourceTable的整数值。请注意sid升序的顺序。
 
 
 
我附加一个sql fiddle与表和一些测试数据： http://sqlfiddle.com/#!3/95fa8/1/0 。
解决方案
如果你的目标是选择有限的行数来执行这个脚本，那么你可以使用下面的解决方案。
  with Top_3_Rows_CTE AS（
 SELECT Top 3 1 AS Artificial_Grouping 
，RowID 
，colA 
，colB 
， colC 
，colX 
 
 FROM sourceTable 
 
 WHERE RowID> = 2 
）
 
，Row_Numbers_CTE AS $ b SELECT ROW_NUMBER（）OVER（ORDER BY Artificial_Grouping）AS Row_Number  - 选择最适合您的情况的order by子句
，Artificial_Grouping 
，RowID 
，ColA 
，ColB 
，ColC 
，ColX 
 
 FROM Top_3_Rows_CTE 
）
 
选择DISTINCT R2.RowID作为RowID1 
， R2.ColA AS ColA1 
，R2.ColB AS ColB1 
，R2.ColC AS ColC1 
，R2.ColX AS ColX1 
，R3.RowID AS RowID2 
， R3.ColA AS ColA2 
，R3.ColB AS ColB2 
，R3.ColC AS ColC2 
，R3.ColX AS ColX2 
，R4.RowID AS RowID3 
， R4.ColA AS ColA3 
，R4.ColB AS ColB3 
，R4.ColC AS ColC3 
，R4.ColX as ColX3 
 
 FROM Row_Numbers_CTE R1 
 LEFT OUTER JOIN Row_Numbers_CTE R2 
 ON R2.Row_Number = 1 
 AND R1.Artificial_Grouping = R2.Artificial_Grouping 
 LEFT OUTER JOIN Row_Numbers_CTE R3 
 ON R3.Row_Number = 2 
 AND R1.Artificial_Grouping = R3.Artificial_Grouping 
 LEFT OUTER JOIN Row_Numbers_CTE R4 
 ON R4.Row_Number = 3 
 AND R1.Artificial_Grouping = R4.Artificial_Grouping  - 您将在此处为每行创建另一个联接你想分成列
  
这应该是很快，因为它开始只选择3行和所有该处理被限制为包含不多于3行的CTE。你只需要为Row_Numbers_CTE添加一个Left Outer Join，你需要将每一个额外的行转换成新的列。
 
I'm using MS SQL Server 2008 R2 and I have a results table that I wish to populate from data belonging from another table.
// Results table definition

create table resultsTable
(
  RowId int Identity(1,1) Not Null,

  sid1 int, colA1 int, colB1 int, colC1 int,
  sid2 int, colA2 int, colB2 int, colC2 int,
  sid3 int, colA3 int, colB3 int, colC3 int,
  colX  bit      
)

// Source table definition

create table sourceTable
(
  RowId int Identity(1,1) Not Null ,
  colA int , colB int , colC int , colX bit ,
) 
If the source table had 100+ rows of data, I would like to be able to select 3 rows using something like
select top 3
       colA,
       colB,
       colC,
       colX
from sourceTable
where Rowid >= @RowIdVariable
where @RowIdVariable value is coming from another table, RowsIdFrom
create table RowIdsFromTable
( 
  id int Identity(1,1) Not Null,
  RowIdFrom int
)
I am thinking of using a cursor for this.
declare MyCursor cursor for
    select RowIdFrom from RowIdsFromTable order by RowIdFrom asc
Open MyCursor 
I know my results will look something like this below for the select statement
sid | colA | colB | colC | colX
-------------------------
1     1      2      3      0
2     4      5      6      0
3     7      8      9      1 
With these results, I would very much like to know how I can could insert them into my resultsTable so that
select *
from resultsTable
should look like the results below and do it in the most efficient way please :D
rowId | sid1 | colA1 | colB1 | colC1 | sid2 | colA2 | colB2 | colC2 | sid3 | colA3 | colB3 | colC3 | colX
1       1       1      2       3       2      4       5       6       3      7       8       9       1
where colX bit value belongs to the last row, as in the above example, 
colA | colB | colC | colX
-------------------------
7      8      9      1 
Thanks for any pointers and help :D

140213 Edit:

To give more information or to be more clear, I am needing to insert into the resultsTable the returned row because I have another table that defines all the RowId that I should be selecting from.  This table just contains the starting point RowId's.

The desired output may therefore look like below if RowIdsFromTable contained 3 values, 1, 12 and 20
rowId | sid1 | colA1 | colB1 | colC1 | sid2 | colA2 | colB2 | colC2 | sid3 | colA3 | colB3 | colC3 | colX
1       1      1       2       3       2      4       5       6       3      7       8       9       1
2       12     24      9       13      32     13      43      88      14     2       54      23      0
3       20     xx      xx      xx      21     xx      xx      xx      22     xx      xx      xx      1
where "xx" represents the integer values from the sourceTable.  Please note the order of the sid's ascending.

I attach an sql fiddle with the tables and some test data: http://sqlfiddle.com/#!3/95fa8/1/0.
解决方案
If your goal really is to select a finite number of rows to perform this script on, then you could use the solution below.
WITH Top_3_Rows_CTE AS (
SELECT Top 3 1 AS Artificial_Grouping
    ,RowID
    , colA
    , colB
    , colC
    , colX

FROM sourceTable

WHERE RowID >=2
)

, Row_Numbers_CTE AS (
SELECT ROW_NUMBER() OVER (ORDER BY Artificial_Grouping) AS Row_Number --Pick an order by clause that makes the most sense for your situation
    , Artificial_Grouping
    , RowID
    , ColA
    , ColB
    , ColC
    , ColX

FROM Top_3_Rows_CTE
)

SELECT DISTINCT R2.RowID AS RowID1
    , R2.ColA AS ColA1
    , R2.ColB AS ColB1
    , R2.ColC AS ColC1
    , R2.ColX AS ColX1
    , R3.RowID AS RowID2
    , R3.ColA AS ColA2
    , R3.ColB AS ColB2
    , R3.ColC AS ColC2
    , R3.ColX AS ColX2
    , R4.RowID AS RowID3
    , R4.ColA AS ColA3
    , R4.ColB AS ColB3
    , R4.ColC AS ColC3
    , R4.ColX AS ColX3

FROM Row_Numbers_CTE R1
    LEFT OUTER JOIN Row_Numbers_CTE R2
        ON R2.Row_Number = 1
            AND R1.Artificial_Grouping = R2.Artificial_Grouping
    LEFT OUTER JOIN Row_Numbers_CTE R3
        ON R3.Row_Number = 2
            AND R1.Artificial_Grouping = R3.Artificial_Grouping
    LEFT OUTER JOIN Row_Numbers_CTE R4
        ON R4.Row_Number = 3
            AND R1.Artificial_Grouping = R4.Artificial_Grouping --You would create another join here for each row you want broken out into columns
This should be pretty fast as it starts be only selecting 3 rows and all the processing is restricted to CTEs containing no more than 3 rows. You would just add a Left Outer Join to the Row_Numbers_CTE for each additional row you want transposed into new columns.

                        
这篇关于TSQL选择插入多行到1行，很多列最简单的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！