Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Yak Posting Veteran

99 Posts

Posted - 04/11/2011 :  05:58:33  Show Profile  Reply with Quote
I have atable TEst with fields as follows

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:


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


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:
   row_number() over (order by rowId,ProcessId) as NewRowNumber, 
   Error for ErrorCode in (errcode1,errcode2,errcode3)
) U
Go to Top of Page

Yak Posting Veteran

99 Posts

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

Go to Top of Page

Esteemed SQL Purist

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

(process_id INTEGER NOT NULL,
error_code CHAR(3) NOT NULL
REFERENCES Errors (error_code),
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.

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  
 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.04 seconds. Powered By: Snitz Forums 2000