Sale for last 10 month sales

Transact-SQL
1

I have a Transaction table where i get all my sale Invoice record entry and Credit Invoice(which we get return after sale).
columns as ItemNo,Item DEsc,TransType(Sale-inv or Credit-inv)
Now i need last 10 month sales(Each month in one column with itemNo,description) for the particular item.

Sale Dt Type Item No Item Desc TRansType Quantity
17/08/2014 CASH 263513C100 CAP-OIL FILTER Sale-INV 1
17/08/2014 CASH 924512K500 LAMP ASSY-RR REFLEX LH Sale-INV 1
17/08/2014 CASH 583023QA10 MESH ASSY-FR BUMPER CTR Sale-INV 1
18/09/2014 CASH 583023QA10 PAD KIT-RR DISC BRAKE Cred-INV 2
18/08/2014 CASH 988503W000 BLADE ASSY-RR WIPER Cred-INV 1
18/08/2014 CASH 273012B010 COIL ASSY-IGNITION Cred-INV 1
18/08/2014 CASH 263304X000 OIL FILTER Sale-INV 1
19/08/2014 CASH 281132G000 FILTER-AIR CLEANER Sale-INV 1
19/08/2014 CASH 971332B010 FILTER-AIR CLEANER Sale-INV 1

Item Item Desc Aug Sep
263513C100 CAP-OIL FILTER 1
924512K500 LAMP ASSY-RR REFLEX LH 1
583023QA10 MESH ASSY-FR BUMPER CTR 1 2
988503W000 PAD KIT-RR DISC BRAKE 1
273012B010 BLADE ASSY-RR WIPER 1
263304X000 COIL ASSY-IGNITION 1
281132G000 OIL FILTER 1
971332B010 FILTER-AIR CLEANER 1

2

I think this is what you are looking for

drop table if exists #Test
create table #Test
(SaleDate date,
SaleType nvarchar(20),
ItemNum varchar(20),
ItemDesc nvarchar(50),
TransactionType nvarchar(20),
Quantity int)

insert into #Test values
('08/17/2014', 'CASH', '263513C100','CAP-OIL FILTER','Sale-INV','1'),
('08/17/2014', 'CASH', '924512K500','LAMP ASSY-RR REFLEX LH','Sale-INV','1'),
('08/17/2014', 'CASH', '583023QA10','MESH ASSY-FR BUMPER CTR','Sale-INV','1'),
('09/18/2014', 'CASH', '583023QA10','PAD KIT-RR DISC BRAKE','Cred-INV','2'),
('08/18/2014', 'CASH', '988503W000','BLADE ASSY-RR WIPER','Cred-INV','1'),
('08/18/2014', 'CASH', '273012B010','COIL ASSY-IGNITION','Cred-INV','1'),
('08/18/2014', 'CASH', '263304X000','OIL FILTER','Sale-INV','1'),
('08/19/2014', 'CASH', '281132G000','FILTER-AIR CLEANER','Sale-INV','1'),
('10/19/2014', 'CASH', '971332B011','FILTER-AIR CLEANER1','Sale-INV','1'),
('11/19/2014', 'CASH', '971332B012','FILTER-AIR CLEANER2','Sale-INV','1'),
('12/19/2014', 'CASH', '971332B013','FILTER-AIR CLEANER3','Sale-INV','1'),
('07/19/2014', 'CASH', '971332B014','FILTER-AIR CLEANER4','Sale-INV','1')


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + DateName(month, SaleDate)
            FROM #test c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ItemNum, ItemDesc, ' + @cols + ' from 
            (
                select ItemNum
                    , ItemDesc
                    ,  DateName(month, SaleDate) Monthname
					,Quantity
                from #test
           ) x
            pivot 
            (
                 sum(quantity)
                for Monthname in (' + @cols + ')
            ) p '


execute(@query)

select * from #Test