Displaying multiple rows into single row as columns based on id - Sql Server
-
Hello Friends, my requirement is, I have table with following fields EmpID,RequestNo,RequestDate,RequestType,RequestStatus. Now, I need to fetch the top 2 rows of the employee based on the employee ID with request date desc, and should display data of these 2 rows into 1 row with the fields: I have query to fetch only top 2 rows, now I should know how to display that 2 rows into single rows with columns. EmpID Current ReqNo Current ReqDate Current ReqType Current ReqStatus Previous ReqNo Previous ReqDate Previous ReqType Previous ReqStatus Kindly help, I googled and found Pivot, but I am not getting exact result. Thank you so much, Best Regards, Priya.
-
Hello Friends, my requirement is, I have table with following fields EmpID,RequestNo,RequestDate,RequestType,RequestStatus. Now, I need to fetch the top 2 rows of the employee based on the employee ID with request date desc, and should display data of these 2 rows into 1 row with the fields: I have query to fetch only top 2 rows, now I should know how to display that 2 rows into single rows with columns. EmpID Current ReqNo Current ReqDate Current ReqType Current ReqStatus Previous ReqNo Previous ReqDate Previous ReqType Previous ReqStatus Kindly help, I googled and found Pivot, but I am not getting exact result. Thank you so much, Best Regards, Priya.
-
Hello Friends, my requirement is, I have table with following fields EmpID,RequestNo,RequestDate,RequestType,RequestStatus. Now, I need to fetch the top 2 rows of the employee based on the employee ID with request date desc, and should display data of these 2 rows into 1 row with the fields: I have query to fetch only top 2 rows, now I should know how to display that 2 rows into single rows with columns. EmpID Current ReqNo Current ReqDate Current ReqType Current ReqStatus Previous ReqNo Previous ReqDate Previous ReqType Previous ReqStatus Kindly help, I googled and found Pivot, but I am not getting exact result. Thank you so much, Best Regards, Priya.
You could create a temp table with the "current" & "previous" columns and insert data into it from the fetched two top records. And do it in a simple stored procedure... :)
-
Hello Friends, my requirement is, I have table with following fields EmpID,RequestNo,RequestDate,RequestType,RequestStatus. Now, I need to fetch the top 2 rows of the employee based on the employee ID with request date desc, and should display data of these 2 rows into 1 row with the fields: I have query to fetch only top 2 rows, now I should know how to display that 2 rows into single rows with columns. EmpID Current ReqNo Current ReqDate Current ReqType Current ReqStatus Previous ReqNo Previous ReqDate Previous ReqType Previous ReqStatus Kindly help, I googled and found Pivot, but I am not getting exact result. Thank you so much, Best Regards, Priya.
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
-
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
Hello Richard, Thanks lot and lot. I was looking on this work for a day, it saved my another day. Thanks lot. Thanks for also sharing the link to learn not only just to copy paste the code shared for me. Best Regards, Priya.
-
You could create a temp table with the "current" & "previous" columns and insert data into it from the fetched two top records. And do it in a simple stored procedure... :)
Hi Victor, Thank you
-
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
The CTE king strikes again ;P
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
