| Author |
 Topic  |
|
|
susan_151615
Yak Posting Veteran
India
99 Posts |
 Posted - 04/11/2011 : 05:58:33
|
I have atable TEst with fields as follows
(rowid,processid,errorcode1,errorcode2,errorcode3)
Sample values for Test table:
RowId ProcessID ErrorCode1 ErrorCode2 ErrorCode3
1 2 cd1 cd2 cd3
i need to insert values from this table into another table
test2 which has fields as follows(rowid,processis,error)
Now i want a query which will yield result as follows for the test 2 table when i insert value from test table:
Result:
RowId ProcessID Error
1 2 cd1
2 2 cd2
3 2 cd3
Here rowid ia auto incremented column.
I tried using PIVOT operator but it didnt yield the result i wanted.
Can anyone help me in this
susan |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/11/2011 : 07:15:57
|
Looks like you need UNPIVOT rather than PIVOT - like this:select
row_number() over (order by rowId,ProcessId) as NewRowNumber,
ProcessId,
Error
from
TestTable
unpivot
(
Error for ErrorCode in (errcode1,errcode2,errcode3)
) U |
 |
|
|
susan_151615
Yak Posting Veteran
India
99 Posts |
Posted - 04/11/2011 : 07:55:36
|
Thank you so much sunitabeck.It worked
susan |
|
|
|
jcelko
Esteemed SQL Purist
USA
547 Posts |
Posted - 04/11/2011 : 18:07:07
|
>> a table Test with fields [sic: Columns are not fields] as follows
(row_id, process_id, error_code_1, error_code_2, error_code_3) <<
Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.
Did you actually put the physical position of a row in a table!? Do you actually have a repeated group, so you are not anywhere near First Normal Form (1NF)?!
Sample values for Test table:
row_id process_id error_code_1 error_code_2 error_code_3
1 2 cd1 cd2 cd3
>> I need to insert values from this table into another table <<
Probably not. You need to get a proper, normalized table and to quit writing “1950's punch card code” in SQL. First, read a book on RDBMS, any book. Pay attention to the parts about “redundancy” and we SQL People view it like eating babies .. without Ketchup!
Seriously, you are doing everything wrong. Drop the table and start over. Here is a normalized table
CREATE TABLE Test2
(process_id INTEGER NOT NULL,
error_code CHAR(3) NOT NULL
REFERENCES Errors (error_code),
error_seq INTEGER NOT NULL
CHECK (error_seq BETWEEN 1 AND 3), –--Hardwired 3 errors
PRIMARY KEY (process_id, error_seq));
>> Here row_id is an auto incremented column. <<
NO, NO, NO! That was punch cards and mag tapes; it has no place in RDBMS. Again, you are doing everything wrong. A key has to be logical attributes and not a count of the physical storage. 
--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
|
|
|
| |
Topic |
|
|
|
Use of PIVOT operator
