Replicated a particular string and create new column

Hi Vitaliy,

Here is the final query. I notice it takes more than 20 minutes to display the result which is not quite good. This table INVENTTRANS where it takes time to process which this table is a transaction.

Declare @timezoneOffset int
Set @timezoneOffset=8
SELECT s.PRODID, s.ASUPRODLINEID, s.ITEMID, 
                isNull((
                SELECT CAST(MAX(Case When ITEMID  like 'FRUA%' THEN 'FRUA'
                        When ITEMID like 'FRUB%' THEN 'FRUB'
                        When ITEMID like 'TX3%' THEN 'TX'
                        When ITEMID like 'FRU%' THEN 'FRU'
                        ELSE null END) as Varchar(20)) AS GetType 
                FROM  INVENTTRANS x with (nolock)
                WHERE 
                        x.TRANSTYPE=8 AND x.DATAAREAID='mpa' AND x.TRANSREFID = s.PRODID
            GROUP BY TRANSREFID
        ),'Non-FRU') as REMARKS
FROM PRODTABLE s with (nolock)
  inner join prodpool pp with (nolock) on s.dataareaid = pp.dataareaid and s.prodpoolid = pp.prodpoolid 
  inner join inventdim ivd  with (nolock) on s.dataareaid = ivd.dataareaid and s.inventdimid = ivd.inventdimid  
WHERE  
           s.dataareaid =  
           AND s.PRODSTATUS IN   
           AND s.prodpoolid IN 
           AND pt.asuprodlineid = CASE WHEN  
           AND ivd.inventlocationid IN 
           AND pp.ASUPOOLGROUP = 
           AND DATEADD(HOUR,convert(int,@timezoneOffset), s.ASURAFDATETIME) BETWEEN '2015-03-04 12:00 AM' AND  '2015-03-05 12:00 AM'
ORDER BY s.PRODID