October 10, 2008 at 12:54 pm
I have a table with obj_id (key) and MATRIX_CAT (LOC1 or LOC2) and MX_VALUE (location of asset). Curiously, one asset can have more than one location. How can I with TSQL, combine in one query the obj_id, both locations if they exist and the names? Thanks.
OBJ_ID MATRIX_CAT MX_VALUE
7782 LOC1 BOARDROOM
7782 LOC2 WIL
7783 LOC1 STEVE'S OFFICE
I want:
7782, LOC1, BOARDROOM, LOC2, WIL
7783, LOC1, WIL, LOC2, ''
October 10, 2008 at 5:34 pm
It ain't pretty, but it's a start. This will run faster than doing it with a cursor or a while loop.
You'll probably need to create another cte with the distinct values of the location column and join it in
to get your desired result of 7783,Loc1,Steve's Office,Loc2,''. If you need help with that, just holler.
Have a good weekend.
---------------------------------------------------------------------------------------------------------------
declare @tbl table (rownum int identity(1,1), objid int,matrix_cat varchar(10), mx_value varchar(20), work varchar(1000))
declare @work varchar(1000)
set @work = ''
insert into @tbl
select 7782,'LOC1','BOARDROOM',''
union all
select 7783,'LOC1','STEVE''S OFFICE',''
union all
select 7782,'LOC2','WIL',''
order by 1,2
;with cteprevious as
(select * from @tbl)
update @tbl
set @work = work = case
when t.objid= isnull(c.objid,'') then @work+', '+t.matrix_cat+', '+t.mx_value
else t.matrix_cat+', '+t.mx_value
end
from @tbl t
left join ctePrevious c on c.rownum = t.rownum-1 and c.objid = t.objid
select cast(objid as varchar(10))+', '+max(work) as result
from @tbl
group by objid
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 10, 2008 at 7:28 pm
He double posted.
http://qa.sqlservercentral.com/Forums/Topic584195-338-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply