"TRANSFORM Sum(tblClaims.[CurrentLossReserve]) AS SumOfCurrentLossReserve SELECT tblClaims.[LossStatCode], tblClaims.[ClaimCode], Sum(tblClaims.[CurrentLossReserve]) AS [Total Of CurrentLossReserve] FROM tblClaims GROUP BY tblClaims.[LossStatCode], tblClaims.[ClaimCode] PIVOT Format([DateReport],'yyyy-mm'); "



how to convert this code to t-sql this runs in msaccess

It's a bit hard to understand what you are really trying to achieve without some more detailed explanation. However using my best guess I come out some suggestion for you so that you can try using my script as below.

1. Create test table:
CREATE TABLE tblClaims (
LossStatCode int
, ClaimCode varchar(10)
, CurrentLossReserve int
, DateReport DateTime)

2. Create test data:
-- 1,a1
INSERT INTO tblClaims VALUES (1,'a1',3,'2009-10-01')
INSERT INTO tblClaims VALUES (1,'a1',4,'2009-10-02')
INSERT INTO tblClaims VALUES (1,'a1',5,'2009-10-03')
INSERT INTO tblClaims VALUES (1,'a1',6,'2010-01-01')
INSERT INTO tblClaims VALUES (1,'a1',3,'2010-01-02')
INSERT INTO tblClaims VALUES (1,'a1',4,'2010-01-03')
-- 1,a2
INSERT INTO tblClaims VALUES (1,'a2',13,'2009-10-01')
INSERT INTO tblClaims VALUES (1,'a2',14,'2009-11-01')
INSERT INTO tblClaims VALUES (1,'a2',15,'2009-11-05')
INSERT INTO tblClaims VALUES (1,'a2',16,'2010-02-01')
INSERT INTO tblClaims VALUES (1,'a2',13,'2010-02-05')
INSERT INTO tblClaims VALUES (1,'a2',14,'2010-02-09')
-- 1,a3
INSERT INTO tblClaims VALUES (1,'a3',23,'2009-12-01')
INSERT INTO tblClaims VALUES (1,'a3',24,'2009-12-07')
INSERT INTO tblClaims VALUES (1,'a3',25,'2009-12-21')
INSERT INTO tblClaims VALUES (1,'a3',26,'2010-03-01')
INSERT INTO tblClaims VALUES (1,'a3',23,'2010-03-09')
INSERT INTO tblClaims VALUES (1,'a3',24,'2010-03-11')

3. Script to review:
SELECT LossStatCode,Convert(varchar(7),DateReport,121) [ReportMonth], a1 AS a1, a2 AS a2, a3 AS a3
FROM
(SELECT CurrentLossReserve, ClaimCode, LossStatCode, Convert(varchar(7),DateReport,121) [DateReport]
FROM tblClaims) p
PIVOT
(
SUM (CurrentLossReserve)
FOR ClaimCode IN
( a1,a2,a3)
) AS pvt
ORDER BY LossStatCode

4. Test result:
LossStat ReportMonth
Code a1 a2 a3
------------ ----------- ----------- ----------- -----------
1 2009-10 12 13 NULL
1 2009-11 NULL 29 NULL
1 2009-12 NULL NULL 72
1 2010-01 13 NULL NULL
1 2010-02 NULL 43 NULL
1 2010-03 NULL NULL 73


Try yourself even if this is not exactly what you wanted by changing columns a bit.

Well if i don't have any limit on number of claim code...like i don't know its a1,a2,a3 there can be many..so what's the resolution for that....as in my access query which i originally posted i am usind date part as to be displayed as column and there can be many

It seems like you want to pivot on the Month instead of ClaimCode.

Then tweak the original script to the following script.
SELECT LossStatCode,ClaimCode,[2009-10] as Month0910,[2009-11] as Month0911,[2009-12] as Month0912,[2010-01] as Month1001,[2010-02] as Month1002
FROM
(SELECT CurrentLossReserve, ClaimCode, LossStatCode, Convert(varchar(7),DateReport,121) [YYYYMM]
FROM tblClaims) p
PIVOT
(
SUM (CurrentLossReserve)
FOR [YYYYMM] IN
( [2009-10],[2009-11],[2009-12],[2010-01],[2010-02],[2010-03])
) AS pvt
ORDER BY LossStatCode

As the count of YYYYMM is limited and known, you may not have problem typing the target month range.

Best option is not to do this in T-SQL - do this in your presentation layer. Most presentation tools can pivot data easily.
If you are determined to do this in T-SQL (for some reason everyone seems to be) you can use Terry's suggestions. If you do not know all the values you need to pivot on then you now need to write some dynamic sql to build a csv string of the values and insert this into your pivot statement.

If you want more information about why pivoting is not a good idea in SQL try googling for some of Celko's responses on the subject - he is rather forthright on the matter.

pootle flump ur codings are working excelent.