By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

Selective updating of table

P: n/a
Hi

I'm struggling to find a solution to this problem. I have a table
containing a number of columns 3 of which are known as Impact 1, 2 and
3. I need to produce a report that has seperate rows for each impact,
however, impact 2 + 3 do not always have data in them so in this case i
wouldnt want them included in the report.

I was thinking i could loop through the rows and then where there is
just 1 impact add that to a new table, where there are 2 impacts add 2
rows to the new table etc, however, im struggling with this. any
suggestions?

I guess i should probably seperate out the impacts into a seperate
table, but ive just been asked to produce a relatively simple report
using someone elses DB rather than redesign the whole DB.

Thanks for any help
Paul

Jun 5 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a UNION query. E.g.:

SELECT 1 as impact_code, Impact1 As impact_value
FROM table_name
WHERE Impact1 IS NOT NULL

UNION

SELECT 2 as impact_code, Impact2
FROM table_name
WHERE Impact2 IS NOT NULL

UNION

SELECT 3 as impact_code, Impact3
FROM table_name
WHERE Impact3 IS NOT NULL

Change the table name to your table name.

The result will be like this:

impact_code impact_value
1 <some value>
2 <some value>
3 <some value>
.... etc. for all rows in the source table ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRISNMIechKqOuFEgEQKPLwCgoicqltIyBatL1DMl9nQzd3 V2wIMAoOLr
U7Qr3C2w9HpNfH4fqS+A9n0w
=LT2c
-----END PGP SIGNATURE-----
pa************@hotmail.com wrote:
Hi

I'm struggling to find a solution to this problem. I have a table
containing a number of columns 3 of which are known as Impact 1, 2 and
3. I need to produce a report that has seperate rows for each impact,
however, impact 2 + 3 do not always have data in them so in this case i
wouldnt want them included in the report.

I was thinking i could loop through the rows and then where there is
just 1 impact add that to a new table, where there are 2 impacts add 2
rows to the new table etc, however, im struggling with this. any
suggestions?

I guess i should probably seperate out the impacts into a seperate
table, but ive just been asked to produce a relatively simple report
using someone elses DB rather than redesign the whole DB.

Jun 5 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.