Pivot Table within SQL query

Question

I have an SQL query here, which works perfectly fine:

select
R.[Source_Loaction] as VendorID
,R.[Article_Nbr]
,A.[Article_Desc]
,S.[Destination_Location] as Site
,AH.[External_Product_SType_Arth_Desc] as Product_Subtype
,datepart (week, R.[Scheduled_Ship_dae]) as WK
,SUM(S.[Major_Ship_Qty]) as UoM
,SUM(R.[Shipment_Qty]) as Qty

from [dbo].[FnR_RECEIVED_SHIPMENT_V] as R

INNER JOIN [dbo].[FnR_SOURCING_V] as S
ON (R.[Article_Nbr]=S.[Article_Nbr] AND R.[Destination_Location]=S.[Destination_Location])
INNER JOIN [dbo].[FnR_EXTERNAL_ARTICLE_V] as AH
ON R.[Article_Nbr]=AH.[Article_Nbr]
INNER JOIN [dbo].[FnR_ARTICLE_V] as A
ON R.[Article_Nbr]=A.[Article_Nbr]

Group by 
R.[Source_Loaction]
,R.[Article_Nbr]
,A.[Article_Desc]
,S.[Destination_Location]
,AH.[External_Product_SType_Arth_Desc]
,datepart (week, R.[Scheduled_Ship_dae])

I wish to place a PIVOT table as structured HERE:

PIVOT(sum(Qty) for [WK] in ([25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36])) as pivot

These two SQL functions are not working together and I am unsure where to place the pivot. Could someone assist as to where I have gone wrong on this one? The items 25 through 36 in the pivot tables are week start dates and this is what I wish to pivot on.

I appreciate any incite.

I am creating a pivot table on qty's based on week function. Once I can do this, I have my problem solved.

podiluska · Accepted Answer · 2014-06-17 16:29:12Z

1

select *
from 
(
     -- your first query here
) src
PIVOT(sum(Qty) for [WK] in ([25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36])) as pivot

as per the documentation

answered Jun 17, 2014 at 16:29

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3749070 Over a year ago

When I attempt this, I receive the following error message:

user3749070 Over a year ago

Msg 156, Level 15, State 1, Line 31 Incorrect syntax near the keyword 'pivot'.

Collectives™ on Stack Overflow

Pivot Table within SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related