Loop Thru Values and Assign Them To Field 1

[jcmv007]

Using SQL 8.0, Windows XP SP1

I would like to create a VIEW that outputs all the Job Types a Contractor has worked on. The following data is in a table called WorkOrders:

Contractor  JobType  Amount
A           1        20
A           2        30
A           3        50
B           5        150
B           7        150

Desired output

Contractor     JobTypes  TotalAmount
A              1, 2, 3   100
B              5, 7      300

Can this be done and how should I write the query?
Thanks,

 

[iker3000]

How many job types do you have?

 

[cheyney]

ok this isn't a view, and its probably grossly inefficient, but it will get you the result set you want.


CREATE TABLE #temp (Constractor char, JobTypes varchar(8000), TotalAmount int)
DECLARE @Contractor char
DECLARE @JobTypes varchar(8000)
DECLARE @Amount int

DECLARE cur CURSOR FOR
select distinct Contractor
from WorkOrders
OPEN cur
FETCH NEXT FROM cur INTO @Contractor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @JobTypes = null
SET @Amount= 0
SELECT @JobTypes = Coalesce(@JobTypes + ',', '') + JobType, @Amount= @Amount + Amount
FROM WorkOrders
WHERE Contractor = @Contractor

INSERT INTO #temp
VALUES (@Contractor, @JobTypes, @Amount)
FETCH NEXT FROM cur INTO @Contractor
END
CLOSE cur
DEALLOCATE cur
SELECT *
FROM #temp

DROP table #temp


does anyone have a better way?

cheyney

 

[TheBugSlayer]

I suggest that you define a function in which you will a cursor cursor to build a string with all the job types for a contractor, which will be passed as an argument to the function.

Assume your function is called
getJobTypes(@ContractorID int), the query to get the results you want will be like

SELECT Contractor, getJobTypes(Contractor), SUM(Amount) AS TotalAmount FROM WorkOrders GROUP BY Contractor

There is a way it can be done a little easier in Access but I have not been able to translate that to SQL Server yet.

-----------------------------------
Would anybody ever rate me please?

 

[TheBugSlayer]

Cheyney, your solution and mine have a lot in common. Part of your code is what I have in the function. jmcv007 it's up to you now.

 

[cheyney]

TheBugSlayer:

Better solution, i didn't think of that. You don't have a cursor or a temp table, and yours can be compiled into a view. It would be interesting to see how much more efficient it actually is. Probably wouldn't be too different if there aren't many contractors.

Cheyney

 

[jcmv007]

WOW, this can get big quite fast.

iker3000
46 different types and could grow a few more not that much

 

[JamesLean]

Try this out:

/*** create function ***/
CREATE FUNCTION dbo.GetJobs (@c varchar(10))
  RETURNS varchar(200)
AS
BEGIN
  DECLARE @res varchar(200)

  SELECT @res = CASE WHEN @res IS NULL THEN jobtype ELSE @res + ', ' + jobtype END
  FROM workorders
  WHERE contractor = @c

  RETURN @res
END

/*** get results ***/
SELECT contractor, dbo.getjobs(contractor) AS jobtypes, SUM(amount) AS totalamount
FROM workorders
GROUP BY contractor, dbo.getjobs(contractor)
ORDER BY contractor

--James

 

[jcmv007]

The total number of contractor is 64!

cheyney
I tried your solution but I'm getting the following error messaege:

Server: Msg 245, Level 16, State 1, Line 13
Syntax error converting the varchar value '*' to a column of data type int.

This is the data type of the fields
Contractor int
JobType tinyint

Amount Decimal(10,2)



TheBugslayer
Haven't tried yours yet.

 

[JamesLean]

Given those data types, you may have to change my function slightly:

- change the input parameter to int
-change the SELECT line to:

SELECT @res = CASE WHEN @res IS NULL THEN CAST(jobtype AS varchar) ELSE @res + ', ' + CAST(jobtype AS varchar) END

--James

 

[jcmv007]

JamesLean

I've tride your solution INCLUDING THE LATEST CHANGES but I'm getting the following error message:
Server: Msg 208, Level 16, State 1, Procedure GetJobs, Line 13
Invalid object name 'workorders'.

 

[cheyney]

You should be able to debug this yourself, but this will get you started (btw, if the contractor column is of type int, why did you represent them with A's and B's?)


CREATE TABLE #temp (Constractor char, JobTypes varchar(8000), TotalAmount int)
DECLARE @Contractor int
DECLARE @JobTypes varchar(8000)
DECLARE @Amount int

DECLARE cur CURSOR FOR
select distinct Contractor
from WorkOrders
OPEN cur
FETCH NEXT FROM cur INTO @Contractor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @JobTypes = null
SET @Amount= 0
SELECT @JobTypes = Coalesce(@JobTypes + ',', '') + Cast(JobType as varchar), @Amount= @Amount + Amount
FROM WorkOrders
WHERE Contractor = @Contractor

INSERT INTO #temp
VALUES (@Contractor, @JobTypes, @Amount)
FETCH NEXT FROM cur INTO @Contractor
END
CLOSE cur
DEALLOCATE cur
SELECT *
FROM #temp

DROP table #temp


But don't use this...use the method that BugSlayer and JamesLean proposed.

JamesLean: as a shortcut, you may want to use the Coalesce function instead of a case statement in the select in your function, but it really makes no difference. good job guys.

Cheyney

 

[jcmv007]

Ok Guys,

Here is how I finally got it to work. Thank you all very much for your time!

CREATE TABLE #temp (Contractor INT, JobTypes VARCHAR(8000))
DECLARE @Contractor int
DECLARE @JobTypes VARCHAR(8000)

DECLARE cur CURSOR  FOR
select distinct Contractor
from WorkOrders
OPEN cur
FETCH NEXT FROM cur INTO @Contractor 
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @JobTypes = null
    SELECT @JobTypes = Coalesce(@JobTypes + ',', '') + Cast(JobType as varchar)
    FROM WorkOrders
    WHERE Contractor = @Contractor
    ORDER BY JobType
    INSERT INTO #temp
    VALUES (@Contractor, @JobTypes)
    FETCH NEXT FROM cur INTO @Contractor
END
CLOSE cur
DEALLOCATE cur
SELECT *
FROM #temp

DROP table #temp