• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 720
  • Last Modified:

SQL 2005 PIVOT a varchar field

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



0
Baxters801
Asked:
Baxters801
  • 3
  • 3
  • 3
  • +1
1 Solution
 
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
 
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
Anthony PerkinsCommented:
>>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:
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now