SQL View that concatenates 2 fields for every record and combines those results with all other concantenated records to view in a different field/table

I have a table called 'Workorder' with a field called 'Description'. Each workorder record has 1 description field.

I have a table called 'Workorder_Instructions' that has a 'qty' and 'parts' field. There can be multiple 'instruction' records per workorder.

What I would like is for the 'Description' field in the 'Workorder' table to be a result of the qty and parts fields concantenated into 1 string PER instruction record.

For example: Workorder # 999 has 2 instruction records, 1 has qty 4 doors and the other has qty 2 drawers. I want the description field to show '4 doors, 2 drawers'. I would like it to be active so as users enter in more parts to the instruction table, the description field in the workorder is updated as well.

Thanks for your help!
Norm-alNetwork EngineerAsked:
Who is Participating?
 
lwadwellCommented:
Are you wanting to create a view? - views are stored SQL statement, so when the value changing in an underlying table, the value selected in the view changes.

Or do you want to update the value in the physical 'Workorder' table and have it kept updated - to maintain the value requires code to achieve (a trigger or procedure or something).


For a view refer to the documentation http://msdn.microsoft.com/en-us/library/ms187956(v=sql.90).aspx for full details but basically it would be (and you cannot reuse the Workorder name)
    CREATE VIEW view_Workorder AS
    SELECT ...
and finish the SELECT statement with the columns and criteria that you want.

This SQL shows how columns can be concatenated into delimited strings:
;with workorders as (
    select 777 as workorder_no, 'nursery rhyme' as description union all
    select 999 as workorder_no, 'def' as description
), Workorder_Instructions as (
    select 777 as workorder_no, 3 as qty, 'little pigs' as parts union all
    select 777 as workorder_no, 1 as qty, 'big bad wolf' as parts union all
    select 777 as workorder_no, 1 as qty, 'straw house' as parts union all
    select 777 as workorder_no, 1 as qty, 'stick house' as parts union all
    select 777 as workorder_no, 1 as qty, 'double brick house' as parts union all
    select 999 as workorder_no, 4 as qty, 'doors' as parts union all
    select 999 as workorder_no, 2 as qty, 'drawers' as parts
)
select wo.workorder_no, wo.description as old_description, 
       stuff((SELECT ', ' + convert(varchar,qty) + ' ' + parts
              FROM Workorder_Instructions woi 
              WHERE wo.workorder_no = woi.workorder_no 
              FOR XML PATH ('')), 1, 2, '') as new_description
from workorders wo

Open in new window

Note: I used the cte's to generate data for the main SELECT to work - you would use your own tables instead and fix the column names to match your tables too.


For maintaining the table ... that is much more complex.  Please confirm your direction first please.
0
 
sachitjainCommented:
You might not do it with single query but definitely do it through following script

declare @i int, @WorkOrderId int, @cnt int, @WODesc varchar(255)
declare @WO table (Id int identity(1,1), WorkOrderId int)
insert into @WO (WorkOrderId)
select distinct WorkOrderId from WorkOrder

set @i = 1
select @cnt = COUNT(*) from @WO
while @i <= @cnt
begin
      select @WorkOrderId = WorkOrderId from @WO where Id = @i
      set @WODesc = ''
      select @WODesc = (case when @WODesc <> '' then @WODesc + ', ' else @WODesc end) + CAST(Qty as varchar(4)) + ' ' + parts
      from Workorder_Instructions where WorkOrderId = @WorkOrderId
      
      update WorkOrder set [Description] = @WODesc where WorkOrderId = @WorkOrderId
      set @i = @i + 1
end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.