Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Crosstab function

Posted on 2009-05-15
Medium Priority
270 Views
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
Question by:Fairfield
• 2
• 2
• 2

LVL 26

Accepted Solution

tigin44 earned 2000 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
``````
0

Author Comment

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

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
``````
0

LVL 57

Expert Comment

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

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

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

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month12 days, 7 hours left to enroll