sam-1083699 (10/12/2009)

My role is to query the data according to the client requirement and convert the data in to xml file and save the xml file in some folder......

Now the OP know's that there are (at least) two people with...

No problem. Glad I could help.:-D

So, you're all set then? (data for September will be returned with the code in my previous post)

Edit:...

Date format:

it depends on the @@language setting during the session you're running the query in.

By default the Server and each new login is set to US English, so it...

change "& lt;" to "<" (the display of SQL code...

Something along this line?

WHERE ld.delq_dt < dateadd(mm, datediff(mm, 0, '2009-11-10'), 0)

The dateadd/datediff will return the first day of the month.

Edit: code formatting changed to "xml" due to wrong display

To post sample data please follow the first link in my signature.

Another option would be to check BOL for UNPIVOT function ...

I'd like to be able to bookmark specific posts/threads (not just articles).

Another subject would be description handling for a new post:

It kinda "disappear" once the thread is started. Suggestion: Either...

Glad we could help! 🙂

Hi Gus,

to me it looks like a "standard row to col problem"...

;with

cte AS (

SELECT

row_number () OVER (partition BY loanref ORDER BY rowid) AS row,

rowid,

loanref, notice1

FROM #test

)

SELECT loanref,

MAX(CASE...

The link Dave pointed you at is a broad overview on how to split a string.

It actually covers most if not any basic techniques.

The first link in the "Links for"...

Oh, I'm sorry!

I didn't realize that Notice1, Notice2,and Notice3 are identical in your sample data... My fault. You most probably have valid reasons for adding those columns in your sample...

You can simply add those addtl cols to the select and group by list.

You can also use a sub-query, a CTE, an inline table function or a temp table to...

What would be the reason for not using the group by clause?

Did you try what effect the grouping has on the code below?

Unfortunately, you didn't describe the rules for building the groups. 🙁

First I thought you're looking for min(RowId) per Loanref, but that's obviously not true, because rowid 11 and 12...