Pivot Function in SQL 2005

Question

Pivot Function in SQL 2005

B Jackson

SSC Enthusiast

Points: 191
More actions
January 25, 2006 at 1:25 pm

#167912

I am new to pivot tables and SQL. I am using the following T-SQL code to generate a pivot table.
USE
NWIND_2000SQL;
GO
SELECT
rptperiod, [Doe, Jane], [Adams, John]
FROM

(
SELECT rptperiod, coder, sumofcompleted
FROM
VWDBO_TBL_CODERTRAINING_CODER_CROSSTAB) p
PIVOT
(
sum
(sumofcompleted)
FOR
coder IN
(
[Doe, Jane], [Adams, John )
)
AS pvt
ORDER
BY rptperiod
The problem is that I have 192 names in addition to the [Doe, Jane] and [Adams, John]
Does anyone know of a way to include the names without having to type each of the 192 names in brackets. Also the names are subject to change so I need a generic way of doing this.
Any help will be appreciated Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

So far, without building a dynamic SQL query, I haven't found a way. I wish I could execute a subquery in the parentheses but every method I've tried has failed.

K. Brian Kelley
@kbriankelley

B Jackson SSC Enthusiast Points: 191 More actions · Answer 2

Thanks for replying, Can you tell me a little bit more (point me in the right direction) about how the dynamic query would interact with the pivot statement

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

The same as it would in any other situation. Robert Marda has several articles on the use of Dynamic SQL and the problems therein. SQL Server 2005, at least with respect to stored procedures, addresses one of those issues, and that's impersonation. But basically you'd be building the T-SQL string to execute and then execute it using EXEC or sp_executesql.

K. Brian Kelley
@kbriankelley

B Jackson SSC Enthusiast Points: 191 More actions · Answer 4

B Jackson

SSC Enthusiast

Points: 191

January 26, 2006 at 8:00 am

#617178

Thank you, I'll take a look.

B Jackson SSC Enthusiast Points: 191 More actions · Answer 5

I found the solution, yippee, it works perfectly.

http://www.sqlteam.com/item.asp?ItemID=2955

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

Yup, this one involves dynamic SQL. The key here is that the end user will have to have SELECT rights against the table where the pivot data is coming from. In SQL Server 2005 you can use the EXECUTE AS clause on the stored procedure to specify under what context to run. That would prevent the necessity for the end user to directly have permissions against said table.

K. Brian Kelley
@kbriankelley

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

I am a big fan of the Ron Soukup's and Kalen Delaney's book "Inside Microsoft SQL Server 7' (there are later versions of the book out there). I already quoted Running Totals solution from this book in one of the posts recently. The same chapter 12 "Transact SQL Examples and Brainteasers" has a good solution for Pivots on the page 712. They use the idea by Steve Roti from DBMS magazine years earlier. The solution is to create a "Unit Matrix" as

1 1 0 0 0

2 0 1 0 0

3 0 0 1 0

4 0 0 0 1

You have to give fields the names and basically you have to have the appropriate dimentions to this matrix. Then you join your table with unpivoted data with this table while multiplying your values by the fileds of your unit table. The result will be a pivoted table.

Regards,Yelena Varsha

steve dassin SSCrazy Points: 2396 More actions · Answer 8

An easy and comprehensive solution to pivoting/xtabs has been around for a while.

Check out RAC @

http://www.rac4sql.net

We will be optimizing a solution for 2005.