Pivot table withiught aggregation

Question

Pivot table withiught aggregation

paulneyman

Mr or Mrs. 500

Points: 575
More actions
October 25, 2008 at 3:33 am

#200019

Hello,
I need to pivot table, but pivot operation requires agregation function, in my case I does not need agregation:
There is original table TABLE_A:
ItemID | Rubric | Value
----------------------------------------
1 | 101 | 201
----------------------------------------
1 | 101 | 202
----------------------------------------
1 | 102 | 301
I need:
ItemID | [101] | [102]
----------------------------------------
1 | 201 | 301
----------------------------------------
1 | 202 | 301
If I try:
SELECT
ItemID,
[101],
[102]
FROMTABLE_A
PIVOT ( MAX(Value) FOR Rubric IN ([101],[102])) AS pvt
I'll get:
ItemID | [101] | [102]
----------------------------------------
1 | 202 | 201
If there was no agregation function MAX it would be great
Waiting for answer,
Paul

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You have to aggregate, but what you can do is to add a fake column in which you then ignore.

This more or less gets want you want. I don't understand why you have the value 201 second row, second column in your output as the sample data doesn't show the combo of 102,201 at all

SELECT

ItemID,

[101],

[102]

FROM

(SELECT ItemID, Rubric, Value, Rank() OVER(partition by ItemID, rubric Order By value) As Fake FROM TABLE_A) Sub

PIVOT ( max(Value) FOR Rubric IN ([101],[102])) AS pvt

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 2

Paul,

It would help us give you a better answer quicker if you posted Table A and it's data in a readily "consumable" format. See the link in my signature below for how to do that pretty easily. 🙂

--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)

paulneyman Mr or Mrs. 500 Points: 575 More actions · Answer 3

You have to aggregate, but what you can do is to add a fake column in which you then ignore.
This more or less gets want you want. I don't understand why you have the value 201 second row, second column in your output as the sample data doesn't show the combo of 102,201 at all
SELECT
ItemID,
[101],
[102]
FROM
(SELECT ItemID, Rubric, Value, Rank() OVER(partition by ItemID, rubric Order By value) As Fake FROM TABLE_A) Sub
PIVOT ( max(Value) FOR Rubric IN ([101],[102])) AS pvt

Nice idea, but result is little bit wrong result.

ItemID | [101] | [102]

----------------------------------------

1 | 201 | 301

----------------------------------------

1 | 202 | NULL

Instead NULL there should be 301.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

There is no way you're going to be able to get that result straight from the pivot. There is only one '301' in the table. SQL won't duplicate a value just to avoid a null.

The duplication of values you'll have to do as a separate step, either using a temp table or in your front end.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

paulneyman Mr or Mrs. 500 Points: 575 More actions · Answer 5

Jeff Moden (10/25/2008)
Paul,
It would help us give you a better answer quicker if you posted Table A and it's data in a readily "consumable" format. See the link in my signature below for how to do that pretty easily. 🙂

/* Oreginal table:

*/

DECLARE @TABLE_A TABLE(

ItemID INT,

Rubric INT,

[Value] INT

)

INSERT INTO @TABLE_A

SELECT 1, 101, 201 UNION ALL

SELECT 1, 101, 202 UNION ALL

SELECT 1, 102, 301;

-- That is original table

SELECT * FROM @TABLE_A

/* Result of query must be as follows:

*/

DECLARE @RESULT TABLE(

ItemID INT,

[101] INT,

[102] INT

)

INSERT INTO @RESULT

SELECT 1, 201, 301 UNION ALL

SELECT 1, 202, 301

-- That is is what I need from TABLE_A

SELECT * FROM @RESULT

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Paul,

How many DISTINCT values of Rubric does your base table contain. How dynamic are you expecting these to be? (Are these likely to change / be added to?) Also, please expand your data and result sets to include a second ID so we can see how you want to handle this.

As Gail said, this data is no longer coming straight from your table, and is semi-manufactured. That second 301 is not in your dataset anywhere, and while it might seem intuitive to you that it should be 301 rather than null, pivoting your data makes this semi problematic. You'll have to come up with a whole set of rules on what number should be used there, and either handle that in a second step within your SP (likely a much more complicated second step being that you won't know what the column names are going to be ahead of time) or handle it on your front end.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 7

paulneyman (10/26/2008)
Jeff Moden (10/25/2008)
Paul,
It would help us give you a better answer quicker if you posted Table A and it's data in a readily "consumable" format. See the link in my signature below for how to do that pretty easily. 🙂
/* Oreginal table:
*/
DECLARE @TABLE_A TABLE(
ItemID INT,
Rubric INT,
[Value] INT
)
INSERT INTO @TABLE_A
SELECT 1, 101, 201 UNION ALL
SELECT 1, 101, 202 UNION ALL
SELECT 1, 102, 301;
-- That is original table
SELECT * FROM @TABLE_A
/* Result of query must be as follows:
*/
DECLARE @RESULT TABLE(
ItemID INT,
[101] INT,
[102] INT
)
INSERT INTO @RESULT
SELECT 1, 201, 301 UNION ALL
SELECT 1, 202, 301
-- That is is what I need from TABLE_A
SELECT * FROM @RESULT

Guess I'm being a bit thick... I just don't see the correlation... sorry.

--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)

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 8

Jeff Moden (10/26/2008)
Guess I'm being a bit thick... I just don't see the correlation... sorry.

Glad to know I'm not the only one who's not understanding this post. 🙂

Paul, you need to give us some more information about your problem so that we can help you solve it.

We don't know the logic behind your expected result.