<div>
order by ordernumber added in the below query for exact output 
 

<pre><code id="code-8227797">select * from 
(select * from OrderLine )
p
pivot
(sum(orderQty) for
orderitemcode in ([A],[B],[C],[D],[E])
) as pvt
order by ordernumber</code></pre>
</div>

order by ordernumber added in the below query for exact output



select * from 
(select * from OrderLine )
p
pivot
(sum(orderQty) for
orderitemcode in ([A],[B],[C],[D],[E])
) as pvt
order by ordernumber

<div>
<div class="content wysiwyg-content">
I have a table OrderLine 
 
OrderNumber 
OrderDate 
OrderItemCode 
OrderQty 
 
I have another table OrderItem 
with a single column OrderItemCode 
 
The table OrderItem has five rows with OrderItemCode's A, B, C, D, E. This is a constant number of rows. 
 
The table OrderLine has eight rows 
 
o1 , 10/14/2011, &nbsp;A , 10 
o1 , 10/14/2011, &nbsp;B , 12 
o2 , 10/14/2011, &nbsp;A , 6 
o2 , 10/15/2011, &nbsp;C , 5 
o3 , 10/14,2011, &nbsp;D , 11 
o3 , 10/16/2011, &nbsp;E , 9 
o3 , 10/16/2011, &nbsp;D , 5 
o3 , 10/17/2011, &nbsp;B , 11 
 
I want to convert this to a table, with these seven columns 
 
OrderNumber, OrderDate, A, B, C, D, E 
 
And the final output should be as follows, since I wnt to group by OrderNumber and OrderDate 
 
 
o1 , 10/14/2011, 10 , 12 , , , , 
 
o2 , 10/14/2011, 6 &nbsp;, &nbsp; &nbsp;, , , , 
o2 , 10/15/2011, &nbsp; &nbsp;, &nbsp; &nbsp;, 5 , , , 
o3 , 10/14/2011, &nbsp; , &nbsp; &nbsp;, ,11 , , 
 
o3 , 10/16/2011, &nbsp; , &nbsp; &nbsp;, ,5 ,9 , 
 
o3 , 10/17/2011, &nbsp; , 11 &nbsp; , , , ,
</div>
</div>

I have a table OrderLine

OrderNumber
OrderDate
OrderItemCode
OrderQty

I have another table OrderItem
with a single column OrderItemCode

The table OrderItem has five rows with OrderItemCode's A, B, C, D, E. This is a constant number of rows.

The table OrderLine has eight rows

o1 , 10/14/2011,  A , 10
o1 , 10/14/2011,  B , 12
o2 , 10/14/2011,  A , 6
o2 , 10/15/2011,  C , 5
o3 , 10/14,2011,  D , 11
o3 , 10/16/2011,  E , 9
o3 , 10/16/2011,  D , 5
o3 , 10/17/2011,  B , 11

I want to convert this to a table, with these seven columns

OrderNumber, OrderDate, A, B, C, D, E

And the final output should be as follows, since I wnt to group by OrderNumber and OrderDate


o1 , 10/14/2011, 10 , 12 , , , ,

o2 , 10/14/2011, 6  ,    , , , ,
o2 , 10/15/2011,    ,    , 5 , , ,
o3 , 10/14/2011,   ,    , ,11 , ,

o3 , 10/16/2011,   ,    , ,5 ,9 ,

o3 , 10/17/2011,   , 11   , , , ,

COnvert rows to columns

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.