Pulling multiple values in the same line in SQL
Sorry if the title is a bit vague but its almost what am I trying to solve.
The best way to explain my issue is this.
If I have a table which takes the format..
user_id, error_code. (and other values also).
The problem is that the same user_id is listed multiple times for each error_code the user has recieve ie.
user_1, 680
user_1, 768
user_1, 680
user_1, -103
user_1, 768
user_2, -103
user_2, 0
user_2, 678
user_2, 0
user_3, 680
user_3, 768
user_3 0
user_3, 768
What I am trying to construct is a table which has ONE row per user with the number of times each error_code was experienced ie,
error codes
uname 680 768 -103 0
user_1, 2, 2, 1,1
user_2, 0, 1, 1,2
ie a count value for each time the error_code is encountered.
I've tried a few ways to do this and have had no luck so far. Any advice would be greatly recieved.
Thanks,
Alex.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 27, 2009 1:22 AM
UPDATED:
February 6, 2009 1:50 AM
Answer Wiki:
Doing this requires the use of a function. Something like this.
<pre>CREATE FUNCTION YourFunction (@UserId varchar(10))
RETURNS VARCHAR(4000)
BEGIN
DECLARE @Return VARCHAR(4000)
SET @Return = ''
DECLARE @error_code VARCHAR(10)
DECLARE cur CURSOR FOR SELECT Error_code FROM YourTable WHERE UserId = @UserId
OPEN cur
FETCH NEXT FROM cur INTO @error_code
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Return = @Return + @error_code + ', '
FETCH NEXT FROM cur INTO @error_code
END
CLOSE cur
DEALLOCATE Cur
SET @Return = SUBSTRING(@Return, 0, LEN(@Return)-2)
RETURN @Return
END
GO</pre>
Do keep in mind that this function will not perform very well as it has to run the function for each user in your record set. You would then call the function like this.
<pre>SELECT UserId, dbo.YourFunction(UserId)
FROM YourTable</pre>
--------------------------
If I understand well, what you need is the count of each error, not the error codes.
If you have a fixed number of error codes, you could create the new table from a query like this:
<pre>SELECT user_id,
SUM(
CASE error_code
when 680 then 1
else 0
END) as error_680,
SUM(
CASE error_code
when 768 then 1
else 0
END) as error_768,
SUM(
CASE error_code
when -103 then 1
else 0
END) as error_M103,
SUM(
CASE error_code
when 0 then 1
else 0
END) as error_0
From YourTable
group by user_id;</pre>
If the number of errors is variable, then you do need a function. If this is the case, please let us know (discuss section).
---------------------
Using dynamic SQL you could construct the above query with errors that occured at least once during the report coverage.
Somthing like this:
<pre>CREATE PROCEDURE YourProcedure (@fromDate date, @toDate date)
AS
DECLARE @selectStmt nvarchar(4000),
@params nvarchar(4000),
@error_code varchar(10)
BEGIN
SET @selectStmt = N'SELECT user_id, '
DECLARE cur CURSOR FOR
SELECT DISTINCT error_code
FROM YourTable
WHERE error_date BETWEEN @fromDate AND @toDate
OPEN cur
FETCH NEXT FROM cur INTO @error_code
WHILE @@FETCH_STATUS = 0
BEGIN
SET @selectStmt = @selectStmt +
N' SUM( ' +
N' CASE error_code' +
N' WHEN ' + @error_code + ' then 1 else 0 ' +
N' END) as error_' + REPLACE(@error_code,'-','M') + ', '
FETCH NEXT FROM cur INTO @error_code
END
CLOSE cur
DEALLOCATE Cur
SET @selectStmt = SUBSTRING(@selectStmt, 0, LEN(@selectStmt))
SET @selectStmt = @selectStmt +
N' FROM YourTable' +
N' WHERE error_date between @fromDate AND @toDate' +
N' GROUP BY user_id'
SET @params = N'@fromDate date, ' +
N'@toDate date'
EXEC sp_executesql @selectStmt,@params,@fromDate,@toDate
END
GO</pre>
<pre>exec YourProcedure '01-jan-2009','31-jan-2009';</pre>
Beware that dyamic sql could pose some security issues.
-CarlosDL
---------------------------------------------------------
Assuming you know the list of error numbers ahead of time, and this is what your source looks like:
CREATE TABLE [DBO].[TST_USER_ERRORS]
(USER_ID NVARCHAR(50),
ERROR_CODE INT)
GO
This is the test data you offered:
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_1', 680)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_1', 768)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_1', 680)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_1', -103)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_1', 768)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_2', -103)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_2', 0)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_2', 678)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_2', 0)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_3', 680)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_3', 768)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_3', 0)
INSERT INTO DBO.TST_USER_ERRORS ([USER_ID],[ERROR_CODE]) VALUES ('USER_3', 768)
GO
Then the output is easy:
SELECT USER_ID, ISNULL([-103],0) [-103], ISNULL([0],0) [0], ISNULL([678],0) [678], ISNULL([680],0) [680], ISNULL([768],0) [768]
FROM (SELECT USER_ID, ERROR_CODE, COUNT(*) CNT
FROM DBO.TST_USER_ERRORS
GROUP BY USER_ID, ERROR_CODE) USER_ERRORS
PIVOT (SUM(CNT) FOR ERROR_CODE IN ([-103], [0], [678], [680], [768])) AS PIV
-->dschwant<--
-------------------------
With PL/SQL you could use a procedure like the following (You might want to find the way to simplify the code a little... :-) ):
<pre>CREATE OR REPLACE PROCEDURE yourProcedure (fromDate in date, toDate in date) IS
type errors_list is table of number index by binary_integer;
cursor yourCursor1 is
select distinct error_code
from yourTable
where error_date between fromDate and toDate;
cursor yourCursor2 is
select user_id,error_code,count(*) errorCount
from yourTable
where error_date between fromDate and toDate
group by user_id,error_code
order by user_id,error_code;
errors errors_list;
previous_user yourtable.user_id%type := null;
tableIndex number := 1;
outputLine varchar2(2000) := 'USER,';
BEGIN
-- populate the possible errors table
open yourCursor1;
fetch yourcursor1 bulk collect into errors;
close yourCursor1;
-- print the headers
for k in errors.first .. errors.last loop
outputLine := outputLine || errors(k)||',';
end loop;
dbms_output.put_line(outputLine);
outputLine := null;
for i in yourCursor2 loop
-- This previous_user is just to avoid the need to use a nested loop, which would make the code simpler, but less efficient
if nvl(previous_user,'*') != i.user_id then
-- to put zeroes when the current user does not have all the possible errors
while tableIndex <= errors.Last and nvl(previous_user,'*') != '*' loop
outputLine := outputLine || '0,';
tableIndex := tableIndex + 1;
end loop;
dbms_output.put_line(outputLine);
outputLine := i.user_id||',';
tableIndex := 1;
-- to put zeroes when the current user does not have all the possible errors
while i.error_code > errors(tableIndex) loop
outputLine := outputLine || '0,';
tableIndex := tableIndex + 1;
end loop;
if i.error_code = errors(tableIndex) then
outputLine := outputLine || i.errorCount || ',';
tableIndex := tableIndex + 1;
end if;
else
-- to put zeroes when the current user does not have all the possible errors
while i.error_code > errors(tableIndex) loop
outputLine := outputLine || '0,';
tableIndex := tableIndex + 1;
end loop;
if i.error_code = errors(tableIndex) then
outputLine := outputLine || i.errorCount || ',';
tableIndex := tableIndex + 1;
end if;
end if;
previous_user := i.user_id;
end loop;
dbms_output.put_line(outputLine);
END;</pre>
You could spool the results to a CSV file.
<pre>set serveroutput on;
spool <the destination csv file name and path>;
execute yourProcedure;
spool off;</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Hi Carlos,
The error_codes are known and this is eactly what I was after. Both inputs helped me to understand what I could do, and from this I can move forward.
Thank you both very much!
Alex.
I have this working now and your help is greatly appreciated.
I wonder though about your statement regarding the number of error codes experienced. The error_code itself comes from a known quantity set of codes but as I see now, not EVERY code will be experienced during the report coverage period and this results in a report which has many “zero” values.
I wonder if it is possible to write a table collecting the relevant error codes for that period, then build the case statement from the codes collected?
This way, the final report would only contain the error codes experienced, rather than all the codes available.
thanks in advance for any further thoughts!
-Alex.
I can only think of a stored procedure with dynamic sql. Will that work for you ?
I’ve put an example procedure on the ‘answer’ section. Have a look at it. (I did not put it here, because the “code” tool doesn’t work here, and it makes the code easier to understand).
Sorry Carlos,
Been seconded away for 2 days, I’ll give this a try next week.
Alex.
If your are on 2005+, you can also do this using a CTE that does a regressive expansion. I can throw an example together if you want.
Hello,
I eventually got a chance to try the case select method and it turns out there are simply too many error codes for it to cope with (as I had to include all possibles) . I recieve the error ORA-01467: sort key too long.
Unfortunately it looks like I will have to determine the codes flung for the months data then find a way to run the query on that.
What I did do was pull each user id and error code (and count) and group them together ie
user1, 680, 14
user1 -103, 23
user1 700, 10
user2, 680, 12
user2, 719, 13
This enabled me somewhat to get some useful data in Excel. Not idea, though and not very “automated”
Alex.
“If your are on 2005+, you can also do this using a CTE that does a regressive expansion. I can throw an example together if you want.”
This would be great, but again I would be unable to predict the error_codes experienced for each month, but could pull them and create a new table from that.
Alex.
I thought you were using Sql Server. Now I realize you are on Oracle (and I know it better than sql server).
I still think there must be a way to do what you want, but I do not know exactly what it is, what you are going to do with the resulting information, if you are going to call this query from an application, etc…
If you need further help, I will be glad to try to help you. If so, please provide your Oracle version.
Hi Carlos,
You’d think I would have known what I was talking about..(sorry).,
Ok we are using Oracle 10g Enterprise edition.
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
I’m after the resulting info to be used as a spreadsheet simply showing the output of the query (*which the case statement does fine when it’s not too long).
I was willing to live with all the error_codes being shown in the report (whether they were experienced or not) and have excel remove the “zero” columns, thus the report would only show the relevant errors. If it was a table also, all the better, then we could bounce more query’s off it at a later date.
As its stands there are simply too many error codes to deal with, and this then makes the case statement too long.
So, to sum up I guess.
The origin data takes the format I described above…(except, quite a number of users)
user_1, 680
user_1, 768
user_1, 680
user_1, -103
user_1, 768
user_2, -103
user_2, 0
user_2, 678
user_2, 0
user_3, 680
user_3, 768
user_3 0
user_3, 768
and I want to present this as a csv etc as
User Id Error Codes
680 768 -103 0 etc etc
user1 2 2 1 0
user2 0 0 1 1
user3 1 2 0 1
Hope this helps to clarify.
Alex.
We did something similar once.
I have put a stored procedure (in the ‘answer’ section) that I think could do what you want.
Please let me know the results.