SQL 2005 PIVOT a varchar field

Hi
I have a field that has a value of varchar that I need to pivot...Any ideas....



Baxters801Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>Exactly, you cannot perform an aggregate function on a varchar field while performing a pivot<<
I suspect you mean you cannot perform a SUM or an AVG function on a varchar field (and this has nothing to do with PIVOT) so what is your point?   In other words tell us what output would you expect here:

DECLARE @Animals TABLE (Animal varchar(20))
INSERT  @Animals
        (Animal)
VALUES  ('Horse'),
        ('Donkey')

SELECT  SUM(Animal)
FROM    @Animals

Output
--------
Mule

Open in new window

0
 
Baxters801Author Commented:
Hi
I have already viewed these links along with many more...The issue is that I want to pivot on a varchar field which does not allow for aggregate functions...
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
Why NOT? You can just do MAX or MIN. Please give examples of your actual data and the desired results.
0
 
Anthony PerkinsCommented:
>>The issue is that I want to pivot on a varchar field which does not allow for aggregate functions... <<
You may want to double check that.  Obviously doing SUM or AVG on a character column does not make a lot of sense, but I suspect you know that already.
0
 
Baxters801Author Commented:
Exactly, you cannot perform an aggregate function on a varchar field while performing a pivot
0
 
Kevin CrossChief Technology OfficerCommented:
That is a false statement. You just have to pick the right aggregate. Please show data and what you are trying to do.
0
 
Baxters801Author Commented:
i did not mean all aggregate functions...my mistake
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, so if you understand that some aggregates will apply, please clarify what is the issue. You can PIVOT VARCHAR data just fine; therefore, you may need to show us a sample of your current data accompanied by an example of your desired output. If you have attempted the PIVOT yourself and are receiving a compilation or run-time error, then please post the error message. If you have a logical issue, i.e., it all parses and runs fine but the results do not match what you expect that provide those details.

This will be very helpful in our being able to help you sort this out.

Kevin
0
 
Anthony PerkinsCommented:
Even better show us the table(s) schema and your query efforts so far.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.