Ulphat Ulphat - 2 months ago 6
MySQL Question

What is the best way for selecting 2 row from table in one row?

I have a table like following

TABLE_A
ID PERSON_ID NAME GRADE
---------- ---------- ---------- ----------
1 1 NAME_1 10
2 1 NAME_1 20
3 2 NAME_2 30
4 2 NAME_2 40
...


in this table, for each name there is exactly two rows (two grades).
I want to make a query which results like following

RESULT
PERSON_ID NAME GRADE1 GRADE_2
---------- ---------- ---------- ----------
1 NAME_1 10 20
2 NAME_2 30 40


What is the best way for this.
I can use self join but I think this is not correct method

Answer

Try this for SQL SERVER

; WITH CTE AS(
SELECT PERSON_ID, NAME, GRADE AS GRADE_1, 
LEAD(GRADE) OVER(PARTITION BY NAME ORDER BY NAME) AS GRADE_2 
FROM TABLE_A
) 
SELECT * FROM CTE 
WHERE GRADE_2 IS NOT NULL

RESULT SET:

PERSON_ID    NAME         GRADE_1       GRADE_2
----------   ----------   ----------   ----------
1            NAME_1       10            20
2            NAME_2       30            40