Solved

SQL 2005 PIVOT a varchar field

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

777 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