Create a "Pivot" table

Question

Post reply

Create a "Pivot" table

Bryan P. Clauss

SSC Eights!

Points: 959
More actions
January 4, 2006 at 1:15 pm

#166663

I need to create a "Pivot" table similiar to the Access Pivot Table.
I have 2 tables, Code_Description, and Transactions.

Code_Description
ID (Code)
Description
Status
TransactionID
Tran_Code
Date
Quantity
SalesRep

Now, I need to create a Total column based on each Code in the Code_Description table, with each row based on the SalesRep ID.
Example:
SalesRep Code 1 Code 2 Code 3
Joe 23 42 12
Bob 59 32 62
Can this be done?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 1

SQL Server 2000 or SQL Server 2005? The reason I ask is there's a new PIVOT T-SQL command in SQL Server 2005.

If 2000, something like the following works (and I understand how your tables are referenced):

SELECT 
  T.SalesRep,
  SUM(CASE CD.Description WHEN 'Code 1' THEN 1 ELSE 0 END ) AS [Code 1],
  SUM(CASE CD.Description WHEN 'Code 2' THEN 1 ELSE 0 END ) AS [Code 2],
  SUM(CASE CD.Description WHEN 'Code 3' THEN 1 ELSE 0 END ) AS [Code 3]
FROM Transactions T
  INNER JOIN Code_Description CD ON T.Tran_Code = CD.ID
GROUP BY T.SalesRep

For 2005, something like the following works:

SELECT T.SalesRep, [Code 1], [Code 2], [Code 3]
FROM Transaction T
  INNER JOIN Code_Description CD ON T.Tran_Code = CD.ID
PIVOT(COUNT(*) FOR CD.Description IN ([Code 1], [Code 2], [Code 3])) AS PVT

Or something to that effect.

K. Brian Kelley
@kbriankelley

Bryan P. Clauss SSC Eights! Points: 959 More actions · Answer 2

I was afraid that that was what I needed to do. (In fact I had already coded it that way.)

That 2005 thing is yet another good reason to upgrade SQL.

Thanks, all!

steve dassin SSCrazy Points: 2396 More actions · Answer 3

For all types of pivoting/xtabs check out the RAC utility @

http://www.rac4sql.net

Similar to Access (ie. dynamic pivoting) but much more powerful as well as easy to use.

As for the 2005 PIVOT operator it is primitive even by Access standards.How could it possibly be a reason to upgrade?

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 4

Pivot being primitive in 2005... I'd have to agree. Can't stick a subquery where you specify the columns for the PIVOT operator, meaning you have to hardcode them. That means going towards a dynamic SQL solution if there is a requirement to determine the columns on the fly.

K. Brian Kelley
@kbriankelley

SalesRep	Code 1	Code 2	Code 3
Joe	23	42	12
Bob	59	32	62