Post reply

I really cannot think of a good description for this one :)

  • November 11, 2011 at 6:30 pm

    #390846

    Hello Everyone

    Hope that everyone has some time to rest and relax over the weekend.

    Ok, here is goes, I will try to explain this the best way that I can.

    In a table, I have data that needs to be grouped together. The table has the SetID and a SubSetID plus the Message. That is the nice part. I need to put it all back together in a single column, and in the correct order.

    Here is some sample data.

    IF OBJECT_ID('TempDB..#MyTempTable','U') IS NOT NULL

    DROP TABLE #MyTempTable

    create table #MyTempTable

    (

    SetID int

    , SubSetID int

    , TextMessage varchar(100)

    )

    INSERT INTO #MyTempTable

    (

    SetID

    , SubSetID

    , TextMessage

    )

    SELECT 15,1,'CERVICAL STRAIN'

    UNION ALL SELECT 16,1,'R RC TENDONITIS'

    UNION ALL SELECT 16,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 16,3,'SPRAIN ROTATOR CUFF'

    UNION ALL SELECT 17,1,'KNEE PAIN'

    UNION ALL SELECT 17,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 17,3,'LOC OSTEOARTH NOS-PELVIS'

    UNION ALL SELECT 17,4,'PAIN IN JOINT-L/LEG'

    UNION ALL SELECT 18,1,'JRA END STAGE DX'

    UNION ALL SELECT 18,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 18,3,'AFTRCR FOLL JOINT REPLAC'

    UNION ALL SELECT 18,4,'KNEE JT REPLACE STATUS'

    UNION ALL SELECT 19,1,'NECK PAIN S/P MVA'

    UNION ALL SELECT 19,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 19,3,'CERVICALGIA'

    UNION ALL SELECT 19,4,'SPRAIN OF NECK'

    UNION ALL SELECT 19,5,'SPRAIN THORACIC REGION'

    UNION ALL SELECT 20,1,'LBP'

    UNION ALL SELECT 20,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 20,3,'LUMBAGO'

    select * from #MyTempTable

    That comes out looking nice, everything in a separate column.

    I need to be put the SetID into into a single column and combine the SubSetID and TextMessage into another column, and in the correct order, like this:

    SetID TextMessage

    16 1. R RC TENDONITIS 2. PHYSICAL THERAPY NEC 3. SPRAIN ROTATOR CUFF

    17 1. KNEE PAIN 2. PHYSICAL THERAPY NEC 3. LOC OSTEOARTH NOS-PELVIS 4. PAIN IN JOINT-L/LEG

    18 1. JRA END STAGE DX 2. PHYSICAL THERAPY NEC 3. AFTRCR FOLL JOINT REPLAC 4. KNEE JT REPLACE STATUS

    etc.....

    Hope that the quote displays correctly.

    Thank You in advance for any and all help

    Andrew SQLDBA

  • November 11, 2011 at 7:49 pm

    #1406626

    Tx for the consumable data!

    BEGIN TRAN

    IF OBJECT_ID('TempDB..#MyTempTable','U') IS NOT NULL

    DROP TABLE #MyTempTable

    create table #MyTempTable

    (

    SetID int

    , SubSetID int

    , TextMessage varchar(100)

    --Please note how this improves the execution plan. Might not be the best choice for this table, but it's certainly a good index candidate

    , PRIMARY KEY CLUSTERED (SetID, SubSetID)

    )

    INSERT INTO #MyTempTable

    (

    SetID

    , SubSetID

    , TextMessage

    )

    SELECT 15,1,'CERVICAL STRAIN'

    UNION ALL SELECT 16,1,'R RC TENDONITIS'

    UNION ALL SELECT 16,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 16,3,'SPRAIN ROTATOR CUFF'

    UNION ALL SELECT 17,1,'KNEE PAIN'

    UNION ALL SELECT 17,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 17,3,'LOC OSTEOARTH NOS-PELVIS'

    UNION ALL SELECT 17,4,'PAIN IN JOINT-L/LEG'

    UNION ALL SELECT 18,1,'JRA END STAGE DX'

    UNION ALL SELECT 18,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 18,3,'AFTRCR FOLL JOINT REPLAC'

    UNION ALL SELECT 18,4,'KNEE JT REPLACE STATUS'

    UNION ALL SELECT 19,1,'NECK PAIN S/P MVA'

    UNION ALL SELECT 19,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 19,3,'CERVICALGIA'

    UNION ALL SELECT 19,4,'SPRAIN OF NECK'

    UNION ALL SELECT 19,5,'SPRAIN THORACIC REGION'

    UNION ALL SELECT 20,1,'LBP'

    UNION ALL SELECT 20,2,'PHYSICAL THERAPY NEC'

    UNION ALL SELECT 20,3,'LUMBAGO'

    ;WITH CTE (SetID)

    AS

    (

    SELECT DISTINCT SetID FROM #MyTempTable

    )

    SELECT

    SetId

    , (SELECT CONVERT(VARCHAR(10), T2.SubSetID) + '. ' + TextMessage + ' ' FROM #MyTempTable T2 WHERE T1.SetID = T2.SetID ORDER BY T2.SubSetID FOR XML PATH('')) AS ConcatenatedIds

    FROMCTE T1

    ROLLBACK

  • November 12, 2011 at 11:03 am

    #1406729

    That is perfect.

    Thank You very much.

    As you can see from my UserName, I am more of an Admin and not so much as a Developer. I am learning, and with the help of good folks like yourself, learning is fun.

    Thanks again, and have a great weekend

    Andrew SQLDBA

  • November 12, 2011 at 12:28 pm

    #1406742

    Always hth!

  • November 12, 2011 at 7:40 pm

    #1406772

    AndrewSQLDBA (11/12/2011)

    That is perfect.

    Thank You very much.

    As you can see from my UserName, I am more of an Admin and not so much as a Developer. I am learning, and with the help of good folks like yourself, learning is fun.

    Thanks again, and have a great weekend

    Andrew SQLDBA

    Andrew...

    See the following article for a bit of an explanation as to how Remi's code actually works. The only difference is that your's doesn't use comma separation...

    http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 14, 2011 at 10:27 am

    #1407388

    Thanks Jeff

    Andrew SQLDBA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply