PIVOT

[Keyth]

Hello

I am trying to work the PIVOT function but not doing very well at it!

How do you suppose I pivot the query below to list the Year/Month as rows and stock_code as column, values to list along the rows will be sum([aii].[QUANTITY]) as Qty

SELECT CAST(CONVERT(CHAR(6), [ai].[INVOICE_DATE], 112) As datetime) AS InvMonth, [aii].[stock_code],
 sum([aii].[QUANTITY]) as Qty, [aii].[JOB_NUMBER]
  FROM [Archives].[dbo].[ARCHIVED_INVOICE] as [ai], [Archives].[dbo].[ARCHIVED_INVOICE_ITEM] as [aii]
where [ai].[invoice_number] = [aii].[invoice_number] and (not [aii].[description] like '%Carriage%') and ([ai].[name] like 'MARKUS%' or [ai].[name] like 'MARCUS%' or [ai].[name] like 'ELLIOT%' )
and not stock_code  = 'm' and not stock_code  Like 'TR%' AND NOT [AI].[INVOICE_TYPE] LIKE '%CREDIT%' and ai.invoice_date between '20060101' and '20090101' 
group by CAST(CONVERT(CHAR(6), [ai].[INVOICE_DATE], 112) As datetime) , [aii].[stock_code], [aii].[JOB_NUMBER]
order by CAST(CONVERT(CHAR(6), [ai].[INVOICE_DATE], 112) As datetime) desc;

Any help much appreciated

Keyth