[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-20
2
Medium Priority
?
355 Views
Last Modified: 2012-09-19
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!
0
Comment
Question by:Bianca
2 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38314562
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
 
LVL 12

Expert Comment

by:sachitjain
ID: 38314652
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question