How to horizontally insert data into a table from another table where this data is positioned vertically?

Question

I currently have a data in a table positioned vertically:

 FILE ID:        001                                                                                             
 RECORD 1        1111                                                                                                  
 RECORD 2        123456789012345                                                                                      
 RECORD 3        A01     11                                                                                                    
 RECORD 4        A02     11                                                                                                    
 RECORD 5        A03     11                                                                                                    
 RECORD 6                0103050                                                                                         
 RECORD 7                777                                                                                                   
 RECORD 8        A01     1                                                                                                     
 RECORD 9        A02     1                                                                                               
 RECORD 10       A03     1111                                                                                                  
 RECORD 11       A04     11111                                                                                                                                                                                                                                                                                              
 FILE ID:        002                                                                                             
 RECORD 1        2222                                                                                                  
 RECORD 2        1234567                                                                                    
 RECORD 3        A01     11                                                                                                    
 RECORD 4        A02     11                                                                                                    
 RECORD 5        A03     11                                                                                                    
 RECORD 6                0103050                                                                                       
 RECORD 7                777                                                                                                   
 RECORD 8        A01     1                                                                                                     
 RECORD 9        A02     1                                                                                                
 RECORD 10       A03     1111                                                                                                  
 RECORD 11       A04     11111                                                                                          
FILE ID:         003                                                                                            
 RECORD 1        3333                                                                                                  
 RECORD 2        1234567                                                                                     
 RECORD 3        A01     11                                                                                                    
 RECORD 4        A02     11                                                                                                    
 RECORD 5        A03     11                                                                                                    
 RECORD 6                0103050                                                                                         
 RECORD 7                777                                                                                                   
 RECORD 8        A01     1                                                                                                     
 RECORD 9        A02     1                                                                                                
 RECORD 10       A03     1111                                                                                                  
 RECORD 11       A04     11111

How can I insert it into another table so it is positioned horizontally, the following way:

FileID|Record1|Record2|Record3|Record4|Record5|Record6|Record7|Record8|Record9|Record10|Record11
--------------------------------------------------------------------------------------------------
001   |1111   |1111111|A01  11|A02  11|A03  11|0103050|777    |A01   1|A02   1|A03 1111|A04 11111
002   |2222   |1234567|A01  11|A02  11|A03  11|0103050|777    |A01   1|A02   1|A03 1111|A04 11111
003   |3333   |1234567|A01  11|A02  11|A03  11|0103050|777    |A01   1|A02   1|A03 1111|A04 11111

Thank's

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Jan 30 '20 at 15:18
Why do you have columns labeled as "records"? That is cognitively dissonant. "Records" are usually associated with rows, not columns. — Gordon Linoff, Jan 30 '20 at 15:19
I named columns "Record" just for an example here... Of course I will have them named differently — gene, Jan 30 '20 at 15:22
The accepted answer [here](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) should get you what you need. — gbeaven, Jan 30 '20 at 15:36
How do tell the relationship in that data? There's nothing to "order" your data by. Is the row with the value of `'123456789012345'` for `'RECORD2'` related the to row with `'1111'` or `'3333'` for `'RECORD1'` when you don't have an order to your data? — Thom A, Jan 30 '20 at 15:48
No relation. I do a bulk insert into a table. That's how data is inserted inside of a "Data" column. I want to insert it to another table horizontally — gene, Jan 30 '20 at 15:51

score 0 · Answer 1 · answered Mar 04 '20 at 20:09

I'm a little late to the party, but I'll give it a shot. This will only work if your data is consistent with file id followed by 11 records. First, you need to have a table with an identity column. I would make it a habit to do so when creating tables. Do your bulk insert into the following table. This will store your data with row id's which will be important later.

CREATE TABLE [dbo].[Table_1](
    [TableId] [bigint] IDENTITY(1,1) NOT NULL,
    [Column1] [varchar](255) NULL,
    [Column2] [varchar](255) NULL
) ON [PRIMARY]
GO

Create this table for the pivoted data.

CREATE TABLE [dbo].[Table_2](
    [Table2ID] [bigint] IDENTITY(1,1) NOT NULL,
    [FileID] [varchar](255) NULL,
    [Record1] [varchar](255) NULL,
    [Record2] [varchar](255) NULL,
    [Record3] [varchar](255) NULL,
    [Record4] [varchar](255) NULL,
    [Record5] [varchar](255) NULL,
    [Record6] [varchar](255) NULL,
    [Record7] [varchar](255) NULL,
    [Record8] [varchar](255) NULL,
    [Record9] [varchar](255) NULL,
    [Record10] [varchar](255) NULL,
    [Record11] [varchar](255) NULL
) ON [PRIMARY]
GO

Now to get the data from table 1 to table 2. I will use a Common Table Expression (CTE) and the LEAD function.

WITH preselect AS
( 
    SELECT  Column1
           ,Column2 AS 'FileID'
           ,LEAD(Column2,1,0) OVER(ORDER BY TableId) AS 'Record1'
           ,LEAD(Column2,2,0) OVER(ORDER BY TableId) AS 'Record2'
           ,LEAD(Column2,3,0) OVER(ORDER BY TableId) AS 'Record3'
           ,LEAD(Column2,4,0) OVER(ORDER BY TableId) AS 'Record4'
           ,LEAD(Column2,5,0) OVER(ORDER BY TableId) AS 'Record5'
           ,LEAD(Column2,6,0) OVER(ORDER BY TableId) AS 'Record6'
           ,LEAD(Column2,7,0) OVER(ORDER BY TableId) AS 'Record7'
           ,LEAD(Column2,8,0) OVER(ORDER BY TableId) AS 'Record8'
           ,LEAD(Column2,9,0) OVER(ORDER BY TableId) AS 'Record9'
           ,LEAD(Column2,10,0) OVER(ORDER BY TableId) AS 'Record10'
           ,LEAD(Column2,11,0) OVER(ORDER BY TableId) AS 'Record11'
    FROM Table_1
)

INSERT INTO Table_2
SELECT FileID,Record1,Record2,Record3,Record4,Record5,Record6,Record7
       ,Record8,Record9,Record10,Record11
FROM preselect
WHERE Column1 = 'FILE ID:'

I am ordering by the TableId in the LEAD function to ensure the order of the data. Then it is just a matter of getting the FileId value along with the values of the next 11 rows.

LEAD (Transact-SQL

Common Table Expression (CTE)

How to horizontally insert data into a table from another table where this data is positioned vertically?

1 Answers1