Ms. Access crosstab query to a pivot in sql server

• Briceston

  SSCrazy

  Points: 2899

  More actions

  June 30, 2016 at 2:51 pm

  #327913

  How would write the below Ms.Access code into sql server using the pivot function?

  TRANSFORM Count([101_Test].AdmitNumber) AS CountOfAdmitNumber

  SELECT [101_Test].CCSCategory, Sum([101_Test].diffAdmitDays) AS SumOfdiffAdmitDays, Count([101_Test].AdmitNumber) AS CountOfAdmitNumber1

  FROM 101_Test

  GROUP BY [101_Test].Category

  PIVOT [101_Test].AdmitIndICT;

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  July 4, 2016 at 10:40 am

  #1888342

  What have you tried so far.

  You might be better off giving us some sample data and the desired output.

  PIVOT works in much the same way as in Access:

  You need a pivot key field and the row fields that will turn into the columns of the pivot key.

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  July 5, 2016 at 5:27 am

  #1888418

  You could just use the crosstab technique in SQL Server. I find they're generally faster than pivot. Jeff has an article on the subject at http://www.sqlservercentral.com/articles/T-SQL/63681/ that's a really good place to start learning them.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions