Solved

SQL 2005 PIVOT a varchar field

Posted on 2011-09-12
10
688 Views
Last Modified: 2012-05-12
Hi
I have a field that has a value of varchar that I need to pivot...Any ideas....



0
Comment
Question by:Baxters801
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 12

Expert Comment

by:jagssidurala
ID: 36524853
0
 

Author Comment

by:Baxters801
ID: 36525284
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36525435
Why NOT? You can just do MAX or MIN. Please give examples of your actual data and the desired results.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36526688
>>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
 

Author Comment

by:Baxters801
ID: 36527040
Exactly, you cannot perform an aggregate function on a varchar field while performing a pivot
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36527074
That is a false statement. You just have to pick the right aggregate. Please show data and what you are trying to do.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36527145
>>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
 

Author Comment

by:Baxters801
ID: 36543213
i did not mean all aggregate functions...my mistake
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36543237
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36543443
Even better show us the table(s) schema and your query efforts so far.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question