tsql - Select from 2 rows in one table into a single row with 2 or more columns in the second table

Question

I have a table that has 2 columns. One is a type column and the other is a value amount column. There are only 2 types/ I would like to select columns of this table into another table with 2 combined columns based on type and value. For example, the table may have order with 2 of the types in 2 rows. It would be inserted into the 2nd table as one row.

Example:

Table 1

| ID | OrderID | Type | Value | |:-----|:--------:|:------------:|-------:| | 1 | 300 | bike | 100 | | 2 | 300 | skateboard | 150 | | 3 | 700 | bike | 200 | | 4 | 700 | skateboard | 50 | | 5 | 800 | bike | 150 | | 6 | 800 | skateboard | 100 _ What is the TSQL to have it inserted into the 2nd table with these values? Table 2 | ID | OrderID | BikeValue | SkateboardValue | |:----|:--------:|:----------:|-----------------:| | 1 | 300 | 100 | 150 | | 2 | 700 | 200 | 50 | | 3 | 800 | 150 | 100 |

question from:https://stackoverflow.com/questions/65893182/select-from-2-rows-in-one-table-into-a-single-row-with-2-or-more-columns-in-the

Answer 1

Just make it simple for yourself. Do two SQL statements. One to insert and another to update.

INSERT INTO Table2 (OrderID, BikeValue)
SELECT Table1.OrderID, Table1.Value
FROM Table1 (NOLOCK) 
WHERE Table1.Type = 'bike'

UPDATE Table2 SET Table2.SkateboardValue = Table1.Value 
FROM Table2 
    INNER JOIN Table1 ON Table1.OrderID = Table2.OrderID 
WHERE Table1.Type = 'skateboard'