How to store fixed row values in a variable - SQL server

Question

I have a table which can have a maximum of 5 rows and minimum 1 row. Now I need to store these rows in different variable like @v1,@v2,@v3,@v4,@v5. How can I do it?

The Table has only 1 column custid.

CustId
100
200
300
400

If the table contain only 1 row then @v1 should have that value and rest can be null.

Answer 1

You can use the following query:

SELECT @v1 = MAX(CASE WHEN rn = 1 THEN CustId END),
       @v2 = MAX(CASE WHEN rn = 2 THEN CustId END),
       @v3 = MAX(CASE WHEN rn = 3 THEN CustId END),
       @v4 = MAX(CASE WHEN rn = 4 THEN CustId END),
       @v5 = MAX(CASE WHEN rn = 5 THEN CustId END)
FROM (
   SELECT CustId, ROW_NUMBER() OVER (ORDER BY CustId) AS rn
   FROM mytable ) t

Using ROW_NUMBER you assign a distinct number to each record of your table. Then, using conditional aggregates in an outer query you can consume this number in order to set each separate variable.

If there are less than 5 rows, the corresponding variables will be set to NULL.

SQL Fiddle Demo

Answer 2

If you have SQL Server 2012 or newer, you can try LAG() function.

SELECT 
    @v1 = custID
  , @v2 = LAG(custID, 1) OVER (ORDER BY custID DESC)
  , @v3 = LAG(custID, 2) OVER (ORDER BY custID DESC)
  , @v4 = LAG(custID, 3) OVER (ORDER BY custID DESC)
  , @v5 = LAG(custID, 4) OVER (ORDER BY custID DESC)
FROM yourTable
ORDER BY CustID DESC

SQLFiddle Demo