SQL pivot /unpivot
-
I have table something like below. Need the table with status1 and status2. How can i achieve that?
-
Please provide sample DDL and INSERT statements for the above structure and you'll have a working solution very quickly.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
CREATE TABLE #LoanStatus
(
LOANNUMBER INT
,CLOSED Varchar(200)
,Pending_Transfer Varchar(200)
,REO Varchar(200)
,Foreclosure Varchar(200)
)
INSERT INTO #LoanStatus ( LOANNUMBER, CLOSED ,Pending_Transfer,REO,Foreclosure ) VALUES (12345,'','Pending_Transfer','','Foreclosure')
INSERT INTO #LoanStatus ( LOANNUMBER, CLOSED ,Pending_Transfer,REO,Foreclosure ) VALUES (12678,'Closed','Pending_Transfer','','')
-
As long as there are always exactly two statuses, this should work:
DROP TABLE IF EXISTS #LoanStatus;
CREATE TABLE #LoanStatus
(
LOANNUMBER INT
,CLOSED VARCHAR(200)
,Pending_Transfer VARCHAR(200)
,REO VARCHAR(200)
,Foreclosure VARCHAR(200)
);
INSERT INTO #LoanStatus
(
LOANNUMBER
,CLOSED
,Pending_Transfer
,REO
,Foreclosure
)
VALUES
(12345, '', 'Pending_Transfer', '', 'Foreclosure')
,(12678, 'Closed', 'Pending_Transfer', '', '');
SELECT *
FROM #LoanStatus ls;
SELECT ls.LOANNUMBER
,Status1 = MAX(COALESCE(
NULLIF(ls.CLOSED, '')
,NULLIF(ls.Pending_Transfer, '')
,NULLIF(ls.REO, '')
,NULLIF(ls.Foreclosure, '')
)
)
,Status2 = MAX(COALESCE(
NULLIF(ls.Foreclosure, '')
,NULLIF(ls.REO, '')
,NULLIF(ls.Pending_Transfer, '')
,NULLIF(ls.CLOSED, '')
)
)
FROM #LoanStatus ls
GROUP BY ls.LOANNUMBER;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Your code works , if you place the columns as you want to see result ( as example i provided) , but if i place all the columns in same order for the status 1 , statsu2 it does not work.
-
SELECT ls.LOANNUMBER
,Status1 = MAX(COALESCE(
NULLIF(ls.CLOSED, '')
,NULLIF(ls.Pending_Transfer, '')
,NULLIF(ls.REO, '')
,NULLIF(ls.Foreclosure, '')
)
)
,Status2 = MAX(COALESCE(
NULLIF(ls.CLOSED, '')
,NULLIF(ls.Pending_Transfer, '')
,NULLIF(ls.REO, '')
,NULLIF(ls.Foreclosure, '')
)
)
FROM #LoanStatus ls
GROUP BY ls.LOANNUMBER; - komal145 wrote:
Your code works , if you place the columns as you want to see result ( as example i provided) , but if i place all the columns in same order for the status 1 , statsu2 it does not work.
Obviously, but what's the problem?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I just provided two loans but if i take 1000 loans, i do not want same value for status1 , status 2 . instead i want to get different status for each statuses 1 ,2 . So in real world i will not know which field has value and not.
-
Please provide more sample data (as INSERTs) along with desired results for that data, which demonstrates what you are trying to say.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - komal145 wrote:
Your code works , if you place the columns as you want to see result ( as example i provided) , but if i place all the columns in same order for the status 1 , statsu2 it does not work.
That's because the first one is reading the values from left-to-right and the second one is reading the values from right-to-left. This gives you two different values. If you change them both to read left-to-right, you will obviously get the same values for both statuses.
The real problem with Phil's code is that it requires exactly two values for each loan. Of course, you haven't specified what you want to happen when you have more than two values, so I'm willing to overlook this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Here is how i am looking for status to be in second table. If there is no entry in table 1 for a loan Then i need status to be 'Active'.
DROP TABLE #Loanstatus
GO
CREATE TABLE #LoanStatus
(
LOANNUMBER INT
,CLOSED Varchar(200)
,Pending_Transfer Varchar(200)
,REO Varchar(200)
,Foreclosure Varchar(200)
,preForeclosure Varchar(200)
)
INSERT INTO #LoanStatus ( LOANNUMBER, CLOSED ,Pending_Transfer,REO,Foreclosure,preForeclosure ) VALUES (12345,'','Pending_Transfer','','Foreclosure','')
GO
INSERT INTO #LoanStatus ( LOANNUMBER, CLOSED ,Pending_Transfer,REO,Foreclosure,preForeclosure ) VALUES (12678,'Closed','Pending_Transfer','','','')
GO
INSERT INTO #LoanStatus ( LOANNUMBER, CLOSED ,Pending_Transfer,REO,Foreclosure,preForeclosure ) VALUES (12567,'','Pending_Transfer','','Foreclosure','')
GO
INSERT INTO #LoanStatus ( LOANNUMBER, CLOSED ,Pending_Transfer,REO,Foreclosure,preForeclosure ) VALUES (12789,'','','','','preForeclosure')
GO
INSERT INTO #LoanStatus ( LOANNUMBER, CLOSED ,Pending_Transfer,REO,Foreclosure,preForeclosure ) VALUES (12978,'CLOSED','Pending_Transfer','REO','','')
Go
INSERT INTO #LoanStatus ( LOANNUMBER, CLOSED ,Pending_Transfer,REO,Foreclosure,preForeclosure ) VALUES (12908,'','','','','')? -
I just know that Drew is going to post a really snappy solution to this one ... that's what usually happens after I post my overblown version. But here goes ... this works, but I hate cascading CTEs, so would like to see something more elegant.
DROP TABLE IF EXISTS #LoanStatus;
CREATE TABLE #LoanStatus
(
LOANNUMBER INT
,CLOSED VARCHAR(200)
,Pending_Transfer VARCHAR(200)
,REO VARCHAR(200)
,Foreclosure VARCHAR(200)
,preForeclosure VARCHAR(200)
);
INSERT INTO #LoanStatus
(
LOANNUMBER
,CLOSED
,Pending_Transfer
,REO
,Foreclosure
,preForeclosure
)
VALUES
(12345, '', 'Pending_Transfer', '', 'Foreclosure', '')
,(12678, 'Closed', 'Pending_Transfer', '', '', '')
,(12567, '', 'Pending_Transfer', '', 'Foreclosure', '')
,(12789, '', '', '', '', 'preForeclosure')
,(12978, 'CLOSED', 'Pending_Transfer', 'REO', '', '')
,(12908, '', '', '', '', '');
WITH statuses1
AS (SELECT ls.LOANNUMBER
,Status = ls.CLOSED
,rn = 1
FROM #LoanStatus ls
WHERE ls.CLOSED <> ''
UNION ALL
SELECT ls.LOANNUMBER
,ls.Pending_Transfer
,2
FROM #LoanStatus ls
WHERE ls.Pending_Transfer <> ''
UNION ALL
SELECT ls.LOANNUMBER
,ls.REO
,3
FROM #LoanStatus ls
WHERE ls.REO <> ''
UNION ALL
SELECT ls.LOANNUMBER
,ls.Foreclosure
,4
FROM #LoanStatus ls
WHERE ls.Foreclosure <> ''
UNION ALL
SELECT ls.LOANNUMBER
,ls.preForeclosure
,5
FROM #LoanStatus ls
WHERE ls.preForeclosure <> '')
,Statuses2
AS (SELECT statuses1.LOANNUMBER
,Status1 = (CASE
WHEN ROW_NUMBER() OVER (PARTITION BY statuses1.LOANNUMBER
ORDER BY statuses1.LOANNUMBER
,statuses1.rn
) = 1 THEN
statuses1.Status
ELSE
''
END
)
,Status2 = (CASE
WHEN ROW_NUMBER() OVER (PARTITION BY statuses1.LOANNUMBER
ORDER BY statuses1.LOANNUMBER
,statuses1.rn
) = 2 THEN
statuses1.Status
ELSE
''
END
)
,Status3 = (CASE
WHEN ROW_NUMBER() OVER (PARTITION BY statuses1.LOANNUMBER
ORDER BY statuses1.LOANNUMBER
,statuses1.rn
) = 3 THEN
statuses1.Status
ELSE
''
END
)
FROM statuses1)
SELECT Statuses2.LOANNUMBER
,Status1 = MAX(Statuses2.Status1)
,Status2 = MAX(Statuses2.Status2)
,Status3 = MAX(Statuses2.Status3)
FROM Statuses2
GROUP BY Statuses2.LOANNUMBER
UNION ALL
SELECT ls.LOANNUMBER
,Status1 = 'ACTIVE'
,Status2 = ''
,Status3 = ''
FROM #LoanStatus ls
WHERE CONCAT(ls.CLOSED, ls.Foreclosure, ls.Pending_Transfer, ls.preForeclosure, ls.REO) = '';The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I wonder if grouping is even needed. It looks like one row = one loan. Is this true, komal145? Or there are multiple rows with same loan number but different statuses?
--Vadim R.
-
Yes , it is is one row for one loan.
- Phil Parkin wrote:
I just know that Drew is going to post a really snappy solution to this one ... that's what usually happens after I post my overblown version. But here goes ... this works, but I hate cascading CTEs, so would like to see something more elegant.
Here is the way that I would approach it.
Solution 1 with CROSS APPLY.
WITH CTE AS
(
SELECT LoanNumber, status_desc, ROW_NUMBER() OVER(PARTITION BY LoanNumber ORDER BY status_num) rn
FROM #LoanStatus ls
CROSS APPLY( VALUES(1, Closed), (2, Pending_Transfer), (3, REO), (4, Foreclosure), (5, preForeclosure), (6, 'Active')) st(status_num, status_desc)
WHERE st.status_desc > ''
)
SELECT LoanNumber
,MAX(CASE WHEN rn = 1 THEN status_desc ELSE '' END) AS Status_1
,MAX(CASE WHEN status_desc <> 'Active' AND rn = 2 THEN status_desc ELSE '' END) AS Status_2
,MAX(CASE WHEN status_desc <> 'Active' AND rn = 3 THEN status_desc ELSE '' END) AS Status_3
FROM CTE c
GROUP BY LoanNumber;Here is another solution that works, but I think the conversion to XML and back may not be performant in the long run.
Solution 2 with XML.
SELECT
ls.LoanNumber
,COALESCE(x.LoanStatuses.value('(/LoanStatuses/LoanStatus)[1]', 'VARCHAR(200)'), '') AS status_1
,COALESCE(x.LoanStatuses.value('(/LoanStatuses/LoanStatus)[2][. != "Active"]', 'VARCHAR(200)'), '') AS status_2
,COALESCE(x.LoanStatuses.value('(/LoanStatuses/LoanStatus)[3][. != "Active"]', 'VARCHAR(200)'), '') AS status_3
FROM #LoanStatus ls
CROSS APPLY
(
SELECT NULLIF(status_desc, '') AS LoanStatus
FROM ( VALUES(1, Closed), (2, Pending_Transfer), (3, REO), (4, Foreclosure), (5, preForeclosure), (6, 'Active')) st(status_num, status_desc)
FOR XML PATH('LoanStatuses'), TYPE
) x(LoanStatuses)Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply