Pivot Task Error - Duplicate pivot key

I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.

All worked fine, but now I get this error message:

[ytd_pivot [123]] Error: Duplicate pivot key value "6".

The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.

Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren′t available at this moment while data extracting is still going on?

any hints?

Cheers
Markus

The pivot transform takes values like:

cust# Product Qty

-- -

1 Ham 4

1 Chips 2

1 Flan 1

2 Chips 3

2 Beer 19

and produces rows like:

cust# HamQty ChipsQty FlanQty BeerQty

-- - - - -

1 4 2 1 null

2 null 3 null 19

so what to do with input data like this?

cust# Product Qty

-- -

1 Ham 4

1 Chips 2

1 Chips 5

Which value should go into the ChipsQty column 2 or 5?

Most application would want 7, and so we suggest that the pivot be preceded by an aggregate transform to ensure that there is only 1 row for each distinct value of the pivot key. If not, you will see the error you report.

hope this helps

Pivot Question

I am hoping someone can help me with a procedure. I know the result involves a pivot table, however I don't completely understand how Pivot/Unpivot works or whow to write one:

Here's my scenatio: I have 2 columns of data by 48 rows.

Column A and Column B

Time Data

00:00:00 835
00:30:00 763
01:00:00 669
01:30:00 630
02:00:00 588
02:30:00 585
03:00:00 591
03:30:00 570
04:00:00 616
04:30:00 690

....

I want to turn this into a table that has 1 row of data that is 48 columns wide. The previous Column A would now become the 48 Column headings and Column B would be the data in the Row.

00:00:00 00:30:00 01:00:00 01:30:00.....

835 763 669 630 ....

Any ideas?

You are correct, Jim: You can use a PIVOT for this if you are using SQL Server 2000. Something like this:

select [00:30:00],

[01:00:00],

...

[23:30:00]

from YourTable

pivot ( sum([Data]) for [Time] in

( [00:00:00],

[00:30:00],

...

[23:30:00]

) ) piv

|||Thanks, I was able to get it to work. I really appreciate the help|||

Jim:

When I was getting ready for work I had convinced myself that I had misunderstood the question. I had figured that what you wanted was to establish these partitions and then summarize the data as a pivot and that this wasn't going to work correctly.

I'm glad my second thoughts were wrong!

:-)

PIVOT for non-aggregate data?

I've used UNPIVOT for cleaning up data from tables that violate 1NF.
However, the PIVOT operator seems to behave a little differently, and I
guess I may have misunderstood it from using UNPIVOT (I know they're
not exact complements, in spite of their names). Could someone tell me
if I'm right on this? It seems that PIVOT is only useful in aggregates.
In other words, with the following data (schema can be inferred to
point of relevance):
-- Assume that it's known each person will have a maximum of three
cars. This isn't my exact problem, but can illustrate the conceptual
hurdle.
PersonName AutomobileNumber MakeModel
---
Joe 1 Peugeot505
Joe 2 HondaAccord
Joe 3 LincolnMarkIII
Bob 1 HondaAccord
Bob 2 DMCCoupe
and I want this result (in SQL 2000, I would build this w/ temp tables
and/or joins)
PersonName Automobile1 Automobile2 Automobile3
----
--
Joe Peugeot505 HondaAccord
LincolnMarkIII
Bob HondaAccord DMCCoupe NULL
I assume PIVOT isn't really supposed to be used like this, is it? It's
only for aggregates. Is that right? It'd be nice if the following
worked (and it may be syntactically incorrect as it sits, so forgive
me):
SELECT PersonName, [1] AS Automobile1, [2] AS Automobile2, [3] A
S
Automobile3
FROM PeoplesCars PIVOT (MakeModel FOR AutomobileNumber IN ([1], [2],
[3])) x
-AlanMy apologies to posting to sqlserver.server -- it's late. I was
thinking I was in sqlserver.programming
-Alan|||Alan
CREATE TABLE Foo
(
PersonName NVARCHAR(30) NOT NULL,
AutomobileNumber INT NOT NULL,
MakeModel NVARCHAR(30) NOT NULL
)
INSERT INTO Foo VALUES( N'Joe',1, 'Peugeot505')
INSERT INTO Foo VALUES( N'Joe',2, 'HondaAccord')
INSERT INTO Foo VALUES( N'Joe',3, 'LincolnMarkIII')
INSERT INTO Foo VALUES( N'Bob',1, 'HondaAccord')
INSERT INTO Foo VALUES( N'Bob',2, 'DMCCoupe')
SELECT PersonName,
MAX(CASE WHEN AutomobileNumber =1 THEN MakeModel END ) Automobile1,
MAX(CASE WHEN AutomobileNumber =2 THEN MakeModel END ) Automobile2,
MAX(CASE WHEN AutomobileNumber =3 THEN MakeModel END) Automobile3
FROM foo
GROUP BY PersonName
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1147588956.476925.259410@.i39g2000cwa.googlegroups.com...
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
>|||Alan Samet wrote:
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
This works:
SELECT PersonName,
[1] AS Automobile1, [2] AS Automobile2, [3] AS Automobile3
FROM PeoplesCars
PIVOT (MIN(MakeModel) FOR AutomobileNumber IN ([1], [2], [3])) x
;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

PIVOT for non-aggregate data?

I've used UNPIVOT for cleaning up data from tables that violate 1NF.
However, the PIVOT operator seems to behave a little differently, and I
guess I may have misunderstood it from using UNPIVOT (I know they're
not exact complements, in spite of their names). Could someone tell me
if I'm right on this? It seems that PIVOT is only useful in aggregates.
In other words, with the following data (schema can be inferred to
point of relevance):
-- Assume that it's known each person will have a maximum of three
cars. This isn't my exact problem, but can illustrate the conceptual
hurdle.
PersonName AutomobileNumber MakeModel
---
Joe 1 Peugeot505
Joe 2 HondaAccord
Joe 3 LincolnMarkIII
Bob 1 HondaAccord
Bob 2 DMCCoupe
and I want this result (in SQL 2000, I would build this w/ temp tables
and/or joins)
PersonName Automobile1 Automobile2 Automobile3
-----
Joe Peugeot505 HondaAccord
LincolnMarkIII
Bob HondaAccord DMCCoupe NULL
I assume PIVOT isn't really supposed to be used like this, is it? It's
only for aggregates. Is that right? It'd be nice if the following
worked (and it may be syntactically incorrect as it sits, so forgive
me):
SELECT PersonName, [1] AS Automobile1, [2] AS Automobile2, [3] AS
Automobile3
FROM PeoplesCars PIVOT (MakeModel FOR AutomobileNumber IN ([1], [2],
[3])) x
-AlanMy apologies to posting to sqlserver.server -- it's late. I was
thinking I was in sqlserver.programming
-Alan|||Alan
CREATE TABLE Foo
(
PersonName NVARCHAR(30) NOT NULL,
AutomobileNumber INT NOT NULL,
MakeModel NVARCHAR(30) NOT NULL
)
INSERT INTO Foo VALUES( N'Joe',1, 'Peugeot505')
INSERT INTO Foo VALUES( N'Joe',2, 'HondaAccord')
INSERT INTO Foo VALUES( N'Joe',3, 'LincolnMarkIII')
INSERT INTO Foo VALUES( N'Bob',1, 'HondaAccord')
INSERT INTO Foo VALUES( N'Bob',2, 'DMCCoupe')
SELECT PersonName,
MAX(CASE WHEN AutomobileNumber =1 THEN MakeModel END ) Automobile1,
MAX(CASE WHEN AutomobileNumber =2 THEN MakeModel END ) Automobile2,
MAX(CASE WHEN AutomobileNumber =3 THEN MakeModel END) Automobile3
FROM foo
GROUP BY PersonName
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1147588956.476925.259410@.i39g2000cwa.googlegroups.com...
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
>|||Alan Samet wrote:
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
This works:
SELECT PersonName,
[1] AS Automobile1, [2] AS Automobile2, [3] AS Automobile3
FROM PeoplesCars
PIVOT (MIN(MakeModel) FOR AutomobileNumber IN ([1], [2], [3])) x ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--