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
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!
Question by:Bianca
    LVL 25

    Accepted Solution

    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 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.
    LVL 12

    Expert Comment

    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
          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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now