[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 813
  • Last Modified:

PIVOT Equiv in T-SQL??

Is there a comparable function to Access's PIVOT?? I would like to sum all
the records by employee location by Job Title, where i'd be doing a group by Job Title and pivot on Location;


JobTitle    LocationA    LocationB    LocationC    Total
Job01            22          22           33         77
Job02            11          10           10         31

etc....


ANy help is appreciated greatly..................


John
jcorbin@apci.net

0
jcorbin
Asked:
jcorbin
1 Solution
 
Victor SpiridonovCommented:
This fature dosn't exist in SQL server, the easiest way is to attach the  table in Access and use it's function
0
 
cymbolicCommented:
Transform and Pivot are unique to MS Access.  spiridonov is right, the quickest and easiest way is to attach your SQL table to MS Access, then MS Access will perform the operations locally.
If you really can't do that, then if you don't have a really large number of locations, you can create your query in multiple steps, by first creating a temporary table, containing job title and mulitple location fields in each row.  Then run inserts from your source table into your temporary table, then use group by clause to extract the query in the format you want. It may seem like this would take some time, but in reality, it's only you doing explicitely what Access was doing implicitly through use of the transform/Pivot sequence.
0
 
djcunningCommented:
A useful procedure for doing this in T-SQL (Microsoft SQL Server) is given on this web page:

http://www.sqlteam.com/item.asp?ItemID=2955
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now