TSQL select into insert multiple rows into 1 row,

问题:

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.

回答1:

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.

回答2:

You haven't made it clear how the X bit is to be resolved (choosing a single bit value from 3), so I made a choice (any 1 bit wins, otherwises its 0).

The following query should do you using group by. We do the following:

• use row_number() to assign a contiguous row id to the set
• use modulo 3 arithmetic to partition the set into groups of 3 rows, assigning each row in the group a contiguous sequence number: 0, 1 or 2.
• summarize (flatten) each group into a single row via group by, projecting each row within the original group into the correct columns in the summary row.

Here's the query:

select t.gid ,
       A1 = sum( case t.seq when 0 then A else null end ) ,
       B1 = sum( case t.seq when 0 then B else null end ) ,
       C1 = sum( case t.seq when 0 then C else null end ) ,
       A2 = sum( case t.seq when 1 then A else null end ) ,
       B2 = sum( case t.seq when 1 then B else null end ) ,
       C2 = sum( case t.seq when 1 then C else null end ) ,
       A3 = sum( case t.seq when 2 then A else null end ) ,
       B3 = sum( case t.seq when 2 then B else null end ) ,
       C3 = sum( case t.seq when 2 then C else null end ) ,
       X  = convert(bit,sign(sum(convert(int,t.X))))
from ( select * ,
              seq = ( row_number() over (order by id) - 1 ) % 3 ,
              gid = ( row_number() over (order by id) - 1 ) / 3
       from dbo.source_table
     ) t
group by t.gid
order by t.gid