SQL for Pivot Table like output

[siris]

I want to display certain set of records in a pivot table format. For that I'm giving the statement as :

SELECT criticality, subsystem, COUNT(*) AS Tot
FROM tblBSSCSRProblem WHERE csrstatus NOT LIKE 'C%'
GROUP BY subsystem, criticality

which gives the output as

criticality subsystem Tot
Critical BTS 1
Critical Preside 1
Major BSC 2
Major BSS 2
Major BTS 2
Major OMC 1
Major PCUSN 4
Major Preside 1
Major SGSN 3
Minor BSC 1

Can anybody tell me the query so that I can get the output in the following format:
BTS Preside BSC GGSN OMC
Critical 1 1 0 0 0
Major 2 1 2 0 0
Minor 0 0 1 0 0

somebody told me that we can display the data like this through SQL. But I don't know how. Can anybody help.
Thanks in Advance.

 

[rugbyno9]

You have: GROUP BY subsystem, criticality

Try using: GROUP BY criticality, subsystem

John Fitzgerald
jfitzgerald@nextera.com
fitz@localband.net

 

[tlbroadbent]

SQL Server doesn’t provide a cross-tab query. capability. However, if the number of column headings is fixed you can code a cross-tab query using CASE statements.

SELECT
criticality,
max(Case subsystem
When ‘BTS’ Then Tot Else 0 End) As BTS max(Case subsystem When ‘Preside’ Then Tot Else 0 End) As Preside max(Case subsystem When ‘BSC’ Then Tot Else 0 End) As BSC max(Case subsystem When ‘BSS’ Then Tot Else 0 End) As BSS max(Case subsystem When ‘OMC’ Then Tot Else 0 End) As OMC max(Case subsystem When ‘PCUSN’ Then Tot Else 0 End) As PCUSN max(Case subsystem When ‘SGSN’ Then Tot Else 0 End) As SGSN FROM (SELECT criticality, subsystem, COUNT(*) AS Tot FROM tblBSSCSRProblem WHERE csrstatus NOT LIKE ‘C%’ GROUP BY subsystem, criticality)
GROUP BY criticality
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.