InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 10:06:29 GMT20http://www.sqlservercentral.com/Forums/Topic1361783-392-1.aspxHere's another method that uses a correlated subquery:[code="sql"]SELECT ITEM ,ITEM_DESC=( SELECT DESCRIPTION FROM @ITEM b WHERE a.ITEM = b.ITEM AND CNT='') ,CNT ,DESCRIPTIONFROM @ITEM a[/code]The INNER JOIN method is probably faster but it also may depend on indexing and primary key field(s).Tue, 25 Sep 2012 21:32:21 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1361783-392-1.aspx[code="sql"]SELECT a.ITEM,b.DESCRIPTION AS ITEM_DESC,a.CNT,a.DESCRIPTIONFROM @ITEM aINNER JOIN @ITEM b ON b.CNT='' AND b.ITEM=a.ITEM[/code]Thu, 20 Sep 2012 02:19:54 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1361783-392-1.aspxHi SQL Gurus,I have this default master data table below:[code="plain"]ITEM | CNT | DESCRIPTION ----------------------------------------HMMEDA | | HYH Med Oil HMMEDA | G01 | HYH Med Oil No 1 (56ml) HMMEDA | G02 | HYH Med Oil No 2 (96ml) HMMEDA | G03 | HYH Med Oil No 3 (136ml) [/code]How can I transform into this view below by adding an item description column to represent the item code?[code="plain"]ITEM | ITEM_DESC | CNT | DESCRIPTION ----------------------------------------HMMEDA | HYH Med Oil | | HYH Med Oil HMMEDA | HYH Med Oil | G01 | HYH Med Oil No 1 (56ml) HMMEDA | HYH Med Oil | G02 | HYH Med Oil No 2 (96ml) HMMEDA | HYH Med Oil | G03 | HYH Med Oil No 3 (136ml) [/code]p/s: In my real database, I will have many types of item code in the ITEM column.Below is the DDL and sample data:[code="sql"]DECLARE @ITEM TABLE(ITEM CHAR(10) NOT NULL,CNT CHAR(3) NOT NULL,DESCRIPTION CHAR(50) NOT NULL)INSERT @ITEMVALUES ('HMMEDA', '', 'HYH Med Oil'),('HMMEDA', 'G01', 'HYH Med Oil No 1 (56ml)'),('HMMEDA', 'G02', 'HYH Med Oil No 2 (96ml)'),('HMMEDA', 'G03', 'HYH Med Oil No 3 (136ml)')[/code]Thanks :)Thu, 20 Sep 2012 02:15:08 GMTyingchai