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
Comments
Post a Comment