How to use pivot table without agreegate functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use pivot table without agreegate functions

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2011-07-11 : 07:57:04

Hi
i want to use pivot columns to extract cross-tab report. to do this, i've use this query :

select * from
(select p.PatientFname + ' ' + p.PatientLname as N'Patient', cf.FieldName, cfd.CustomFieldValue from Patients p
join CustomFieldData cfd on p.PatientCaseID = cfd.PatientCaseID
join CustomFields cf on cfd.FieldID = cf.FieldID) tmp
pivot (CustomFieldValue for FieldName in ([Eye Color], [Weight])) pvt

but i'm facing this error :
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'for'.

is there any way to use this query without agreegate functions ?
thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-11 : 08:22:30

You need an aggregate function such as MAX, MIN etc. for the data column : I don't know of another way - let us see if anyone else has ideas.

SELECT
   *
FROM
   (
       SELECT
          p.PatientFname + ' ' + p.PatientLname AS N'Patient',
          cf.FieldName,
          cfd.CustomFieldValue
       FROM
          Patients p
          JOIN CustomFieldData cfd
             ON  p.PatientCaseID = cfd.PatientCaseID
          JOIN CustomFields cf
             ON  cfd.FieldID = cf.FieldID
   ) tmp
   PIVOT(max(CustomFieldValue) FOR FieldName IN ([Eye Color], [Weight])) pvt

hdv212
Posting Yak Master

140 Posts

Posted - 2011-07-11 : 09:45:04

As i told in my first post, i know this, but i don't need to use agreegate functions. can nybody help me ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-11 : 10:22:55

There is a logical problem that the SQL query would run into if you do not use an aggregate function.

The sample code below demonstrates what I mean. I am trying to pivot on the username.. For user B, there is only one row, but for user A there are two rows. SQL Server wouldn't know what to do with the two rows unless you told it what to do. It can use the max, min, avg, or any other function which would let it "aggregate" those multiple values into a single value.

CREATE TABLE #tmp (username VARCHAR(32), qty INT);

INSERT INTO #tmp 
SELECT 'A',10 UNION ALL
SELECT 'A',11 UNION ALL
SELECT 'B', 5

SELECT 
	*
FROM
	#tmp
PIVOT (SUM(qty) FOR username IN ([a],[b])) P

DROP TABLE #tmp;

In a similar way, if you had multiple "CustomFieldValue" in your table you would need to do something to aggregate it. If you have only one value, use max or min and it wouldn't matter and would make SQL Server happy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources