2

I have SQL query and it works OK.

select monthname (timestamp_iso(STATUSDATE)), 

    count (case when service='ADSL' then 1 end) as  ADSL,
    count (case when service='IPTV' then 1 end) as  IPTV,
    count (case when service='VOIP' then 1 end) as  VOIP
from INCIDENT
group by monthname(timestamp_iso(STATUSDATE))

I am getting for each month number of services in that month. But these services I have over 100. Is it possible to have dynamic CASE WHEN? To get all services and for each service return number of them in that month. Also it should write me after AS name of the service. Another option is to I write those 100 services manually so I was just wondering? Thanks

Veljko
  • 1,708
  • 12
  • 40
  • 80

3 Answers3

3

You can't have a dynamic number of columns in SQL.

You can write code (In SQL or another language) that writes SQL to execute (Dynamic SQL), but that can get messy. WSo, the resulting SQL Query is for a fixed number of columns, but you have code that changes the SQL Query each time you run it.

The standard Relational Database Model that fits your needs, and that doesn't need dynamic recoding, is to normalise your results...

SELECT
  monthname (timestamp_iso(STATUSDATE)),
  service,
  COUNT(*) AS count_service
FROM
  incident
GROUP BY
  monthname(timestamp_iso(STATUSDATE)),
  service

The question then becomes: Do you Really need a pivotted result set when you don't know how many columns you'll get?

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Alternatively, if it's always the same 100, and you just want to save time typing... Copy the 100 values into EXCEL and use that two build the case statements for you. Then copy them back into SQL. – MatBailie May 17 '12 at 11:51
  • OK thanks...I have now over 100 so I will write them it is OK but maybe in future they will add some more services so I would like to SQL code pick it up automatically instead of me adding in code but OK. Can you just clarify me what is the pivotted result set? Because I am not sure if that is what I want. You were right I would like to have somehow dynamic number of columns – Veljko May 17 '12 at 12:13
  • @Dejan - Normalised results are as per my answer. Pivotted results are as per your question. It just means to take rows and make them into columns. The up side is that it is more human friendly. The down side is that it is less SQL friendly. – MatBailie May 17 '12 at 12:17
2

If you want to do this, in a programming language you can select DISTINCT service and build your query

For example in C#

string SQL = "select monthname (timestamp_iso(STATUSDATE))" 

List<string> Columns = new List<string>();
foreach(string service in Services)
{
     Columns.Add( "  count (case when service='"+service+"' then 1 end) as  "+service);
}

SQL +="," + String.Join(",",Columns.ToArray());
SQL += " from INCIDENT group by monthname(timestamp_iso(STATUSDATE))";

db.Query(SQL);

in Javascript:

  var SQL = "select monthname (timestamp_iso(STATUSDATE))"; 

var Columns = new Array();
foreach(string service in Services)
{
     Columns.push( "  count (case when service='"+service+"' then 1 end) as  "+service);
}

SQL +="," + Columns.Join(',');
SQL += " from INCIDENT group by monthname(timestamp_iso(STATUSDATE))";

db.Query(SQL);

other way if you use oracle you can try write PL/SQL to select columns, and build your query dynamicly, then use EXECUTE IMMEDIATE Statement

Tufan Barış Yıldırım
  • 3,952
  • 26
  • 36
1
select 
    service,
    monthname (timestamp_iso(STATUSDATE)), 
    count (*)              
from 
    INCIDENT
where 
    service in ('ADSL','IPTV','VOIP')
group by 
    monthname(timestamp_iso(STATUSDATE)), 
    service

I think this gets you close to what you want.

John Sobolewski
  • 4,512
  • 1
  • 20
  • 26