Click here to Skip to main content
15,893,814 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am currently trying to write a sql script that would return a single line of data and not multiple lines.
I am trying to do a count on +- 4 statuses
1.In Process
2.Processing
3.Completed
4.Failed

my table has the following columns

ProcessId
StatusId
Interfaceid
date

the issue i am having is that the data being returned is being returned in multiple rows so eg
lets say i am doing a query on Ineterface 1 the result being returned is

RowNumber interfaceid in process processing completed failed
1 1 5
2 1 9
3 1 250
4 1 50


I want the result to show
RowNumber interfaceid in process processing completed failed
1 1 5 9 250 50

the script i am running is below

What I have tried:

SQL
Select 
interfaceid,
decode(statusid,1,count(*))"in process",
decode(statusid,3,count(*))"processing",
decode(statusid,3,count(*))"completed",
decode(statusid,4,count(*))"Failed"

from table a

group by interfaceid,statusid
Posted
Comments
Richard Deeming 22-Jul-16 9:12am    
Which DBMS, and which version?
IsiR_19 22-Jul-16 9:16am    
oracle 11g
PeejayAdams 22-Jul-16 9:13am    
You need to look at PIVOT (https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx)
[no name] 22-Jul-16 10:13am    
You are grouping by interfaceid _and_ statusid therefore you will have as many lines as different Status are there for one Interface.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900