Solved

SQL 2005 PIVOT a varchar field

Posted on 2011-09-12
10
700 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
 

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

719 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