Update unique row data in table using multiple rows data? 1

[fletchsod]

With this script... No problem...

Update tblDealership 
SET tblDealership.Address1 = tblDms.Address1, 
      tblDealership.Address2 = tblDms.Address2, 
      tblDealership.Address3 = tblDms.Address3
FROM tblDms 
INNER JOIN tblDealership ON tblDms.Id = tblDealership.Id

But if I have mutliple rows in tblDms (multiple Ids) then how do I update the tblDealership?

DECLARE @TblDms(Id INT, Name VARCHAR(50), Address VARCHAR(50))
INSERT INTO @TblDms VALUES(1, 'Company1', '3351 Sara Dr')
INSERT INTO @TblDms VALUES(2, 'Company2', '8710 Isle Cir')
INSERT INTO @TblDms VALUES(2, 'Company2', 'PO BOX 1234')
INSERT INTO @TblDms VALUES(2, 'Company2', '579 Adam Ln')
INSERT INTO @TblDms VALUES(3, 'Company3', '1440 E Cast Ln')

As you see, "Company 2" have multiple addresses, so how do I use the UPDATE sql script above to make it work like this...

Address1 = '8710 Isle Cir', Address2 = 'PO BOX 1234', Address3 = '579 Adam Ln'

Thanks...
[/code]

 

[markros]

You need to transpose the data first, see

;with cte as (select * from (select ID, Address, row_number() over (partition by ID order by PK) as Rn -- some field that controls that order
from @tblDms) X PIVOT (max(Address) for Rn IN ([1],[2],[3])) pvt)

update T set Address1 = cte.[1], Address2 = cte.[2], Address3 = cte.[3]
from tblDealership T inner join cte on T.ID = cte.ID

PluralSight Learning Library

 

[fletchsod]

I can make do w/ partition and row_number. I'm not too familar w/ PIVOT, so I'll have to study it some more.

Thanks for the replay.