Click here to Skip to main content
14,027,757 members
   

Database

 
GeneralRe: Oracle database USER and SCHEMA Pin
CHill6013-Mar-19 2:23
protectorCHill6013-Mar-19 2:23 
GeneralRe: Oracle database USER and SCHEMA Pin
Valentinor13-Mar-19 3:04
memberValentinor13-Mar-19 3:04 
AnswerRe: Oracle database USER and SCHEMA Pin
Eddy Vluggen13-Mar-19 0:59
mveEddy 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
memberMember 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
protectorCHill606-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
memberVictor 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
memberMember 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:
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:
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
memberMember 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
memberMycroft Holmes7-Mar-19 10:50 
QuestionHelp me with caculate debit balace and incredit by SQL server 2012 Pin
Member 141737536-Mar-19 19:08
memberMember 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
memberMember 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
mveAfzaal 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
mveAfzaal 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
mveEddy 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
mveEddy Vluggen1-Mar-19 8:51 
QuestionMANAGEMENT DOCUMENT Pin
Member 1413102419-Feb-19 2:23
memberMember 1413102419-Feb-19 2:23 
AnswerRe: MANAGEMENT DOCUMENT Pin
Eddy Vluggen19-Feb-19 3:09
mveEddy Vluggen19-Feb-19 3:09 
AnswerRe: MANAGEMENT DOCUMENT Pin
CHill6019-Feb-19 4:14
protectorCHill6019-Feb-19 4:14 
AnswerRe: MANAGEMENT DOCUMENT Pin
Victor Nijegorodov19-Feb-19 9:27
memberVictor 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.


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190419.4 | Last Updated 18 Apr 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid