Two SELECT statements in one stored procedure, one supplying input for another and the other returning more than one row

sql, sql-server, t-sql

Solution

You can do something like this

WITH first AS 
(
  SELECT val1, val2, val3, val4
    FROM Table1
   WHERE 1 = 1
), second AS
(
  SELECT val1,
         MIN(CASE WHEN rnum = 1 THEN val5 END) val51,
         MIN(CASE WHEN rnum = 1 THEN val6 END) val61,
         MIN(CASE WHEN rnum = 2 THEN val5 END) val52,
         MIN(CASE WHEN rnum = 2 THEN val6 END) val62,
         MIN(CASE WHEN rnum = 3 THEN val5 END) val53,
         MIN(CASE WHEN rnum = 3 THEN val6 END) val63
    FROM
  (
    SELECT t2.val1, val5, val6, 
           ROW_NUMBER() OVER (PARTITION BY t2.val1 ORDER BY (SELECT 1)) rnum
      FROM Table2 t2 JOIN first f
        ON t2.val1 = f.val1
  ) a
   GROUP BY val1
)
SELECT * 
  FROM first f JOIN second s
    ON f.val1 = s.val1

Here is SQLFiddle demo

Problem

I have a select statement that gets 4 column values in a row for one iteration from a query that has lot of joins. One of the column value has to be given to another select statement as input to check a where condition. This select statement returns three rows for a input from each iteration of the first select statement. I need to get all the column values from the three rows of the second select statement along with the all the column values of the first select statement. ``` SELECT val1, val2, val3, val4 from .... SELECT val5, val6 from anotherTable where someColumn = val1 ``` RESULT required : ``` val1, val2, val3, val4, val51, val61, val52, val62, val53, val63 ``` I, am using two connections and two readers to make this happen, but its slowing me down. I'd like it if I can get this done in a single stored procedure.

Original source