Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Crosstab function

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
Fairfield
Asked:
Fairfield
  • 2
  • 2
  • 2
1 Solution
 
tigin44Commented:
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
 
FairfieldAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You can use this as part of your SELECT statement itself. No Need for any functions to handle this
0
 
tigin44Commented:
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
 
FairfieldAuthor Commented:
rrjegan17:
I am receiving and error when using your solution



Msg 207, Level 16, State 1, Line 1
Invalid column name 'Level1'.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now