Trying to get Pivot Table working in MS SQL 2005
-
I have never used a pivot before and I am starting to think that one doesnt work. Please help provide guidence. I have 3 tables _Contracts, which contain contractid and sepworkstatus _contracts -------------------- 1,[Sent] 2,[Sent] 3,[No Further Action] _vendors which has umbrellaname and vendornumber _contracts.vendorid is foreign to _vendors.vendornumber _vendors --------------------- um1,1 um2,2 um2,3 and _UmbrellaData which has umbrellaname and a few other needless columns. umbrellaname connects _UmbrellaData and _vendors _UmbrellaData --------------------- um1 um2 The outer apply will grab a list of all the statuses and their counts for each umbrella company The results will look similar to um1,sent,1 um2,No Further Action, 1 um2,sent,1 um3,Executed my output needs to look like this umbrellaname, sent, [No Further Action], [Executed] um1,1,null,null um2,1,1,null My current query looks like this select u.umbrellaname, u.stream, functionalarea, [In Negotiation],[Prework Complete],[Sent],[No Further Action],[Executed],[Separation Work Started],[Ready to Send],[Prework Started] from dbo._UmbrellaData u outer apply ( select v.umbrellaname, c.sepworkstatus, count(c.sepworkstatus) as counte from dbo._Vendor v join dbo._Contracts c on c.vendorid = v.vendornumber group by v.umbrellaname, c.sepworkstatus ) as oa pivot(max(oa.counte) for oa.sepworkstatus in ( [In Negotiation],[Prework Complete],[Sent],[No Further Action],[Executed],[Separation Work Started],[Ready to Send],[Prework Started] )) as cw where oa.umbrellaname = u.umbrellaname Unfortunately when I run it I get the following errors. Msg 8156, Level 16, State 1, Line 2 The column 'umbrellaname' was specified multiple times for 'cw'. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "oa.umbrellaname" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "u.umbrellaname" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "u.umbrellaname" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "u.stream" could not be bound. Why? How do I get a pivot to work with an outer apply? Any help on this would be greatly appreciated.
-
I have never used a pivot before and I am starting to think that one doesnt work. Please help provide guidence. I have 3 tables _Contracts, which contain contractid and sepworkstatus _contracts -------------------- 1,[Sent] 2,[Sent] 3,[No Further Action] _vendors which has umbrellaname and vendornumber _contracts.vendorid is foreign to _vendors.vendornumber _vendors --------------------- um1,1 um2,2 um2,3 and _UmbrellaData which has umbrellaname and a few other needless columns. umbrellaname connects _UmbrellaData and _vendors _UmbrellaData --------------------- um1 um2 The outer apply will grab a list of all the statuses and their counts for each umbrella company The results will look similar to um1,sent,1 um2,No Further Action, 1 um2,sent,1 um3,Executed my output needs to look like this umbrellaname, sent, [No Further Action], [Executed] um1,1,null,null um2,1,1,null My current query looks like this select u.umbrellaname, u.stream, functionalarea, [In Negotiation],[Prework Complete],[Sent],[No Further Action],[Executed],[Separation Work Started],[Ready to Send],[Prework Started] from dbo._UmbrellaData u outer apply ( select v.umbrellaname, c.sepworkstatus, count(c.sepworkstatus) as counte from dbo._Vendor v join dbo._Contracts c on c.vendorid = v.vendornumber group by v.umbrellaname, c.sepworkstatus ) as oa pivot(max(oa.counte) for oa.sepworkstatus in ( [In Negotiation],[Prework Complete],[Sent],[No Further Action],[Executed],[Separation Work Started],[Ready to Send],[Prework Started] )) as cw where oa.umbrellaname = u.umbrellaname Unfortunately when I run it I get the following errors. Msg 8156, Level 16, State 1, Line 2 The column 'umbrellaname' was specified multiple times for 'cw'. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "oa.umbrellaname" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "u.umbrellaname" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "u.umbrellaname" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "u.stream" could not be bound. Why? How do I get a pivot to work with an outer apply? Any help on this would be greatly appreciated.
-