Post reply

query(bit urgent pls help)

  • January 30, 2009 at 6:01 am

    #94553

    Hi all,

    can any one pls send the query

    I have two tables

    A B

    101 101 abc

    102 101 efg

    I need query which displays output as 101 abc,efg

  • January 30, 2009 at 6:19 am

    #935461

    Try This:

    CREATE TABLE #A (ID int)

    CREATE TABLE #B (ID int, Code varchar(10))

    INSERT INTO #A SELECT 1 UNION SELECT 2

    INSERT INTO #B

    SELECT 1,'abc' UNION SELECT 1,'def' UNION

    SELECT 2,'pqr' UNION SELECT 2,'lmn'

    SELECT * FROM #A

    SELECT * FROM #B

    Declare @a varchar(100)

    SELECT @a = COALESCE(@a+',','')+Code FROM #B WHERE ID = 1

    SELECT @a

    SET @a = NULL

    SELECT @a = COALESCE(@a+',','')+Code FROM #B WHERE ID = 2

    SELECT @a

    DROP TABLE #A

    DROP TABLE #B

    Cheers,
    Hari
    Tips & Tricks for SQL BI Developers

  • January 30, 2009 at 6:29 am

    #935471

    Its working fine hari

    I Let u know whether it satisfies all my requirement.

  • January 30, 2009 at 6:41 am

    #935481

    Do you always want to run the query for a single ID? Or do you intend to run a single query and get the result for all the IDs? If so, you can achieve it using FOR XML PATH as explained here: How to generate a Delimited String using FOR XML PATH

    .

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

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