Use of PIVOT operator - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use of PIVOT operator

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2011-04-11 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-11 : 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

99 Posts

Posted - 2011-04-11 : 07:55:36

Thank you so much sunitabeck.It worked

susan

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-11 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources