Post reply

comma Separted Value

  • November 17, 2010 at 8:05 pm

    #231718

    Dear All;

    I Have an Table are four Columns

    The Columns like an Courseid,Coursename,Edatetime,Cid the table having the four values

    like an

    Courseid Coursename EdateTime, cid

    07112200 Maths 15-10-2009 001

    07112200 Maths 15-10-2009 002

    07112202 Chemistry 15-10-2009 003

    07112202 Chemistry 15-10-2009 004

    07112202 Chemistry 15-10-2009 005

    07112202 Chemistry 15-10-2009 006

    I need the following format Like an

    Courseid Coursename EdateTime cid

    07112200 Maths 15-10-2009 001,002

    07112202 Chemistry 15-10-2009 003,004,005,006

    Any ony to help me how can i do that

    Thanks;

    Faijurrahuman.A

  • November 17, 2010 at 8:40 pm

    #1252253

    This should handle it for you...

    SET DATEFORMAT DMY; -- to handle the format of your dates

    -- See how you start off by actually creating a table

    -- and then inserting the data into it? Doing this really

    -- makes things a lot easier for all the people you want to

    -- help you. So, HELP US HELP YOU by doing this for us! See

    -- http://www.sqlservercentral.com/articles/Best+Practices/61537/

    -- for more details on how to do all of this.

    DECLARE @test-2 TABLE (Courseid INT,

    Coursename VARCHAR(20),

    EdateTime DATE,

    cid char(3),

    PRIMARY KEY (Courseid, Coursename, EdateTime, cid));

    INSERT INTO @test-2

    SELECT 07112200, 'Maths', '15-10-2009', '001' UNION ALL

    SELECT 07112200, 'Maths', '15-10-2009', '002' UNION ALL

    SELECT 07112202, 'Chemistry', '15-10-2009', '003' UNION ALL

    SELECT 07112202, 'Chemistry', '15-10-2009', '004' UNION ALL

    SELECT 07112202, 'Chemistry', '15-10-2009', '005' UNION ALL

    SELECT 07112202, 'Chemistry', '15-10-2009', '006';

    WITH CTE AS

    (

    SELECT DISTINCT

    Courseid,

    Coursename,

    EdateTime

    FROM @test-2

    )

    SELECT Courseid,

    Coursename,

    EdateTime,

    cid = STUFF((SELECT ',' + cid

    FROM @test-2 t2

    WHERE Courseid = t1.Courseid

    AND Coursename = t1.Coursename

    AND EdateTime = t1.EdateTime

    ORDER BY cid

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

    FROM CTE 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

  • November 18, 2010 at 8:46 pm

    #1252836

    Thank you so much

  • November 18, 2010 at 9:05 pm

    #1252841

    You're welcome. Does it meet your needs? And, do you understand what it's doing?

    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