October 10, 2008 at 12:52 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.
October 10, 2008 at 1:02 pm
Please refer to the post in my signature on how to post data on this forum and then post some sample data and your table structure.
October 10, 2008 at 1:06 pm
I did post some sample data and table structure. Wish I'd copied it somewhere.
I'll try again below here:
OBJ_ID MATRIX_CAT MX_VALUE
16109 LOC1 BOARDROOM
16109 LOC2 WIL
16110 LOC1 STEVE'S OFFICE
RESULTS I WANT:
16109, LOC1, BOARDROOM, LOC2, WIL
16110, LOC1, STEVE'S OFFICE, LOC2, ''
October 10, 2008 at 1:15 pm
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
( OBJ_ID INT ,
MATRIX_CAT NVARCHAR(12), MX_VALUE NVARCHAR(32) )
--===== Setup any special required conditions especially where dates are concerned SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable (OBJ_ID, MATRIX_CAT, MX_VALUE)
SELECT 16109,'LOC1','BOARDROOM' UNION ALL
SELECT 16109,'LOC1','WIL' UNION ALL
SELECT 16110,'LOC2','STEVE OFFICE'
October 10, 2008 at 2:18 pm
Hah, you caught me there for a second. In your second post, you posted some data and an expected result. Then in your third, the actual inserts had different data 😎
Once I changed the inserts to match your actual data, this works fine. There may be a more efficient method of doing this, and if there is, I'm interested to hear it, but this is the way I normally do it.
[font="Courier New"]
SELECT DISTINCT T1.OBJ_ID, T2.MX_VALUE Loc1, T3.MX_VALUE Loc2
FROM #mytable T1
LEFT JOIN #mytable T2 ON T1.OBJ_ID = T2.OBJ_ID AND T2.MATRIX_CAT = 'LOC1'
LEFT JOIN #mytable T3 ON T1.OBJ_ID = T3.OBJ_ID AND T3.MATRIX_CAT = 'LOC2'
[/font]
October 10, 2008 at 2:55 pm
Thanks! It worked great!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply