CODEWITHSUNDEEP
sqlsql-server-2005
chaitanya
chaitanya

Reputation: 29

sql- display the table of records in the following format

Question Part 1

I am having a Roles table which specifies the level and status of each candidate.

name     id     status      level       location     country
==========================================================
RAJ      1      PENDING      MAJOR      BANGALORE     INDIA   
SAM      2      ACTIVE       LEAD       KOLKATA       INDIA
ANN      3      CLOSED       SENIOR     HYDERABAD     INDIA
BEN      4      APPROVED     MINOR      PUNE          INDIA
JACK     5      APPROVED     MINOR      PUNE          INDIA
REC      6      ACTIVE       LEAD       BANGALORE     INDIA
VESPER   7      ACTIVE       LEAD       BANGALORE     INDIA
KISHOR   8      ACTIVE       LEAD       HYDERABAD     INDIA

I need to generate a report for count of candidtes in each level and how many are there in particlular status based on the selected location and country. I may not be able to explain clearly. but i hope the below picture will help you to assist me.

status     MINOR   MAJOR   LEAD   SENIOR   Total 
==========================================================
PENDING    0       1       0      0        1
ACTIVE     0       0       4      0        4
CLOSED     0       0       0      1        1  
APPROVED   2       0       0      0        2

Question Part 2

How can I rename the values in status column that are displayed after executing above query. Suppose I want to display status Pending as 'Delayed response', Active as 'Online'.

Upvotes: 3

Views: 116

Answers (4)

chaitanya
chaitanya

Reputation: 29

regarding Question Part 2:

WITH PivotSource AS ( SELECT id,status,LEVEL FROM Roles ) SELECT 
Status= case Status when 'Pending' then 'Delayed Response'
        when 'Active' then 'online'  
         else 'unknown' End,
MINOR,MAJOR,LEAD, SENIOR,MINOR+MAJOR+LEAD+SENIOR AS Total
FROM PivotSource
PIVOT (COUNT(id) FOR LEVEL IN (MINOR,MAJOR,LEAD,SENIOR) ) AS Pvt;

Upvotes: 0

Andriy M
Andriy M

Reputation: 77707

A PIVOT-less solution:

SELECT
  status,
  MINOR  = COUNT(CASE level WHEN 'MINOR'  THEN 1 END),
  MAJOR  = COUNT(CASE level WHEN 'MAJOR'  THEN 1 END),
  LEAD   = COUNT(CASE level WHEN 'LEAD'   THEN 1 END),
  SENIOR = COUNT(CASE level WHEN 'SENIOR' THEN 1 END),
  Total  = COUNT(*)
FROM Roles
GROUP BY status

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453563

;WITH PivotSource AS
(
SELECT id,status,LEVEL
FROM Roles
)
SELECT status,
       MINOR,
       MAJOR,
       LEAD,
       SENIOR,
       MINOR+MAJOR+LEAD+SENIOR AS Total
FROM PivotSource
PIVOT (COUNT(id) FOR LEVEL IN (MINOR,MAJOR,LEAD,SENIOR) ) AS Pvt;

Upvotes: 1

Adam Dymitruk
Adam Dymitruk

Reputation: 129634

You need to use a pivot table. Here is an example:

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Upvotes: 0

Related Questions