Convert table row values into column
-
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 -
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
-
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 -
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.
-
Thanks a Lot
Deepa -
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply