SQL pivot /unpivot

Question

SQL pivot /unpivot

komal145

SSCrazy Eights

Points: 9918
More actions
June 19, 2019 at 6:14 pm

#3655396

I have table something like below. Need the table with status1 and status2. How can i achieve that?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246977 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

komal145 SSCrazy Eights Points: 9918 More actions · Answer 2

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','','')

Phil Parkin SSC Guru Points: 246977 More actions · Answer 3

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

komal145 SSCrazy Eights Points: 9918 More actions · Answer 4

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.

komal145 SSCrazy Eights Points: 9918 More actions · Answer 5

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;

This reply was modified 5 years, 6 months ago by Steve Jones - SSC Editor. Reason: format code

Phil Parkin SSC Guru Points: 246977 More actions · Answer 6

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

komal145 SSCrazy Eights Points: 9918 More actions · Answer 7

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.

Phil Parkin SSC Guru Points: 246977 More actions · Answer 8

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

drew.allen SSC Guru Points: 76996 More actions · Answer 9

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

komal145 SSCrazy Eights Points: 9918 More actions · Answer 10

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,'','','','','')?

This reply was modified 5 years, 6 months ago by Steve Jones - SSC Editor.

Phil Parkin SSC Guru Points: 246977 More actions · Answer 11

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

rVadim Hall of Fame Points: 3969 More actions · Answer 12

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.

komal145 SSCrazy Eights Points: 9918 More actions · Answer 13

komal145

SSCrazy Eights

Points: 9918

June 20, 2019 at 2:24 pm

#3655783

Yes , it is is one row for one loan.

drew.allen SSC Guru Points: 76996 More actions · Answer 14

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