Solved

Crosstab function

Posted on 2009-05-15
6
190 Views
Last Modified: 2012-05-07
I have data in a table like this:

Level1      Level2       Level3       Level4       Error
--------      --------       --------       --------       ------
Red1        Red2          Red3         Red4         E
Red1        Red2          Red3         Red4         E
Red1        Red2          Red3         Red4         E
Red1        Red2          Red3         Red4         A
Red1        Red2          Red3         Red4         A
Red1        Red2          Red3         Blue4        E
Red1        Red2          Blue3        Blue4        W
Red1        Red2          Blue3        Blue4        W
Red1        Red2          Red3         Blue4        A

I need a function to give a cross tab counts result like the following:

Level1      Level2       Level3       Level4       Error_A     Error_E      Error_W
--------      --------       --------       --------       ---------      ---------      ----------
Red1        Red2          Red3         Red4         2                3
Red1        Red2          Red3         Blue4         1                1
Red1        Red2          Blue3        Blue4                                              2
0
Comment
Question by:Fairfield
  • 2
  • 2
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 24396693
try this
select level1, level2, level3, level4,

   sum(case when error = 'A' tthen 1 else 0 end) as error_A,

   sum(case when error = 'E' tthen 1 else 0 end) as error_E,

   sum(case when error = 'W' tthen 1 else 0 end) as error_W

from yourTbale

group by level1, level2, level3, level4

Open in new window

0
 

Author Comment

by:Fairfield
ID: 24396769
Do I use this in a function or as a select statement in a query?  If in a function, can you tell me how to use it?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24396825
Use Pivot Function for better performance
SELECT Level1, Level2, Level3, Level4, [A] AS Error_A,[E] AS Error_E,[W] AS Error_W

FROM (

SELECT Level1, Level2, Level3, Level4, Error

FROM urtable) ps

PIVOT

(count (level1)

FOR Error IN

( [A], [E], [W])

) AS pvt

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24396832
You can use this as part of your SELECT statement itself. No Need for any functions to handle this
0
 
LVL 26

Expert Comment

by:tigin44
ID: 24396834
you can use as you needed.
if you want you may put the select staement into a funtion and call taht function when you need it.
0
 

Author Comment

by:Fairfield
ID: 24396967
rrjegan17:
I am receiving and error when using your solution



Msg 207, Level 16, State 1, Line 1
Invalid column name 'Level1'.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 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

15 Experts available now in Live!

Get 1:1 Help Now