CODEWITHSUNDEEP
sql-server
user13858425
user13858425

Reputation: 13

Move a row to a column SQL Server

I have a table like below and I want to transpose the value on column_1 where column_2 is Null to the next row column_3 until the next Null value on column_2.

------------------------------------------------------
| ID  |Column_1 |Column_2|Column_3                   |
------------------------------------------------------
|1    |Deposit  |Null    |                           |
|2    |01/02    |Charges |value from column_2 of id=1|
|3    |02/02    |payable |value from column_2 of id=1|
|4    |operation|Null    |                           |
|5    |02/03    |Charges |value from column_2 of id=4|
|6    |03/03    |receive |value from column_2 of id=4|

Upvotes: 1

Views: 53

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Try this below script using Sub-Query-

Demo Here

SELECT *,
(
    SELECT TOP 1 
    CASE 
        WHEN A.Column_2 IS NULL THEN NULL 
        ELSE Column_1 
    END
    FROM your_table B
    WHERE B.ID < A.ID 
    AND B.Column_2 IS NULL
    ORDER BY ID DESC
) Column_3                   
FROM your_table A
ORDER BY ID

Upvotes: 0

Andrei Odegov
Andrei Odegov

Reputation: 3429

Try this:

select ID, Column_1, Column_2,
  max(c3) over(partition by partby) as Column_3
from (
  select
    *,
    iif(Column_2 is null, Column_1, null) as c3,
    sum(iif(Column_2 is null, 1, 0)) over(order by ID) as partby
  from t1
) as a;

Demo.

Upvotes: 2

Related Questions