Click here to Skip to main content
15,860,972 members
Home / Discussions / Database
   

Database

 
GeneralRe: Oracle database USER and SCHEMA Pin
CHill6013-Mar-19 2:23
mveCHill6013-Mar-19 2:23 
GeneralRe: Oracle database USER and SCHEMA Pin
Valentinor13-Mar-19 3:04
Valentinor13-Mar-19 3:04 
AnswerRe: Oracle database USER and SCHEMA Pin
Eddy Vluggen13-Mar-19 0:59
professionalEddy Vluggen13-Mar-19 0:59 
QuestionDisplaying multiple rows into single row as columns based on id - Sql Server Pin
Member 111541886-Mar-19 20:18
Member 111541886-Mar-19 20:18 
SuggestionRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
CHill606-Mar-19 21:47
mveCHill606-Mar-19 21:47 
AnswerRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
Victor Nijegorodov6-Mar-19 21:52
Victor Nijegorodov6-Mar-19 21:52 
GeneralRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
Member 111541886-Mar-19 23:24
Member 111541886-Mar-19 23:24 
AnswerRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
Richard Deeming6-Mar-19 22:08
mveRichard Deeming6-Mar-19 22:08 
For SQL Server 2012 or later:
SQL
WITH cte As
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As RN,
        EmpID,
        RequestNo As CurrentReqNo,
        RequestDate As CurrentReqDate,
        RequestType As CurrentReqType,
        RequestStatus As CurrentReqStatus,
        LEAD(RequestNo) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqNo,
        LEAD(RequestDate) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqDate,
        LEAD(RequestType) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqType,
        LEAD(RequestStatus) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqStatus
    FROM
        YourTable
)
SELECT
    EmpID,
    CurrentReqNo,
    CurrentReqDate,
    CurrentReqType,
    CurrentReqStatus,
    PreviousReqNo,
    PreviousReqDate,
    PreviousReqType,
    PreviousReqStatus
FROM
    cte
WHERE
    RN = 1
;

LEAD (Transact-SQL) - SQL Server | Microsoft Docs[^]

For SQL Server 2008 or 2008 R2:
SQL
WITH cte As
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As RN,
        EmpID,
        RequestNo,
        RequestDate,
        RequestType,
        RequestStatus
    FROM
        YourTable
)
SELECT
    C.EmpID,
    C.RequestNo As CurrentReqNo,
    C.RequestDate As CurrentReqDate,
    C.RequestType As CurrentReqType,
    C.RequestStatus As CurrentReqStatus,
    P.RequestNo As PreviousReqNo,
    P.RequestDate As PreviousReqDate,
    P.RequestType As PreviousReqType,
    P.RequestStatus As PreviousReqStatus
FROM
    cte As C
    LEFT JOIN cte As P
    ON P.EmpID = C.EmpID
    And P.RN = C.RN + 1
WHERE
    C.RN = 1
;




"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

GeneralRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
Member 111541886-Mar-19 23:21
Member 111541886-Mar-19 23:21 
GeneralRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
Mycroft Holmes7-Mar-19 10:50
professionalMycroft Holmes7-Mar-19 10:50 
QuestionHelp me with caculate debit balace and incredit by SQL server 2012 Pin
Member 141737536-Mar-19 19:08
Member 141737536-Mar-19 19:08 
Rant[REPOST] Help me with caculate debit balace and incredit by SQL server 2012 Pin
Richard Deeming6-Mar-19 21:58
mveRichard Deeming6-Mar-19 21:58 
QuestionSQL Access: Calculate the total elimination of records not the same month and year ? Pin
Member 24584674-Mar-19 21:26
Member 24584674-Mar-19 21:26 
QuestionQuery Question Pin
milo-xml1-Mar-19 2:14
professionalmilo-xml1-Mar-19 2:14 
AnswerRe: Query Question Pin
Afzaal Ahmad Zeeshan1-Mar-19 3:19
professionalAfzaal Ahmad Zeeshan1-Mar-19 3:19 
GeneralRe: Query Question Pin
milo-xml1-Mar-19 5:31
professionalmilo-xml1-Mar-19 5:31 
GeneralRe: Query Question Pin
Afzaal Ahmad Zeeshan1-Mar-19 7:46
professionalAfzaal Ahmad Zeeshan1-Mar-19 7:46 
GeneralRe: Query Question Pin
milo-xml1-Mar-19 8:05
professionalmilo-xml1-Mar-19 8:05 
AnswerRe: Query Question Pin
Eddy Vluggen1-Mar-19 7:46
professionalEddy Vluggen1-Mar-19 7:46 
GeneralRe: Query Question Pin
milo-xml1-Mar-19 8:03
professionalmilo-xml1-Mar-19 8:03 
GeneralRe: Query Question Pin
Eddy Vluggen1-Mar-19 8:51
professionalEddy Vluggen1-Mar-19 8:51 
QuestionMANAGEMENT DOCUMENT Pin
Member 1413102419-Feb-19 2:23
Member 1413102419-Feb-19 2:23 
AnswerRe: MANAGEMENT DOCUMENT Pin
Eddy Vluggen19-Feb-19 3:09
professionalEddy Vluggen19-Feb-19 3:09 
AnswerRe: MANAGEMENT DOCUMENT Pin
CHill6019-Feb-19 4:14
mveCHill6019-Feb-19 4:14 
AnswerRe: MANAGEMENT DOCUMENT Pin
Victor Nijegorodov19-Feb-19 9:27
Victor Nijegorodov19-Feb-19 9:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.