Solved

PIVOT Equiv in T-SQL??

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query display the latest row 10 51
Need help with a query 3 36
Many to one in one row 2 35
SQL Server Compression Decision 5 37
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 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