InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 17:49:33 GMT20http://www.sqlservercentral.com/Forums/Topic1408240-392-1.aspxThis should also work:[code="sql"]SELECT newColumnFROM yourTable as ytCROSS APPLY ( VALUES (LEFT(yt.yourColumn,1)+'C') ,(LEFT(yt.yourColumn,1)+'D') new (newColumn)[/code]Thu, 17 Jan 2013 17:32:31 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408240-392-1.aspxCool. Thanks. I was trying this by creating temp table and then making CROSS JOIN. But definitely it's better. :-)Thu, 17 Jan 2013 04:51:54 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1408240-392-1.aspx[quote][b]sqlnaive (1/17/2013)[/b][hr]In a table, I have value like this:1A2AI want data like this:1C1D2C2Dthat is for each value of A in table, i want two records with C and D.[/quote][code="sql"]SELECT newColumnFROM yourTable as ytCROSS APPLY ( SELECT LEFT(yt.yourColumn,1)+'C' UNION ALL SELECT LEFT(yt.yourColumn,1)+'D' ) AS new(newColumn)[/code]Without table definitions and sample data, this is just a sample showing how CROSS APPLY can be used to expand 1 row to many...Thu, 17 Jan 2013 03:10:03 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1408240-392-1.aspxIn a table, I have value like this:1A2AI want data like this:1C1D2C2Dthat is for each value of A in table, i want two records with C and D.Thu, 17 Jan 2013 02:30:56 GMTsqlnaive