SQL help!!!!

[UBfoolin]

If I have a table with fields and data like:

Key A B C
=== = = =
111 A B C
222 A E C
333 B A M

Is there a way to write a query to move the data from fields A, B and C into a single column named D, grouping by the Key field?

Desired result:

Key D
=== =
111 A
111 B
111 C
222 A
222 E
222 C
and so on.


Much thanks in advance for any assistance with this.

 

[SQLSister]

Best bet is to make a new table With the Key and Dfield only
then do a series of inserts, one for each of the three fields you want in the relational model.

Insert into table1 (Key, D)
Select key, A from table2 where A is not null

Questions about posting. See faq183-874

 

[sellappan]

Same result can be achieved using UNION query as well.

SELECT
key_text, col_A AS col_D
FROM
Test

UNION

SELECT
key_text, col_B
FROM
Test

UNION

SELECT
key_text, col_C
FROM
Test

Execution of above query will result in following result.

key_text col_A
----------- -----
111 A
111 B
111 C
222 A
222 C
222 E
333 A
333 B
333 M

 

[SQLScholar]

I also ask (not to be up myself because everyones done it once, i think i did it more then once) but you usually get better responses if you put a usefull subject. "SQL help!!" doesnt really help, as most people posting in the SQL forum need SQL help. Maybe "quey columns to rows" or similar might have been better.

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!