SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Use of PIVOT operator
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

susan_151615
Yak Posting Veteran

India
99 Posts

Posted - 04/11/2011 :  05:58:33  Show Profile  Reply with Quote
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

5155 Posts

Posted - 04/11/2011 :  07:15:57  Show Profile  Reply with Quote
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
Go to Top of Page

susan_151615
Yak Posting Veteran

India
99 Posts

Posted - 04/11/2011 :  07:55:36  Show Profile  Reply with Quote
Thank you so much sunitabeck.It worked

susan
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 04/11/2011 :  18:07:07  Show Profile  Visit jcelko's Homepage  Reply with Quote
>> 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000