Displaying Data In MS SQL Server With Same Key But Different ID

Question

Let's say I have data below

How can I display this data side by side with lowest id and so on without UNION?

I'm thinking about giving sequence number for every data that has the same usercode, create table temporary for each iteration, and then join them.

Here the code

DROP TABLE #TEMP
CREATE TABLE #TEMP (
    ID INT,
    [data] INT,
    usercode NVARCHAR(50),
    RowNum INT
)

INSERT INTO #TEMP(ID, [data], UserCode, RowNum)
SELECT Id, ApplicationID, 'john', ROW_NUMBER() OVER (ORDER BY Usercode) RNum from UserApplicationAccess

This is inserted data, so I'm giving a sequence number for each row for mark every data with id

select a.UserCode, a.RowNum Id_1, a.[data] data_1, b.RowNum Id_2, b.[data] data_2 from #TEMP a join #TEMP b on a.UserCode = b.UserCode 
where a.RowNum = 1 and b.RowNum = 2 
union
select a.UserCode, a.RowNum Id_1, a.[data] data_1, b.RowNum Id_2, b.[data] data_2 from #TEMP a join #TEMP b on a.UserCode = b.UserCode 
where a.RowNum = 2 and b.RowNum = 3

This is how I query to get the data that I want, it works but since there is no limitation how many data for every user, I think this query is not enough. For example this data just have 3 row, so I'm using union just twice, but there are user that have 10 data, so I have to write 9 union, and so on, and so on.

Answer 1

It looks like you're trying to convert adjacent rows into a range, so if for each usercode you have 10 rows, you want to produce 9 output rows, each with the current and "next" ApplicationID value. You can do that using the LEAD or LAG functions.

Given this data:

declare @TEMP table(
    ID INT,
    ApplicationId INT,
    usercode NVARCHAR(50)
)

insert into @temp (ID,ApplicationId,usercode)
values
(43461,34,'john'),
(43462,52,'john'),
(43463,55,'john');

The following query will produce the desired result:

with a as(
SELECT usercode,
    row_number() over (partition by usercode order by id) as RN,
    applicationid,
    lag(applicationid) over (partition by usercode order by id) as Prev
from @temp)
SELECT 
    usercode,
    rn-1 as ID_1 ,
    prev as data_1,
    rn as ID_2,
    applicationid as data_2
from a
where prev is not null

This returns

usercode    ID_1    data_1  ID_2    data_2
john        1       34      2       52
john        2       52      3       55

over (partition by usercode order by id) partitions the data by usercode and orders it by ID. After that, we can calculate the row number in the partition and retrieve the previous or next value using LAG or LEAD.

LAG/LEAD will return NULL at the edges, so prev is not null is used to exclude the edge pair.