sql server - SQL pivot get products by orderNr -

i have 2 tables:

• 'orderheader' columns ordernr , ordercontact.
• 'orderdetail columns ordernr , orderserial.

for each ordernr there max 3 orderserials. so, i'm trying table columns:

• ordernr
• odercontact
• orderserial1
• orderserial2
• orderserial3

i'm stuck pivot

    select  ordernr,         odercontact,         [1] orderserial1,         [2] orderserial2,         [3] orderserial3     (select h.ordernr ordernr,             ordercontact odercontact,             orderserialsnr     orderheader h inner join orderdetail d on h.ordernr = d.ordernr     ) pivsource pivot (count(orderserialsnr) orderserialsnr in([1],[2],[3])) pvt 

i used adventureworks db this.

   use [adventureworks2008r2] go  create view [test].[orderheader] (ordernr,ordercontact) select  salesorderid,         firstname + ' ' + lastname          sales.salesorderheader inner join                       sales.customer on sales.salesorderheader.customerid = sales.customer.customerid inner join                       person.person on sales.customer.personid = person.person.businessentityid go  create view [test].[orderdetail] (ordernr,orderserialsnr,price) select [salesorderid]       ,[productid]       ,[unitprice]   [adventureworks2008r2].[sales].[salesorderdetail]   salesorderid in (select salesorderid                         [adventureworks2008r2].[sales].[salesorderdetail]                         group salesorderid                         having count(salesorderid) < 4) go 

please cause don't know anymore :).

to data in pivot table in claus should have correct data table

you can more idea query

if 1 , 2 not in table not appear count , @ column value

create table #orderheader (ordernr int, ordercontact char(5))  create table #orderdetail (ordernr int, orderserial varchar(10) )  insert #orderheader values ( 1, 'x') insert #orderdetail values ( 1, 'x1') insert #orderdetail values ( 1, 'x2') insert #orderdetail values ( 1, 1) insert #orderdetail values ( 1, 2)   select * #orderheader select * #orderdetail  select  ordernr,         odercontact,         [1] orderserial1,         [2] orderserial2,         [3] orderserial3     (select h.ordernr ordernr,             ordercontact odercontact,             orderserial     #orderheader h inner join #orderdetail d on h.ordernr = d.ordernr     ) pivsource pivot (count(orderserial) orderserial in([1],[2],[3])) pvt   drop table #orderheader drop table #orderdetail