• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 748
  • Last Modified:

SQL view help to concantonate 2 fields into 1 field to be used in Delphi Grid memo field

I have a table called workorder_instructions that has multiple quantity and part records per workorder. For example, workorder # 111 has 3 records, each with different quantity and part data.  The result I am looking for is to have a view with 1 workorder record and 1 combined ([quantity] + [part]) + ([quantity] + [part]) for however many that have the same matching workorder #.

Thanks!
0
Bianca
Asked:
Bianca
  • 2
3 Solutions
 
Jesus RodriguezIT ManagerCommented:
try this. Is getting the first 100 records

DECLARE TBL CURSOR FOR
      SELECT TOP 100
                 workorder
                 ,quantity
                 ,part
      FROM workorder_instructions
      ORDER BY workorder
     
CREATE TABLE #WKORDER_TEMP(
  ID INT IDENTITY(1,1) NOT NULL,
  WKORDER_NO NVARCHAR(50),
  TOTALDES NVARCHAR(MAX)
  )
 
  DECLARE @WKORDER NVARCHAR(50)
  DECLARE @DESCRIP NVARCHAR(150)
  DECLARE @PRICE NVARCHAR(15)
  DECLARE @CID INTEGER
  SET @CID=0
 
  OPEN TBL
  FETCH FROM TBL INTO @WKORDER,@DESCRIP,@PRICE
  WHILE @@FETCH_STATUS=0
   BEGIN
    SELECT @CID=COUNT(JBNO)
    FROM WKORDER_TEMP
    WHERE JBNO=@WKORDER
   
    IF @CID>0
     BEGIN
       UPDATE #WKORDER_TEMP
       SET TOTALDES=TOTALDES+ ' '+ @DESCRIP + ' - ' + @PRICE +';'
       WHERE WKORDER_NO=@WKORDER
     END
    ELSE
     BEGIN
      INSERT INTO #WKORDER_TEMP(WKORDER_NO,TOTALDES) VALUES (@WKORDER,@DESCRIP + ' - ' + @PRICE +';')
     END
    FETCH NEXT FROM TBL INTO @WKORDER,@DESCRIP,@PRICE
  END
  CLOSE TBL
  DEALLOCATE TBL
 
 
  SELECT * FROM WKORDER_TEMP
 
Include this line to delete the table after the select

--DROP TABLE #WKORDER_TEMP
0
 
vastoCommented:
try this:
SELECT wd.workorder, 
         Stuff((SELECT ', ' + convert(varchar,w.quantity) + ' + ' + w.part  
                    FROM workorder_instructions  w 
                    WHERE w.workorder  = wd.workorder_instructions  For XML PATH ('')),1,1,'')
FROM  (SELECT DISTINCT workorder  FROM workorder_instructions  ) wd

Open in new window

Check and update the names (for the table and columns)
0
 
BiancaAuthor Commented:
Those did not quite seem to get what I was looking for but it pointed me in the right direction, thanks!
0
 
BiancaAuthor Commented:
I was able to find a different solution combining what other Expers were posting.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now