CODEWITHSUNDEEP
sqlsql-serversql-server-2008
chelsjo
chelsjo

Reputation: 11

SQL - populate new column according to data in row above

I need to populate a new column in a table known as RowType, where if the ID column contains the same ID value as the one above RowType is populated with 'D', if the value is new then RowType is populate with 'H', how would the SQL code look to be able to do this?

I.e should look something like below:

RowType (to be populated), ID (already there)
H, 1
D, 1
D, 1
H, 2
D, 2
H, 3
D, 3
D, 3

Thanks

Upvotes: 0

Views: 1093

Answers (4)

Pawel Czapski
Pawel Czapski

Reputation: 1864

It may not be the best solution, however it can point you somewhere, and it works. Go through the code carfuly and make sure you understand this.

create table yourTable (RowType char, id int)

insert into yourTable (RowType, id) values
 ('',1)
,('',1)
,('',1)
,('',2)
,('',2)
,('',3)
,('',3)
,('',3)

select 
    row_number() over (order by id) as rowNumber,
    RowType,
    id
into #tempTable
from yourTable

declare @maxRow int = (select max(rowNumber) from #tempTable)
declare @currentRow int = 1
while (@currentRow <= @maxRow)
begin
    if (@currentRow = 1)
    begin
        update #tempTable
        set RowType = 'H'
        where rowNumber = @currentRow
    end
    else
    begin
        if (select id from #tempTable where rowNumber = @currentRow) = (select id from #tempTable where rowNumber = @currentRow - 1)
        begin
            update #tempTable
            set RowType = 'D'
            where rowNumber = @currentRow
        end
        else 
        begin
            update #tempTable
            set RowType = 'H'
            where rowNumber = @currentRow
        end
    end
    set @currentRow = @currentRow +1
end

-- update data in actual table, you can do below if only those two columns exist in table !!!
delete from yourTable

-- insert into table from updated temp table
insert into yourTable
select RowType, ID
from #tempTable

select * from yourTable

select * from #tempTable

-- drop temp table
drop table #tempTable

Upvotes: 0

toonice
toonice

Reputation: 2236

Please try...

UPDATE tableName
SET RowType = CASE
                  WHEN ( ID = LAG( ID ) OVER ( ORDER BY ID ) ) THEN 'D'
                  ELSE 'H'
              END

If you have any questions or comments, then please feel free to post a Comment accordingly.

Further Reading

https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql (for information on LAG()).

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Use ROW_NUMER concept :

CREATE TABLE #table(Id INT)

INSERT INTO #table(Id)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL 
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3

SELECT CASE WHEN RowType = 1 THEN 'H' ELSE 'D' END RowType , Id
FROM 
(
   SELECT ROW_NUMBER() OVER (PARTITION BY Id ORDER BY id) RowType , Id
   FROM #table
) A

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use Row_Number and case

select *, RowType = case when Row_Number() over (partition by id order by id) = 1 then 'H' else 'D' End from #yourid

Your input table:

create table #yourId (id int)

insert into #yourid (id) values
 (1)
,(1)
,(1)
,(2)
,(2)
,(3)
,(3)
,(3)

Upvotes: 1

Related Questions