PIVOT without aggregate function?

Question

PIVOT without aggregate function?

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

I can't speak for anyone else but I consider MAX to be a form of "aggregation" even when it applies to character based values.

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

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

Jeff Moden (8/15/2014)
I can't speak for anyone else but I consider MAX to be a form of "aggregation" even when it applies to character based values.

I agree, anything one can supply a value without a group by in as grouped set is an aggregate. To make things even more twisted, in this case the aggregation order is set by the collation. Of course the function domain is sparsely populated missing simple things like first, next or last (by a chosen order directive) leaving one to use the MAX as NULL is funny enough considered the lowest value by SQL Server. The extended OVER clause in 2012 and later still has the implied lowest value for NULLs although it provides the first/next/last functionality.

😎

Xedni SSCertifiable Points: 6951 More actions · Answer 3

Of course max() is aggregation; I don't think that was ever in question. However for the purposes of what's trying to be accomplished here, using max() in the context of a pivot gives you the same results if you avoid using it and utilized something like a cross-tab pivot.

Executive Junior Cowboy Developer, Esq.[/url]

serviceaellis SSCommitted Points: 1865 More actions · Answer 4

Yes, in the sense, MAX is in fact Aggregating. I think it's because of WHAT you can do with it is why it's looked at / considered as "not" really aggregating.

Ok JeeLee, I'll try your suggestion.

Thank you.

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

JeeTee (8/15/2014)
Of course max() is aggregation; I don't think that was ever in question. However for the purposes of what's trying to be accomplished here, using max() in the context of a pivot gives you the same results if you avoid using it and utilized something like a cross-tab pivot.

The reason I brought it up is because the title of this post is "Pivot without aggregate function" and the link the OP provided is titled "Pivoting without aggregation". I just want to make sure that neophytes browsing this thread and the OP understand that MAX is, indeed, an aggregate.

Also, a CROSS-TAB pivot uses aggregations to do its job if you want items to appear on the same line.

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

@serviceaellis,

Why on Earth do you need to do this without aggregates? As JeeTee and some of the others have pointed out, it's one of the more efficient methods in T-SQL whether you use a PIVOT or a CROSS-TAB to do it. If I knew the reason, I might give it a shot.

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

serviceaellis SSCommitted Points: 1865 More actions · Answer 7

I don't. I used the title that the link I found I posted states.

Just went with it.

I know that in Access you can do first or last as Value in Cross-tab and get the coach name. BUT your limited to one value column.

I just want to be able to produce the results sought.

List the coaches, when there is more than one, for each Team (TeamID)

HOW is not that big a deal to me as getting that result.:-P

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 8

serviceaellis (8/15/2014)
I don't. I used the title that the link I found I posted states.
Just went with it.
I know that in Access you can do first or last as Value in Cross-tab and get the coach name. BUT your limited to one value column.
I just want to be able to produce the results sought.
List the coaches, when there is more than one, for each Team (TeamID)
HOW is not that big a deal to me as getting that result.:-P

Ah. Got it. Thanks.

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

serviceaellis SSCommitted Points: 1865 More actions · Answer 9

So close.

Found info and got this far with a stored prodecure

DECLARE @sql nvarchar(max)

SET @sql = N'SELECT TeamID'

SELECT @sql = @sql + ',MAX(CASE WHEN rownum='+CAST(a.rownum AS CHAR(5))+' THEN CoachFN ELSE '''' END) AS [CoachFN'+CAST(a.rownum AS CHAR(5))+']'

FROM dbo.vTeamCoaches a GROUP BY a.rownum

ORDER BY a.rownum

SET @sql = @sql + N'

FROM vTeamCoaches

GROUP BY Teamid

ORDER BY Teamid'

PRINT @sql

EXEC sp_executesql @sql

[/code]

When I run it get the results I need EXCEPT

1) I need to also have the CoachLN and CoachEmail to show as columns just like the CoachFN

2) How do I do the above in a SQL View? I need it to run reports on the results.

I saved the stored procedure but can't seem to call it out? In fact I don't see in under Programmability | Stored Procedures?

See attached Function result, result comparison to SQL View having the data set, result, and result compare -02

How do I create a view calling out the stored procedure?

I want to generate a view with the results the stored procedure is producing.

serviceaellis SSCommitted Points: 1865 More actions · Answer 10

For anyone else needing a solution

Got this to save as a SQL View thanks to Brian

NOTE: the ColName fields must be the same data type.

WITH Base AS

(SELECT RowNum, TeamID, PersonID, CONVERT(varchar(100),CoachFN) AS CoachFN, CONVERT(varchar(100),CoachLN) AS CoachLN, CoachEmail

FROM vTeamCoaches), norm AS

(SELECT TeamID, ColName + CONVERT(varchar, RowNum) AS ColName, ColValue

FROM Base

UNPIVOT (ColValue FOR ColName IN ([CoachFN], [CoachLN],[CoachEmail])) AS pvt)

SELECT *

FROM norm

PIVOT (MIN(ColValue) FOR ColName IN ([CoachFN1], [CoachLN1],[CoachEmail1],

[CoachFN2], [CoachLN2],[CoachEmail2],

[CoachFN3], [CoachLN3],[CoachEmail3],

[CoachFN4], [CoachLN4],[CoachEmail4])) AS pvt