Need to PIVOT without aggregate function
-
I want to PIVOT tables where it is not necessary to sum or count or otherwise aggregate data. Every example of using PIVOT that I can find includes pivoting on an aggregate function, e.g. PIVOT(SUM(somevalue)...is there a way to use PIVOT without an aggregate function? I have not been able to get it to work.
-
Can you post a sample of what you are trieng to do ? DDL/Data/Result required
Kindest Regards,Vasc
-
To my knowledge pivet needs a aggregate function. Without aggregate function logically it is not needed. can you explain specfic need.
Also it will be more rows something like this.
SET NOCOUNT ON
DECLARE @Tbl TABLE
(
MyID INT,
MyVal VARCHAR(10)
)
INSERT @Tbl
SELECT '1', 'AA' UNION
SELECT '2', 'BB' UNION
SELECT '3', 'CC' UNION
SELECT '4', 'DD' UNION
SELECT '5', 'EE'
SELECT Col1 = CASE WHEN MyVal = 'AA' THEN MyID ELSE NULL END,
Col2 = CASE WHEN MyVal = 'BB' THEN MyID ELSE NULL END,
Col3 = CASE WHEN MyVal = 'CC' THEN MyID ELSE NULL END,
Col4 = CASE WHEN MyVal = 'DD' THEN MyID ELSE NULL END,
Col5 = CASE WHEN MyVal = 'EE' THEN MyID ELSE NULL END
FROM
@Tbl
Regards,
gova -
Bruce might mean an ordinary transform of a resultset. Changing rows to columns and columns to rows.
Take a moment or two and read my recent article here
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp
That might give you an idea of how to accomplish your task without aggregation.
N 56°04'39.16"
E 12°55'05.25" -
Thank you Peter, I read the article. The thing is, we have need of dozens of such procs and I would like to keep it simple, hence my desire to use PIVOT.
Here is more information. The first thing you will notice is that tblProperties is all wrong, but we don’t know what kinds of properties will be added in the future, so cannot create columns for them. So, we have a bunch of tables like this one.
tblProperties:
AircraftPropertyNameValue
1 Hours1200
1 Landings900
1 Seats2
1 Engines 2
I can get the result below with the following statement (more or less, I changed things to make the table readable and hope I “translated” my original query property – in any case you get the idea):
SELECT Aircraft, Hours, Landings, Seats, Engines
FROM (SELECT Aircraft, PropertyName, Value FROM tblProperties) As PivotProperties
PIVOT (MAX(Value) FOR Aircraft In (Hours, Landings, Seats, Engines) As pvt
For result:
Aircraft HoursLandingsSeatsEngines
1120090022
Here’s the catch – there cannot be dupes in the table; that is, Aircraft 1 will never have more than one record of "Hours," and therefore there is nothing to aggregate. All I want to do is pivot the data without the “MAX” in there. I saw the following in the SQL Server 2005 Books online, which suggests that it is possible to PIVOT without aggregating, but I’ve not been able to make it work (“Using PIVOT and UNPIVOT”): “When using aggregate functions with PIVOT, the presence of any NULL values in the value column are not considered when computing an aggregation.” This certainly implies that aggregating with PIVOT is optional, but I sure can’t find any examples or figure out the syntax myself.
Suggestions are welcome, thanks.
Bruce
-
You can not use PIVOT for SQL 2005 since you need to hardwire the columns.
Your choice of method is dynamic SQL.
And selecting MAX of 1 record is very fast!
N 56°04'39.16"
E 12°55'05.25" -
Why you are so afraid of aggregates?
They do not harm people. Honestly.
If there is only 1 value MAX will just present that value and dod not do any aggregation. Exactly as you wish.
But what if there is actually more than one value? Did you think about such scenario?
_____________
Code for TallyGenerator -
Using this slighty rewritten SP from my article will do the trick for you. It uses no aggregations!
CREATE PROCEDURE uspAirplaneProperties
AS
SET NOCOUNT ON
CREATE TABLE #Aggregates
(
RowText VARCHAR(50),
ColumnText VARCHAR(50),
CellData INT
)
INSERT INTO #Aggregates
(
RowText,
ColumnText,
CellData
)
SELECT Aircraft,
PropertyName,
Value
FROM tblProperties
CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)
CREATE TABLE #Columns
(
ColumnIndex INT IDENTITY (0, 1),
ColumnText VARCHAR(50)
)
INSERT INTO #Columns
(
ColumnText
)
SELECT DISTINCT ColumnText
FROM #Aggregates (INDEX(IX_Aggregates), NOLOCK)
ORDER BY ColumnText
CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
CREATE TABLE #Rows
(
RowText VARCHAR(50)
)
INSERT INTO #Rows
(
RowText
)
SELECT DISTINCT RowText
FROM #Aggregates (INDEX(IX_Aggregates), NOLOCK)
CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)
DECLARE @ColumnIndex INT,
@MaxColumnIndex INT,
@ColumnText VARCHAR(50),
@SQL VARCHAR(1000)
SELECT @ColumnIndex = 0,
@MaxColumnIndex = MAX(ColumnIndex)
FROM #Columns
WHILE @ColumnIndex <= @MaxColumnIndex
BEGIN
SELECT @ColumnText = ColumnText
FROM #Columns
WHERE ColumnIndex = @ColumnIndex
SELECT @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' INT NULL'
EXEC (@SQL)
SELECT @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.CellData
FROM #Aggregates (INDEX(IX_Aggregates), NOLOCK)
, #Columns (INDEX(IX_Columns), NOLOCK)
WHERE #Rows.RowText = #Aggregates.RowText
AND #Columns.ColumnText = #Aggregates.ColumnText
AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
EXEC (@SQL)
SELECT @ColumnIndex = @ColumnIndex + 1
END
DROP TABLE #Columns
DROP TABLE #Aggregates
SELECT #Rows.*
FROM #Rows
ORDER BY #Rows.RowText
DROP TABLE #Rows
N 56°04'39.16"
E 12°55'05.25" -
There cannot be duplicate values in the table, or at least there shouldn't be. If I have to use MAX so be it, but calculating the aggregate is an operation that must consume some time even if not much, but it adds up, so I'd prefer to eliminate it if possible.
-
Thanks, Peter, I'll give it a try...but still hoping for a solution re: PIVOT :<
. -
And if you have several tables like tblProperties, just add a UNION after the red text in my previous rewritten SP.
SELECT Aircraft,
PropertyName,
Value
FROM tblProperties
UNION
SELECT Aircraft,
PropertyName,
Value
FROM tblPropertiesAnotherTable
N 56°04'39.16"
E 12°55'05.25" -
Yes, I know. We all hoped that the new SQL 2005 PIVOT operator really should be a PIVOT, and not just a rewrite for a lot of case statements...
But who knows? Maybe Microsoft will change this behaviour with next service pack? And let PIVOT operator work like the TRANSFORM operator in Access.
N 56°04'39.16"
E 12°55'05.25" -
Don't forget at the end of the day to compare performance of this solution with performance of simple PIVOT with aggregates.
_____________
Code for TallyGenerator -
FWIW, a number of years ago I picked up a very slim book called Optimizing Transact-SQL: Advanced Programming Techniques. The first chapter deals with single statement SQL solutions to table pivoting and folding problems. Obviously I cannot 'reprint' the text here, and simply describing the code used will not be enough. However, I can attest that the authors' use of characteristic functions in T-SQL yields some elegant yet simple solutions to both pivoting and folding.
Later chapters deal with calculating medians, finding continuous regions in data and computing extreme values.
The ISBN is 0-9649812-0-3. The publisher was SQL Forum Press. The authors are David Rozenshtein, Anatoly Abramovich and Eugene Birger. I do not know if the volume is still in print (try http://www.bookfinder.com) but it is worth searching for.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson -
We will compare performance for the various options. Rozenshtein's book is still available, used and I am purchasing a copy - it sounds like a good book to have, thanks for the info on that.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply