Custom result set layout for end users

Sometimes you asked to view your query result in an away that customers need to provide for example for another system that will handle it, once I asked to provide some result in CSV file that will be displayed in a specific order. They asked to get data like this

1	Data	qty	Data	qty
2	Data	qty	Data	qty
3	Data	Qty	Data	qty

It some thin like sequence number, data , qty, data, qty while the data stored in databased is just like data, qty. I know it is easy to provide sequence number in the result set by using for example ROW_NUMBER() function but how to split data in this order to make it appear like as they requested.

I decided to use CTE and split the data across it and then make a join based on the order of this data.

First I used NTILE(2) function to split the data into two sets in the same result set so the data will be something like this

1	Data	Qty
1	Data	Qty
2	Data	Qty
2	Data	qty

After this I used ROW_NUMBER, CTE and join CTEs based on the ROW_NUMBER() as here or download from

http://gallery.technet.microsoft.com/Custom-result-set-layout-f0660b8b

WITH CTE(ORD, IDS, ID, NM) AS

(SELECT ROW_NUMBER() OVER(ORDER BY IDS) ORD,  IDS, ID, NM

FROM

(SELECT NTILE(2) OVER(ORDER BY ID) IDS, ID, NM  FROM Table_1) A

WHERE A.IDS = 1),

CTE1(ORD, IDS, ID, NM) AS

(SELECT ROW_NUMBER() OVER(ORDER BY IDS) ORD, IDS, ID, NM

FROM

(SELECT NTILE(2) OVER(ORDER BY ID) IDS, ID, NM  FROM Table_1) A

WHERE A.IDS = 1)

SELECT * FROM CTE1 C

JOIN CTE C2 ON C.ORD = C2.ORD