Post reply

Concatenating String Values in Transact-SQL

  • October 13, 2010 at 5:28 pm

    #227936

    Hi

    I need to Concatenate strings using Group By in sql server 2005

    For Example :

    DocNo CreatedAt NoteTitle NoteLine NoteText

    6677656 2010-09-27 C 1 POP

    6677656 2010-09-27 C 2 STAR

    6677656 2010-09-21 X 1 Jackson

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

    Output:

    DocNo CreatedAt NoteTitle NoteText

    6677656 2010-09-27 C POP Star

    6677656 2010-09-21 X Jackson

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

    Test Code:

    CREATE TABLE test(

    [DocNo] [int] NOT NULL,

    [CreatedAt] DATETIME,

    [Notetitle] [varchar](25) NOT NULL,

    [NoteLine] [INT],

    [NoteText] [varchar](25) NOT NULL

    )

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

    INSERT INTO test(DocNo,CreatedAt,Notetitle,NoteLine,NoteText)

    SELECT 6677656,'2010-09-27','C',1,'POP'

    UNION ALL

    SELECT 6677656,'2010-09-27','C',2,'STAR'

    UNION ALL

    SELECT 6677656,'2010-09-21','X',1,'JACKSON'

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

    I tried below code.

    SELECT

    DocNo,

    CreatedAt,

    SUM((CASE WHEN noteline=1 THEN notetext ELSE '' END )+ (CASE WHEN noteline=2 THEN notetext ELSE '' END) )AS test,

    Notetitle FROM NotesView

    where DocNo = 6677656

    GROUP BY DocNo,CreatedAt,Notetitle

    I know it doent work , but do not know how to do.

    Can anybody help me ?

    Thanks in advance.

    Regards

    Anitha

  • October 13, 2010 at 6:28 pm

    #1236590

    Does this work for you?

    ;WITH CTE AS

    (

    select DISTINCT DocNo, CreatedAt, Notetitle

    from dbo.test

    )

    SELECT *,

    stuff((SELECT ',' + NoteText

    FROM dbo.Test

    WHERE DocNo = CTE.DocNo

    AND CreatedAt = CTE.CreatedAt

    AND Notetitle = CTE.Notetitle

    ORDER BY NoteText

    FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

    FROM CTE

    ORDER BY DocNo, CreatedAt, Notetitle;

    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

  • October 14, 2010 at 3:27 pm

    #1237240

    Thanks .

  • October 14, 2010 at 8:25 pm

    #1237278

    You're welcome... but does it do what you need?

    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