CODEWITHSUNDEEP
sqlsql-server
RAJASEKHARA REDDY MARAM
RAJASEKHARA REDDY MARAM

Reputation: 153

How to show one column value in two columns in SQL Server

I want to show status column value in two different columns as oldstatus and newstatus as shown here.

Table: 

CREATE TABLE #Data
(
      assetno VARCHAR(10),      
      status VARCHAR(30),
      statusid VARCHAR(10),
      cdate DATE
)

Sample data:

INSERT INTO #Data (assetno, status, statusid, cdate)
VALUES ('M1008', 'NOT READY', '302', '2019-06-03 18:53:51.643' ),
       ('M1008', 'INACTIVE', '302', '2019-06-03 18:56:07.83' ),
       ('M1008', 'EMREVIEW', '304', '2019-06-03 19:09:55.61' ),
       ('M1008', 'INACTIVE', '402', '2019-06-03 19:16:20.707'),
       ('M1008', 'EMREVIEW', '403', '2019-06-03 19:16:37.347'),
       ('M1008', 'NOT READY', '404', '2019-06-03 19:18:57.607'),
       ('M1008', 'EMREVIEW', '405', '2019-06-04 11:28:02.007'),
       ('M1008', 'OPERATING', '502', '2019-06-04 11:31:17.143'),    
       ('M1008', 'NOT READY', '802', '2019-06-06 11:03:32.14'),     
       ('M1008', 'INACTIVE', '603', '2019-06-04 11:37:10.117'),
       ('M1008', 'EMREVIEW', '3782', '2019-07-15 16:35:38.793'),        
       ('M1008', 'NOT READY', '3579', '2019-07-11 18:27:08.567'),
       ('M1008', 'OPERATING', '4178', '2019-07-18 22:07:18.513'),
       ('M1008', 'OPERATING', '2602', '2019-06-26 17:56:39.983');

The output needs to look like this:

assetno  oldstatus  newstatus                     cdate
-------  ---------  ---------                     -------
M1008   NOT READY   ---             2019-06-03 18:53:51.643         
M1008   NOT READY   INACTIVE        2019-06-03 18:56:07.83      
M1008   INACTIVE    EMREVIEW        2019-06-03 19:09:55.61      
M1008   EMREVIEW    INACTIVE        2019-06-03 19:16:20.707     
M1008   INACTIVE    EMREVIEW        2019-06-03 19:16:37.347     
M1008   EMREVIEW    NOT READY       2019-06-03 19:18:57.607     
M1008   NOT READY   EMREVIEW        2019-06-04 11:28:02.007     
M1008   EMREVIEW    OPERATING       2019-06-04 11:31:17.143     
M1008   OPERATING   NOT READY       2019-06-06 11:03:32.14      
M1008   NOT READY   INACTIVE        2019-06-04 11:37:10.117     
M1008   INACTIVE    EMREVIEW        2019-07-15 16:35:38.793     
M1008   EMREVIEW    NOT READY       2019-07-11 18:27:08.567     
M1008   NOT READY   OPERATING       2019-07-18 22:07:18.513     
M1008   OPERATING   OPERATING       2019-06-26 17:56:39.983

Upvotes: 3

Views: 64

Answers (1)

marc_s
marc_s

Reputation: 754418

Something like this should work - if your SQL Server version is recent enough (SQL Server 2012 or newer required):

SELECT  
    assetno,
    oldstatus = LAG(status, 1, '---') OVER (ORDER BY Cdate),
    newstatus = status,
    cdate       
FROM 
    #data
ORDER BY 
    cdate

As I said in comments - you really should use DATETIME2(3) for your CDate column - otherwise, your ordering will be unpredictable, if there are several rows with the same date (that differ only in time - but with DATE, the time is discarded and not stored).

I get this output:

enter image description here

Upvotes: 1

Related Questions