April 22, 2009 at 9:34 am
Hi All,
I have used pivots quite a few times without problem. But I cant figure out whats happening in this example.
Here is my code:
Create Table #Data(
emp_id int
, [Name] varchar(200)
, ssn varchar(100)
, [State] varchar(100)
, Reason varchar(100)
, PDFTextBoxName varchar(200)
, [Value] float
)
insert into #Data( emp_id, [Name] , ssn, [State], Reason, PDFTextBoxName, [Value])
Select 5, 'ABRAHAM', 'xxx-22-3433', 'AL', 'Ord','DAP_Ord_AL',-102 union all
Select 5, 'ABRAHAM', 'xxx-22-3433', 'AL', 'Rnt','DAP_Rnt_AL',574
I just need to pivot now - on the basis of the column called "PDFTextBoxName".
So my X-axis becomes - PDFTextBoxName and the Y-axis will be the employee id.
So I wrote:
Select emp_id, DAP_Ord_AL, DAP_Rnt_AL
From #Data
Pivot (Sum([Value]) For PDFTextBoxName In (DAP_Ord_AL, DAP_Rnt_AL)) As pt
The query works, but I was expecting only one row. Now I get 2 rows with Nulls in them.
5-102NULL
5NULL574
What am I doing wrong?
April 22, 2009 at 9:45 am
April 22, 2009 at 9:48 am
It's because you're selecting from the base table directly, not from a derived table with just what you need.
Try this:
Select emp_id, 'Sum Value' as PivotOn, DAP_Ord_AL, DAP_Rnt_AL
From
(select emp_id, PDFTextBoxName, [Value]
from #Data) as Source
Pivot (Sum([Value]) For PDFTextBoxName In (DAP_Ord_AL, DAP_Rnt_AL)) As pt;
See if that gets you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2009 at 10:09 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply