Something like this?

SELECT

st.name AS '@name',

si.name AS 'index/@name'

FROM sys.indexes si

INNER JOIN sys.tables st ON si.object_id = st.object_id

FOR XML PATH('table')

If I'm not overlooking something, you could simply replace the REPLACE(FOR XML) subquery with the code column itself.

What makes me wondering: how did you come up with the concatenated version...

IIRC, this issue is caused by the Jet 4.0 / EXCEL registry setting for the TypeGuessRows parameter.

See http://support.microsoft.com/kb/189897 for details.

This link[/url] might be helpful, too.

Based on the rather vague description I expect you insert the data row by row.

If that's the case, change your insert concept to use a single insert for all 1399...

Use your query inside a CTE and update the CTE value:

;WITH cte AS

(SELECT

p.ProductId,

scr.SupplierSequence,

DENSE_RANK () OVER (PARTITION BY p.ProductId ORDER BY scr.SupplierSequence ASC) AS...

Something like this?

SELECT

[BOOKING_ID],

[BOOKING_TYPE_CD],

[BOOKING_STATUS_CD] ,

MAX([BK_PARTY]) AS [BK_PARTY],

MAX([FW_PARTY]) AS [FW_PARTY],

MAX([SH_PARTY]) AS [SH_PARTY]

FROM [dbo].[xyz]

GROUP BY [BOOKING_ID],[BOOKING_TYPE_CD],[BOOKING_STATUS_CD]

As a side note: thank you for the ready to use sample...

I would try to rewrite the stored procedure as an inline-table valued function.

This would change the RBAR approach into a set based solution.

We'd need to know more what the sproc...

It looks like a CrossTab query to get data in a pivoted format.

The query itself doesn't look that bad except for the following issues:

The subquery aliased as y selects more...

I second Steve.

In a company I worked for the HR philosophy was "travellers should not be stopped".

If you think making more money at a different company is the best for...

It depends. But...

It took me a while to convert the sample data into a ready to use format...

Based on what I've seen all you'd need to do is change your original query...

Here's a reference to both, syntax and range.

george sibbald (2/6/2011)

---

did the interviewer give a solution?

Beats me where the case statement would come into it but this would do it:

select top 1 yourcolumn from yourtable order by yourcolumn...

In your CTE, add another column that will hold the value of the SEGMENT_SORT column (modified to include leading Zero's) for SEGMENTE_MASTERKEY = 0 values. Place this value column at...

What exactly are you trying to do?

Based on your sample data the segment_key=100 has an assigned value of sort=11.

Why should this sort value be listed before a sort=101?

Based on...