PIVOT problem : produces multiple lines

Question

PIVOT problem : produces multiple lines

alef

SSC Enthusiast

Points: 110
More actions
August 11, 2009 at 12:04 pm

#224737

I'm trying to use the PIVOT function
CREATE TABLE #tblResults
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[ContestId] [int] NOT NULL,
[RunnerId] [int] NOT NULL,
[Time] [time](2) NOT NULL
)
INSERT INTO #tblResults( [ContestId], [RunnerId], [Time])
SELECT 1, 100, '00:00:10'UNION ALL
SELECT 1, 200, '00:00:09'UNION ALL
SELECT 1, 300, '00:00:12'UNION ALL
SELECT 2, 100, '00:00:11'UNION ALL
SELECT 2, 200, '00:00:13'
--SELECT * FROM #tblResults
SELECT RunnerId, [1] as Contest1 ,[2] as Contest2 FROM #tblResults
PIVOT
(
Count(Id)
FOR ContestId IN
([1],[2])
) AS pvt
ORDER BY pvt.RunnerId;
DROP TABLE #tblResults
It gives me the following result (there are multiple lines for RunnerId 100 and 200)
RunnerIdContest1Contest2
10010
10001
20010
20001
30010
but I should expect the following result
RunnerIdContest1Contest2
10011
20011
30010
I've been struggling hours with this. So it is better to ask here.
What I'm doing wrong with the PIVOT?

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

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

Hi you have posted this in the SS2K5 forum but you have a TIME datatype...I dont think this was introduced until SQL 2008...

If I run this on 2K5 I get the results you expect

CREATE TABLE #tblResults

(

[Id] [int] IDENTITY(1,1) NOT NULL,

[ContestId] [int] NOT NULL,

[RunnerId] [int] NOT NULL,

)

INSERT INTO #tblResults( [ContestId], [RunnerId])

SELECT 1, 100 UNION ALL

SELECT 1, 200 UNION ALL

SELECT 1, 300 UNION ALL

SELECT 2, 100 UNION ALL

SELECT 2, 200

--SELECT * FROM ##tblResults

SELECT RunnerId, [1] as Contest1 ,[2] as Contest2 FROM #tblResults

PIVOT

(

Count(Id)

FOR ContestId IN

([1],[2])

) AS pvt

ORDER BY pvt.RunnerId;

DROP TABLE #tblResults

10011

20011

30010

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

alef SSC Enthusiast Points: 110 More actions · Answer 2

You are right. I especially moved to SQL 2008 to use this time field.

I don't think that you have already a forum for SQL 2008?

If I run it without the time field like you are doing it runs also OK on SQL2008.

So then must it be a bug in SQL 2008.

Does somebody know something about this problem?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

It isn't a bug in SQL Server 2008 (and yes, there are SQL Server 2008 forums available). I changed the datatype from time to datetime and ran your original query and got the same result you did in SQL Server 2008.

Not sure how to fix it unfortunately, but it has to be something to do with the query/data.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Using your test setup try the following:

select RunnerId, [1] as Contest1, [2] as Contest2

from

(select Id, RunnerId, ContestId from #tblResults) p

pivot (

count([Id])

for ContestId in ([1],[2])

) as pvt

order by RunnerId;

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

gah (8/11/2009)
Hi you have posted this in the SS2K5 forum but you have a TIME datatype...I dont think this was introduced until SQL 2008...
If I run this on 2K5 I get the results you expect
CREATE TABLE #tblResults
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[ContestId] [int] NOT NULL,
[RunnerId] [int] NOT NULL,
)
INSERT INTO #tblResults( [ContestId], [RunnerId])
SELECT 1, 100 UNION ALL
SELECT 1, 200 UNION ALL
SELECT 1, 300 UNION ALL
SELECT 2, 100 UNION ALL
SELECT 2, 200
--SELECT * FROM ##tblResults
SELECT RunnerId, [1] as Contest1 ,[2] as Contest2 FROM #tblResults
PIVOT
(
Count(Id)
FOR ContestId IN
([1],[2])
) AS pvt
ORDER BY pvt.RunnerId;
DROP TABLE #tblResults
10011
20011
30010

It's not a bug... it's the code and a bit of a misunderstanding as to what is necessary for PIVOT to work. You need to use aggregation for this to work the way you want it to. PIVOT is a comparable PITA and is slower than a good ol' fashioned cross-tab. Please see the following for proof on that...

http://www.sqlservercentral.com/articles/T-SQL/63681/

... of course, there's also a PIVOT example in that article, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

alef SSC Enthusiast Points: 110 More actions · Answer 6

I've tried the solution of SSCrazy Eights and this solution is perfect.

Is there an explanation why your solution is working and mine not?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Like Jeff said, your code was wrong. Other than that, I have no explaination. All I did was build the query in pieces following the sample code in BOL (Books Online, the SQL Server Help System that can be accessed from SSMS using the {f1} function key) that builds a PIVOT query using the AdventureWorks database, plus a little guess work along the way.

drew.allen SSC Guru Points: 76987 More actions · Answer 8

alef (8/11/2009)
I've tried the solution of SSCrazy Eights and this solution is perfect.
Is there an explanation why your solution is working and mine not?

I think that this line from BOL indirectly gives the answer:

Books Online
performs aggregations where necessary on any remaining column values

You didn't use a subquery, so it's using each column in the table and using the ContestID as the pivoted columns, the ID as a Count(), and the RunnerID and the Time as a group.

Lynn did use a subquery, but specified only THREE of the four columns. So Lynn's solution used the ContestID as the pivoted columns, the ID as the Count(), but only grouped on the RunnerID instead of grouping on the RunnerID and the Time.

As a test, I used the subquery, just like Lynn, but added the fourth column into the subquery and got multiple rows. When I removed Time from the subquery, I got the desired results.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

drew.allen (8/11/2009)
alef (8/11/2009)
I've tried the solution of SSCrazy Eights and this solution is perfect.
Is there an explanation why your solution is working and mine not?
I think that this line from BOL indirectly gives the answer:
Books Online
performs aggregations where necessary on any remaining column values
You didn't use a subquery, so it's using each column in the table and using the ContestID as the pivoted columns, the ID as a Count(), and the RunnerID and the Time as a group.
Lynn did use a subquery, but specified only THREE of the four columns. So Lynn's solution used the ContestID as the pivoted columns, the ID as the Count(), but only grouped on the RunnerID instead of grouping on the RunnerID and the Time.
As a test, I used the subquery, just like Lynn, but added the fourth column into the subquery and got multiple rows. When I removed Time from the subquery, I got the desired results.
Drew

Drew, Thank you for digging into it more and providing a reasonable explaination. It makes sense. I wish I had had the time to dig deeper at the time.