We help IT Professionals succeed at work.

PIVOT Equiv in T-SQL??

jcorbin
jcorbin asked
on
Medium Priority
832 Views
Last Modified: 2006-11-17
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

Comment
Watch Question

Victor SpiridonovDatabase Director

Commented:
This fature dosn't exist in SQL server, the easiest way is to attach the  table in Access and use it's function
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.