Just a generic observation...

If this function is taking two hours to run--even with all of the cursors and subcursors--then you are probably accessing a LOT of data. I don't think...

The first problem was that your column names in #loanheader didn't match the column names in the query. Search and replace fixed that and I was able to get valid...

pjrpjr7 (2/4/2013)

---

@steve-2 - thanks for replying. But your query gives me a different result. Your query gives me the number of TIMES custA's status was changed (Ex: custA status1, numdays...

Considering something like Contact Info I'd do something like below. I'm not including every possible attribute, just trying to give some ideas. I can't tell you how many wasted and...

pjrpjr7 (2/3/2013)

---

I am working on sql 2005. I have a table wherein it maintains the history of status

Customer Status...

Hey, Jeff I only read the first and last pages so I hope this hasn't been mentioned already. My company is a major developer for users of the DotNetNuke content...

UniData stores a date as a single integer with 0 representing December 31, 1967,

and 1 representing January 1, 1968, and so on. Negative numbers represent dates

before December 31,...

It took a little effort, but here's a replace function that will do the job and more. Won't swear it can't be improved upon or what kind of performance curve...

You can also try shredding the entire XML into a table showing its parts. Then you have a better idea of the node structure or you can even build queries...

OK, here's another option. This procedure is a LOT more complicated than using the OPENXML method but it parses any XML document into an actual table of nodes, nodetypes, nodenames,...

vinu512 (1/28/2013)

---

All the above methods would get you the required data. This is just an extension to them.

Incase you have more than three attribute Types which may change, then you...

OK, OK, I surrender. But for creating sample data I see no reason not to use a loop--it's quick even if dirty. I was playing around and spent enough time...

--create a table for some sample data

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT NOT NULL,

[Col1] INT NULL,

...

OK, just having fun. I create random start and end dates over a maximum 10 year interval for as many rows as you want to test. The main query is...

Since the OP asked for a PIVOT example here's the code. I created some indexes to try and make sure it was a level playing field. If you run either...