Most definitely, there is no c.u.r.s.o.r. required.

The solutions depend on your data structure, which is unknown to us (so far). So please help us help you.

If you would provide...

Did you actually read the CrossTab article I pointed you at? It will answer most of your questions ,except for the variable number of columns.

Once you understood the CroosTab concept...

I assume you're familiar with shredding those data into a relational (temp) table using XQuery. If not, please post back and we'll show you an example how to do it.

Once...

Lynn Pettis (5/3/2010)

---

... and that's exactly the case...

Would you mind to share an example to support your argument?

The following code would solve the example you provided, but as Lynn already suggested, posting your code would probably help us help you with the "real issue"...;-)

SELECT 'Header Record'

UNION ALL...

It seems like you add the extra CR-LF within your trailer record statement. Hard to tell without actually seeing the code...

Probably something like

-- some code

+'

< trailer record > '

...

black.clouds.999 (5/3/2010)

---

Thanks Imu !

Here is what I got from BOL :

CONTAINSTABLE ranking uses the following algorithm:

StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )

Rank = min( MaxQueryRank, HitCount...

It's called CroosTab or Pivot.

For a CrossTab solution please see the related link in my signature. It will shortly describe the PIVOT syntax as well.

You actually search for an exact match of either the word "mountain" or "mounting". Therefore, you'll get two different result sets. You might want to search for "mount*".

For details, please...

You're very welcome! 😀

Assume the following scenario:

You have a new room that's available since the hotel decided to refurniture the clothing store.

This room will never be available since it doesn't show up in...

Change the SELECT clause to

SELECT COUNT(DISTINCT t.room_id)

FROM @tbl t

LEFT OUTER JOIN cte_blocked b ON t.room_id=b.room_id

WHERE b.room_id IS NULL

First I would try to find the blocked rooms and second display the remaining:

Side note: please notice how I posted the sample data o make it easier to work on......

What would be your expected result?