COMBINE ROWS OF ONE TABLE

  • 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.

  • 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.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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, ''

  • --===== 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'

  • 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]

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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