Post reply

Convert table row values into column

  • March 15, 2005 at 8:21 am

    #66069

    Hi,

    I've seen this topic earlier here, but now I'm unable to find it.

    I'm required to fetch data from 2 tables (master & child) in such a way that all a column from the child rows appears as a comma separated string.

    E.g:

    Table A

    ColA    ColB   ColC   ColD

    dhh     dshds   dsjkj  dsds

    dsjd    rrr        www  dsda

    Table B

    ColA  ColH

    dhh   388

    dhh   9jgkk

    dhh  8892nmjk

    Something that will give the following output:

    ColA    ColB    ColC   ColD  ColH

    dhh     dshds  dsjkj  dsds   388, 9jgkk, 8892nmjk

    It will be preferable through a Select rather than a UDF.

    Thanks

    Deepa


    Deepa

  • March 15, 2005 at 8:34 am

    #547073

    This is fairly simple if you are only after one ColA value. The following assumes @ColA as an input parameter to a stored procedure.

    declare @MergedColH varchar(1000)

    set @MergedColH = ''    -- Must initialize in case the next WHERE clause gets zero rows

    SELECT @MergedColH = case when @MergedColH = '' then '' else ', ' end + ColH

               FROM TableB

               WHERE ColA = @ColA

    SELECT ColA, ColB, ColC, ColD, @MergedColH AS ColH

               FROM TableA

               WHERE ColA = @ColA

     

  • March 15, 2005 at 9:20 am

    #547090

    Aaron,

    If I need to extract this way then I'll have to create a procedure. Is it not possible through a single SELECT?

    Deepa


    Deepa

  • March 15, 2005 at 9:30 am

    #547091

    Not really. Technically it doesn't need a SP, it just needs the ability to run a SQL batch so that the two step process will work. The only way I can think of to do this as a single select is to use a UDF and you pooh-poohed that idea. 

    There are other approaches I could come up with but they involve multi-step processes using cursors and/or temp tables or the one I gave you. Ultimately the UDF is the same deal, it just takes the first step and conceals it within a somewhat internal process.

    The example I gave would work for most situations, but if you are trying to code this into an Access query, for example, or create a view from this then the only way I can see to accomplish it would be to use a UDF. If the UDF isn't allowed from whatever source is prompting this due to some incompatible syntax checking then you could define a view that uses the UDF to generate the results and then reference the view from your outside application.

  • March 15, 2005 at 9:40 am

    #547095

    Thanks a Lot


    Deepa

  • March 17, 2005 at 9:16 am

    #547581

    You don't need to think of a way to this with the RAC utility for S2k.Check out the @concatenate parameter and RAC will create a table just as you want.Simple and hassle free

    http://www.rac4sql.net

     

     

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

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