0

I have data below

enter image description here

What I need is something like this

enter image description here

Is this possible to get data like this?

I do some research and getting insight using lead / lag function, but the result was not expected, heres what I tried

SELECT Id, Username, Distributor, 
    LEAD(Id) OVER (PARTITION BY Username ORDER BY Id) AS IdNew,
    LEAD(Distributor) OVER (PARTITION BY Username ORDER BY Id) AS IdNew 
FROM UserDist

enter image description here

another try using dense_rank, still no luck

DROP Table IF EXISTS #Temp 
CREATE TABLE #Temp
(
    Seq INT,
    Id INT,
    Username NVARCHAR(100),
    Distributor NVARCHAR(100)
)


INSERT INTO #Temp(Seq, Id, Username, Distributor)
select DENSE_RANK() OVER (PARTITION BY Username  ORDER BY IdDum) AS Seq,
Id, Username, Distributor
from UserDist

select * from #Temp A

Seq Id  Username    Distributor
1   1   Joe         RNN
1   1   Joe         CNN
1   1   Joe         SSN
2   2   Joe         DSS
2   2   Joe         DDK
3   4   Joe         GER
3   4   Joe         RIG
3   4   Joe         POL
3   4   Joe         MOD
1   3   Kelly       OPK
1   3   Kelly       DLC
2   5   Kelly       DERMA
2   5   Kelly       KOI
2   5   Kelly       DELL




Instead of like returning that i needed, it return redundant data

select * from #Temp A LEFT  JOIN #Temp B ON A.Username = B.Username AND A.Seq + 1 = B.Seq
WHERE A.Seq = 1 AND A.Username = 'Joe'


Seq Id  Username    Distributor Seq Id  Username    Distributor
1   1   Joe         RNN         2    4  Joe         DSS
1   1   Joe         RNN         2    5  Joe         DDK
1   2   Joe         CNN         2    4  Joe         DSS
1   2   Joe         CNN         2    5  Joe         DDK
1   3   Joe         SSN         2    4  Joe         DSS
1   3   Joe         SSN         2    5  Joe         DDK
2
  • 1
    What is the criteria which defines that the row #1 matches the row #4 and not the row #5? Physical ordering doesn't fit.. PS. dbfiddle.uk/DQpjgQDV
    – Akina
    Commented Jan 11, 2023 at 10:57
  • 1
    Your Id number is not unique. Therefore the window functions you're using to sort on it, are going to nondeterminsitically (pseudo-randomly) sort the data. E.g. if you had 3 t-shirts with different logos on each of them, and they were all colored red, and someone asked you to sort them by color, which t-shirt would come first? That's what you're asking the SQL Server Engine to do. What is the unique key for your data that can be concretely sorted on?
    – J.D.
    Commented Jan 11, 2023 at 13:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.