Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crosstab function

Posted on 2009-05-15
6
Medium Priority
?
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
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

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

660 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