Kit G (2/26/2010)

---

lmu92 (2/26/2010)

---

I'm not sure if it's because of my lacking language skills... but all I understood was CLOUDS ...

Is anybody able to tell me what he was...

Running 100 or up to 200 times faster than Karthik's measurement indicates to me that there's something seriously wrong with his system.

I strongly recommend using profiler to figure out what's...

Glad I could help! 🙂

Please post your query so we can help you to get the syntax right.

Did you already study the two "main articles" on CrossTab and DynamicCrossTab? If not, please see the related links in my signature.

I'm not sure if it's because of my lacking language skills... but all I understood was CLOUDS ...

Is anybody able to tell me what he was talking about?

You could use SET STATISTICS XML ON before your query.

This will result in an XML execution plan.

Then query that xml file, e.g. for OutputList/@Column, Identifier a.s.o.

I'm not sure if there's...

A recursive cte will cause performance issues on larger data sets.

A common way to resolve this issue is the usage of FOR XML PATH.

Something like

SELECT [EnrolleeID],

STUFF(

(SELECT '-' + LanguageName...

Something like the following?

SELECT

U.v.value('txtApplicantName[1]', 'VARCHAR(30)') as a,

W.x.value('txtAcctNo[1]', 'VARCHAR(30)') as b,

W.x.value('OptInFlag[1]', 'VARCHAR(30)') as c

FROM @XML.nodes('xfadata/changeDebitCard') T(c)

CROSS APPLY

c.nodes('applicantInfo') U(v)

CROSS APPLY

c.nodes('posAccts') W(x)

423ms on my home PC...

That's my "wild-guessing-code" that might help you:

SELECT

c.value('TRANS_CODE[1]', 'VARCHAR(30)') AS Batches_col1

FROM @xml.nodes('//HEADER') t(c)

SELECT

c.value('CLIENT_SSN[1]', 'VARCHAR(30)') AS Clients_col1

FROM @xml.nodes('//RECORD') t(c)

SELECT

v.value ('@id[1]','VARCHAR(50)') AS Charges_col1,

y.value('local-name(.)', 'VARCHAR(50)') AS Charges_col2,

y.value('(.)', 'VARCHAR(50)') AS Charges_col3

FROM @xml.nodes('//RECORDS')...

I have to repeat my previous post:

Please provide more details. The best way would be to post ready to use sample data (including table def, sample data and expected result)....

Divya Agrawal (2/26/2010)

---

Thanks Lutz.

Your solution i have already used for less number of columns.

There are lots of columns around 100's in the table which i need to query. Case...

Hard to tell what you're trying to do...

Please provide more details.

Especially the following phrase is unclear (at least to me...): "without using txt file"

Please provide more details. The best waa would be to post ready to use sample data (including table def, sample data and expected result). For details please see the first...