Solved

SQL 2005 PIVOT a varchar field

Posted on 2011-09-12
10
694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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 60

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 60

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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