Thread: Pivot with out Aggregate Function -SQL 2005

Hi, take a look at the example here: Pivot without aggregate function?

They still use an aggregate function, and I don't think there is a way out of that when using a pivot. However, the correct results are returned.

try this:

Code:

With typerank as
(select pid, typeid, date,
	row_number () over (
		partition by pid 
		order by typeid) as rn
from test_1
)
select pid, 
	max(case when rn = 1 then typeid end) as typeid1,
	max(case when rn = 1 then date end) as date1,
	max(case when rn = 2 then typeid end) as typeid2,
	max(case when rn = 2 then date end) as date2,
	Max(case when rn = 3 then typeid end) as typeid3,
	max(case when rn = 3 then date end) as date3
from typerank t
group by pid
order by pid

You can use the PIVOT function to do this, and yes, it DOES require an aggregate function. In you case, try using min(date) and the aggregate -- assuming that PID and TypeID uniquely identify the date you want.

i'm not sure what you mean by "PID's are hard coded". i don't see how this has anything to do with the code i posted.

Well, you have a problem with your syntax. Specificially

Code:

PIVOT ( max(Date) FOR Date in (V1 ,V2 ,V3 ,V4 ,V5 ,V6 ,V7,V8) )AS pvt

I doubt that Date is ever going to take the value V1, V2, V3 etc. Pivot requries two columns, the first, the one that takes the aggregate, that supplies the values and the second, between the for and the in, the will become the columns in the pivot table. Before we get to deep into this, how many distinct values are there of TYPEID?

Also, I why worry about the performance of the aggregate function? PIVOT is a real pig -- I doubt you would even notice the overhead of the aggregate function.

[edit] I just looked back at you OP and realized that you are doing a true PIVOT here -- in a PIVOT, there is column that supplies the names of the new column names and a column the supplies the values to be aggregated. If we were doing a true PIVOT , you would have your dates in columns named 00010, 00011, 00012, etc. [/edit]