It's fairly a simple problem. Can i (we) see the expected result out of the sample data u posted?

Gianluca Sartori (8/26/2011)

---

Another method:

WITH Test(TestField) AS (

SELECT 'aaa'

UNION

...

Lutz, you are right, this sample...

LutzM (8/26/2011)

---

Something like this?

SELECT TOP 1 ID,

Name,

Number,

...

I see this is your first ever post here, Kavitha, so Welcome to SSC!!

Now, we could not gather much information from your question. Can you please brief us about what...

Oh Mr.Lutz :w00t: i posted a code which was fairly similar to yours 🙂 i was tempted to solve...

How about this?

DECLARE @FirstTable TABLE

(

Name VARCHAR(10)

,id INT

,value ...

Something like this?

declare @String VARCHAR(15)= '12345676'

select @String = STUFF(STUFF(@String,4,0,'-') ,9,0,'-')

select @String

You can make use of STUFF !

Is the dashes needed at the 4th and 8th places always ?

I actually quite dint understand your requirement. Can you be more specific? You have been terrific in providing sample table and data, a little more explanation could get me working...

Dynamic query, may be ???

You're welcome.

This is method 2, using Cross Apply and Distinct 🙂

;with cte ( Account ,ICP ,Custom1,Custom2) AS

(

SELECT 'AC_12345','ICP2','Cust1gftr','Cust2abcd'

UNION ALL SELECT 'AC_12345','ICP3','Custppoe','Cust2efgh'

UNION ALL SELECT 'AC_12345','ICP1','Cust1uytr','Cust2abcd'

UNION ALL SELECT 'AC_54321','ICP3','Cust1cvbc','Cust2asas'

UNION ALL...

One method is to use Row_number to get that

;with cte ( Account ,ICP ,Custom1,Custom2) AS

(

SELECT 'AC_12345','ICP2','Cust1gftr','Cust2abcd'

UNION ALL SELECT 'AC_12345','ICP3','Custppoe','Cust2efgh'

UNION ALL SELECT 'AC_12345','ICP1','Cust1uytr','Cust2abcd'

UNION ALL SELECT 'AC_54321','ICP3','Cust1cvbc','Cust2asas'

UNION ALL SELECT 'AC_54321','ICP5','Cust1fghr','Cust2qwas'

UNION...

OH my bad, i am sorry being a hasty poster. I should have taken time to atleast parse the code, sorry!

Here is the syntax-error-free code; Whether is works for you,...

You dont need a cursor to do this 🙂

some like

DECLARE @MaxIDVal BIGINT

SELECT @MaxIDVal = TOP 1(AcctID) FROM dbo.tableA ORDER BY AcctID DESC

; WITH CTE AS

(

SELECT RN = ROW_NUMBER()...