Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PIVOT Equiv in T-SQL??

Posted on 1997-11-20
3
Medium Priority
?
801 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 400 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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

715 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