Pulling multiple values in the same line in SQL

90 pts.
Tags:
SELECT statement
SQL
SQL Query
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Boardpig
    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.
    90 pointsBadges:
    report
  • Boardpig
    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.
    90 pointsBadges:
    report
  • carlosdl
    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).
    69,920 pointsBadges:
    report
  • Boardpig
    Sorry Carlos, Been seconded away for 2 days, I'll give this a try next week. Alex.
    90 pointsBadges:
    report
  • Dschwant
    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.
    175 pointsBadges:
    report
  • Boardpig
    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.
    90 pointsBadges:
    report
  • Boardpig
    "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.
    90 pointsBadges:
    report
  • carlosdl
    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.
    69,920 pointsBadges:
    report
  • Boardpig
    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.
    90 pointsBadges:
    report
  • carlosdl
    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.
    69,920 pointsBadges:
    report
  • Boardpig
    [...] Boardpig [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following