How to find previous column value in current row sql?-CodePudding

I want to find the previous column value in a current row

OrgID	Treelevel	LEVEL1	LEVEL2	LEVEL3	immediateparent
0010	3	008	009	0010	NULL
0012	2	0011	0012

Expected output

OrgID	Treelevel	LEVEL1	LEVEL2	LEVEL3	immediateparent
0010	3	008	009	0010	009
0012	2	0011	0012		0011

This is my row in a table.

Here my orgid is 0010 and the higher level of 0010 is 009.

Now I want find the higher level of orgid and want to update in immediate parent column.please help me to solve this issue

Thanks

CodePudding user response：

generally speaking, the best practice for this would be if the row included a column indicating the parent Id. For example:

OrgID	OrgParentID
001	:009
002	:009
009	:025

Thus we can query select OrgID from table1 where OrgParentID = '009' or to get the parent select OrgParentID from table1 where OrgID = '001' In order to follow the org structure further we would alias table1 for the next level: select Org1.OrgID as level1, Org1.OrgParentID as level2, Org2.OrgParentID as level3 from Table1 as Org1 left outer join Table1 as Org2 on Org1.OrgParentID = Org2.OrgID

hope this helps

CodePudding user response：

select   orgid
        ,treelevel
        ,immediateparent
from 
         (
         select   orgid
                 ,treelevel
                 ,id
                 ,case when orgid = id then lag(id) over(partition by orgid order by level) end as immediateparent
         from     t
         unpivot (id for level in(LEVEL1, LEVEL2, LEVEL3)) up
         ) t
where    orgid = id

orgid	treelevel	immediateparent
10	3	9
12	2	11

Fiddle

CodePudding user response：

Hy, I'm not sure to get what you are trying to do... Hope that's could help :

SELECT *
FROM TEST MYT
LEFT OUTER JOIN
    (SELECT T.ID,MAX(T.FIELD) AS MAX_VALUE 
     FROM
        (SELECT ID AS ID,
                CAST(RIGHT(MT.LEVEL1, LEN(MT.LEVEL1)-2) AS INT) AS FIELD
         FROM TEST MT
         UNION ALL
         SELECT ID AS ID,
                CAST(RIGHT(MT.LEVEL2, LEN(MT.LEVEL2)-2) AS INT) AS FIELD
         FROM TEST MT
         UNION ALL
         SELECT ID AS ID,
                CAST(RIGHT(MT.LEVEL3, LEN(MT.LEVEL3)-2) AS INT) AS FIELD
         FROM TEST MT)AS T GROUP BY ID) AS T ON T.ID = MYT.ID

This should show you the max value between your fields As a good suggestion, it's always better to make the "SELECT" query first to be sure that the data you are managing is as expected

Here's the query for "UPDATE" :

UPDATE MYT SET MYT.immediateparent = MAX_VALUE
--    SELECT *
FROM TEST MYT
LEFT OUTER JOIN
    (SELECT T.ID,MAX(T.FIELD) AS MAX_VALUE 
     FROM
        (SELECT ID AS ID,
                CAST(RIGHT(MT.LEVEL1, LEN(MT.LEVEL1)-2) AS INT) AS FIELD
         FROM TEST MT
         UNION ALL
         SELECT ID AS ID,
                CAST(RIGHT(MT.LEVEL2, LEN(MT.LEVEL2)-2) AS INT) AS FIELD
         FROM TEST MT
         UNION ALL
         SELECT ID AS ID,
                CAST(RIGHT(MT.LEVEL3, LEN(MT.LEVEL3)-2) AS INT) AS FIELD
         FROM TEST MT)AS T GROUP BY ID) AS T ON T.ID = MYT.ID