Pivot data

Question

Post reply

Pivot data

ramses2nd

Hall of Fame

Points: 3720
More actions
September 24, 2010 at 8:37 am

#240834

I've been trying to use the PIVOT function but cannot come up with the right syntax.
Given the following table data:
Application User CallTime
Excel Bob Jones 10
Excel Tim Ralph 5
Excel Rob Tims 7
Word Tim Ralph 15
Word Bob Jones 5
What Query can I use to get this format:
Application CallTime Bob Jones Tim Ralph Rob Tims
Excel 22 10 5 7
Word 20 5 15 NULL

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

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

The only values for User are Bob Jones , Tim Ralph , Rob Tims ?

ramses2nd Hall of Fame Points: 3720 More actions · Answer 2

No there are actually about 10 users but for the example I only included these three. The same for application would apply, in fact there are around 15 different applications.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Insteadof PIVOT I'd rather use the CrossTab concept as described in the related link in my signature.

Once the static CrossTab works as required you might want to have a look at the DynamicCrossTab article (also refernced in my sig) to change it to cover a flexible number of users (if needed).

If you need any further assistance please post what you've tried so far and where you get stuck.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 4

This might give you an idea of how you could use a PIVOT query

IF NOT OBJECT_ID('tempdb.dbo.#t', 'U') IS NULL DROP TABLE #t;

SELECT 'Excel' AS Application, 'Bob Jones' AS [User], 10 AS CallTime INTO #t UNION ALL

SELECT 'Excel', 'Tim Ralph', 5 UNION ALL

SELECT 'Excel', 'Rob Tims', 7 UNION ALL

SELECT 'Word', 'Tim Ralph', 15 UNION ALL

SELECT 'Word', 'Bob Jones', 5;

SELECT * FROM #t;

SELECT Application,

COALESCE([Bob Jones], 0) + COALESCE([Tim Ralph], 0) + COALESCE([Rob Tims], 0) AS CallTime,

[Bob Jones],

[Tim Ralph],

[Rob Tims]

FROM #t

PIVOT (MAX(CallTime) FOR [User] IN ([Bob Jones],[Tim Ralph],[Rob Tims])) AS Z;