Post reply

Merging 3 coulmns

  • June 3, 2010 at 8:37 pm

    #387080

    Hi Friends

    I am facing different situation,here is sample code

    CREATE TABLE test

    (

    docno INT,

    notetitle VARCHAR(10),

    Notetext VARCHAR(12),

    NoteLine VARCHAR(12)

    )

    -------------------------------------------------------------------

    INSERT INTO test

    SELECT 6496644,'Information','Highway',1 UNION ALL

    SELECT 6496644,'Information','Auckland',2 UNION ALL

    SELECT 6496644,'Information','NewZealand',3 UNION ALL

    SELECT 6496644,'Information','1006',4

    ----------------------------------------------------------------------

    DROP TABLE test

    When i execute above table i will get results like this

    docno notetitle notetext noteline

    ----- -------- -------- --------

    6496644 Information Highway 1

    6496644 Information Auckland 2

    ---

    Desired Output:

    =================================================================

    docno notetitle notetext

    6496644 Information highway Auckland NewZealand 1006

    Can anybody tell me how to get above output?

    Thanks

  • June 3, 2010 at 9:43 pm

    #1176296

    SELECT DISTINCT

    docno,

    notetitle,

    Notetext = STUFF((SELECT ' ' + NoteText

    FROM test

    WHERE docno = t1.docno

    AND notetitle = t1.notetitle

    ORDER BY NoteLine

    FOR XML PATH('')),1,1,'')

    FROM test t1

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 3, 2010 at 10:01 pm

    #1176302

    Thank you very much for your help

  • June 3, 2010 at 10:08 pm

    #1176306

    You're welcome. Did it do what you want it to do?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 4 posts - 1 through 3 (of 3 total)

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