0

I have a SQL table with following fields

ID, Rly, Shed, Loco_no, shed, Date_failure, Equipt

I am using a cross tab query in access using the following script in MS Access 2007

TRANSFORM Count(Sheet1.[sno]) AS CountOfsno
SELECT Sheet1.[Equipt], Count(Sheet1.[sno]) AS [Total Of sno]
FROM Sheet1
GROUP BY Sheet1.[Equipt]
PIVOT Sheet1.[Shed];

How can this query be converted to a SQL Server stored procedure for use on an aspx page?

please help

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
user1235981
  • 21
  • 2
  • 7
  • possible duplicate of [TSQL equivalent of an MS Access Crosstab query](http://stackoverflow.com/questions/802630/tsql-equivalent-of-an-ms-access-crosstab-query) – Fionnuala Jun 20 '12 at 13:30
  • Search also for Pivot in the SQL Server tag. – Fionnuala Jun 20 '12 at 13:30
  • I have tried above mentioned example as : SELECT Equipt, [BSL] AS BSL, [AQ] AS AQ FROM (SELECT Equipt, Shed FROM PunctualityMain) ps PIVOT (COUNT (Equipt) FOR Equipt IN ([BSL], [AQ])) AS pvt But it generate an error as : Invalid column name 'Equipt'. – user1235981 Jun 20 '12 at 13:45
  • @user1235981 if you post some sample data that would be helpful – Taryn Jun 20 '12 at 14:17

1 Answers1

0

In SQL Server you will want to look at the PIVOT function. Considering you did not provide many details on your table or sample data, based on your attempt in the comments something like this might point you in the right direction.

SELECT *
FROM
(
    SELECT Equipt, Shed
    FROM PunctualityMain
) x
PIVOT
(
    COUNT(Equipt)
    FOR Shed IN ([BSL], [AQ])
) p

If you post some more details, then we could provide additional help.

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 1st Filed 'Equipt' data - TFP, TM, VCD, VCB, Gear, Bogie and 2nd Field 'Shed' data - BSL, AQ, TKD, AJJ, GZB, NKJ. Requirement is counting of Equipt in Shed like 22 TM in Shed BSL or 5 TFP in Shed AJJ. – user1235981 Jun 20 '12 at 14:27
  • Thanks for Ans above but name of eqipt are missing. Req. is TFP 5 in BSL, 2 in AQ; VCD 8 in BSL, 1 in AQ etc. – user1235981 Jun 20 '12 at 14:35
  • Please edit your OP with some sample data from your table as well as the expected result. Please include all fields that you need to have in your final product. – Taryn Jun 20 '12 at 14:41
  • I have modified your ans as per my requirement and it works for me. Thank you very much. – user1235981 Jun 20 '12 at 14:46
  • Equip BSL AQ TFP 0 3 TM 4 0 VCB 1 1 VCD 5 0 Here is the result. Can a total field also be added at end of each row like Equipt BSL AQ Total – user1235981 Jun 20 '12 at 14:50
  • putting the sample data in the comments is not helpful, please edit your original question and place the data there. – Taryn Jun 20 '12 at 15:14