Solved

SQL 2005 PIVOT a varchar field

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

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now