Geek Answers Handbook

How to store fixed row values ​​in a variable - SQL Server

I have a table that can contain no more than 5 rows and at least 1 row. Now I need to save these lines in different variables, for example @v1,@v2,@v3,@v4,@v5 . How can i do this?

The table has only 1 custid column.

 CustId 100 200 300 400

If the table contains only one row, then @v1 should have this value, and rest may be null .

+7
sql sql-server
source share
5 answers

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 separate number to each record in your table. Then, using conditional aggregates in an external query, you can use this number to set each individual variable.

If the number of rows is less than 5, the corresponding variables will be set to NULL .

SQL Fiddle Demo

+9
source share

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

 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

+5
source share

Try it.

 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
+1
source share

we can achieve this even with the PIVOT function and with 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
+1
source share

Just for fun, one cruel 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
0
source share

More articles:


All Articles