insert rows into table based on criteria

Question

Suppose I have 5 columns which contain a value of either an ‘A’ or ’B’

+------+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 | Col5 |
+------+------+------+------+------+
| A    | A    | B    | A    | B    |
| B    | A    | B    | B    | A    |
+------+------+------+------+------+

I also want to assign those 5 columns to have an ID number of 1 to 5

+----+---------+
| ID | Columns |
+----+---------+
|  1 | Col1    |
|  2 | Col2    |
|  3 | Col3    |
|  4 | Col4    |
|  5 | Col5    |
+----+---------+

What I would like to do is insert a new row(s) into a db table if col1, col2, col4 in the first row contains an 'A' in which case 3 new rows will be inserted each containing their respective ID number.

The table that I'm inserting into should look something like this

 +----+----------+----------+----------+----------+
    | ID | SomeCol0 | SomeCol1 | SomeCol2 | SomeCol3 |
    +----+----------+----------+----------+----------+
    |  1 | x        | x        | x        | x        |
    |  2 | x        | x        | x        | x        |
    |  4 | x        | x        | x        | x        |
    +----+----------+----------+----------+----------+

This is for a VB.NET application. I would prefer to have this logic work out in TSQL but VB code can do as well. I’m really at a loss on what to do. Any help is greatly appreciated.

Okay. So you have 3 tables that you're working with? Is that right? — jpaugh, Jul 09 '15 at 04:35
Well no, the ID column in the 2nd table shown above does not exist. Only the ID column in the third table which is where I'm inserting into exist. — lostintheloop, Jul 09 '15 at 04:38
Ok. I think I understand. You want to input columns and output rows, with some extra data attached (namely an id)? This is called *transposing*. — jpaugh, Jul 09 '15 at 04:41
Here's a question about transposing. Maybe that will help. http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — jpaugh, Jul 09 '15 at 04:41

score 0 · Accepted Answer · answered Jul 09 '15 at 06:17

Let' say we have the following data:

DECLARE @DataSource TABLE
(
    [Col1] CHAR(1)
   ,[Col2] CHAR(1)
   ,[Col3] CHAR(1)
   ,[Col4] CHAR(1)
   ,[Col5] CHAR(1)
);

INSERT INTO @DataSource ([Col1], [Col2], [Col3], [Col4], [Col5])
VALUES ('A', 'A', 'B', 'A', 'B')  -- this should be inserted
      ,('B', 'A', 'B', 'B', 'B')
      ,('A', 'A', 'A', 'A', 'B')  -- this should be inserted
      ,('B', 'B', 'B', 'A', 'B');

First, we need to filter the rows by your criteria:

SELECT *
FROM @DataSource
WHERE [Col1] = 'A' AND [Col2] = 'A' AND [Col4] = 'A';

enter image description here

Now, we need to UNPIVOT the results above in order to assign an ID value for each column:

SELECT [ID]
      ,[Value]
FROM
(
    SELECT [Col1] AS [1]
          ,[Col2] AS [2]
          ,[Col3] AS [3]
          ,[Col4] AS [4]
          ,[Col5] AS [5]
    FROM @DataSource
    WHERE [Col1] = 'A' AND [Col2] = 'A' AND [Col4] = 'A' 
) DS
UNPIVOT
(
    [Value] FOR [ID] IN ([1], [2], [3], [4], [5])
) UNPVT;

enter image description here

Finally, just use WHERE clause to filter by ID:

SELECT [ID]
      ,[Value]
FROM
(
    SELECT [Col1] AS [1]
          ,[Col2] AS [2]
          ,[Col3] AS [3]
          ,[Col4] AS [4]
          ,[Col5] AS [5]
    FROM @DataSource
    WHERE [Col1] = 'A' AND [Col2] = 'A' AND [Col4] = 'A' 
) DS
UNPIVOT
(
    [Value] FOR [ID] IN ([1], [2], [3], [4], [5])
) UNPVT
WHERE [ID] IN (1, 2, 4);

enter image description here

insert rows into table based on criteria

1 Answers1