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.
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.
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
Try this..
create table #tab
(
custID int
)
insert into #tab
select 110 union all
select 120 union all
select 130 union all
select 140 union all
select 150
declare @v1 int,@v2 int, @v3 int, @v4 int, @v5 int
select @v1 = custID
from #tab
order by custid
OFFSET 0 row
FETCH NEXT 1 ROW ONLY
select @v2 = custID
from #tab
order by custid
OFFSET 1 row
FETCH NEXT 1 ROW ONLY
select @v3 = custID
from #tab
order by custid
OFFSET 2 row
FETCH NEXT 1 ROW ONLY
select @v4 = custID
from #tab
order by custid
OFFSET 3 row
FETCH NEXT 1 ROW ONLY
select @v5 = custID
from #tab
order by custid
OFFSET 4 row
FETCH NEXT 1 ROW ONLY
select @v1,@v2,@v3,@v4,@v5
we can achieve this even using PIVOT function and with the help Row_number
declare @mytable table (CustId int)
insert into @mytable values
(100), (200), (300), (400),(500)
SELECT [1] as V1, [2]as V2, [3]as V3, [4]as V4, [5]as V5
FROM
(SELECT CustId,ROW_NUMBER() OVER (ORDER BY CustId) AS value
FROM @mytable ) AS SourceTable
PIVOT
(
max(CustId)
FOR value IN ([1], [2], [3], [4],[5])
) AS PivotTable
Just for fun, one brutal approach:
Select @v1 = t1.CustID,
@v2 = oa2.CustID,
@v3 = oa3.CustID,
@v4 = oa4.CustID,
@v5 = oa5.CustID
from Table t1
outer apply(select top 1 CustID from Table t2 where t2.CustID not in(t1.CustID) order by CustID) oa2
outer apply(select top 1 CustID from Table t3 where t3.CustID not in(t1.CustID, oa2.CustID) Order by CustID) oa3
outer apply(select top 1 CustID from Table t4 where t4.CustID not in(t1.CustID, oa2.CustID, oa3.CustID) Order by CustID) oa4
outer apply(select top 1 CustID from Table t5 where t5.CustID not in(t1.CustID, oa2.CustID, oa3.CustID, oa4.CustID) Order by CustID) oa5
