Solved

PIVOT Equiv in T-SQL??

Posted on 1997-11-20
3
789 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

0
Comment
Question by:jcorbin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 7

Expert Comment

by:spiridonov
ID: 1089240
This fature dosn't exist in SQL server, the easiest way is to attach the  table in Access and use it's function
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 100 total points
ID: 1089241
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
 

Expert Comment

by:djcunning
ID: 7933850
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question