Hi Jim

I don't think the problem is with an extra space but rather with carriage returns and line feeds in the original string. If you clean these out first then...

Here we can rotate the data by first unpivoting in cte2 and then using pivot in the final select

;WITH cte AS

(

SELECT Id, Username, Role, Status, Expiration

FROM Roles

CROSS APPLY

(SELECT Username FROM...

try this

;WITH cte AS

(

SELECT DISTINCT ID

FROM @Sample

)

SELECT cte.ID, Z.Descriptions

FROM cte

CROSS APPLY

(

SELECT (STUFF((SELECT CHAR(13) + Description

FROM @Sample AS Sample

...

Try this format file

7.0

13

1SQLCHAR00"\""0Quote

2SQLCHAR00"\",\""1Client

3SQLCHAR00"\",\""2Site_Ref_No

4SQLCHAR00"\",\""3Site

5SQLCHAR00"\",\""4Address_1

6SQLCHAR00"\",\""5Address_2

7SQLCHAR00"\",\""6Address_3

8SQLCHAR00"\",\""7City

9SQLCHAR00"\",\""8State

10SQLCHAR00"\",\""9Zip_Code

11SQLCHAR00"\",\""10Country

12SQLCHAR00"\","11Account

13SQLCHAR00"\r\n"12Account_id

In the absence of anything in particular to sort on, this may be what you're looking for

;WITH cte1 AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowAsc,

*

FROM YourTable

)

,

cte2 AS

(

--last 10...

Another one to try:-)

;WITH cte AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY oid, Test, TestColumn, TestString ORDER BY IFFRN) AS RowAsc,

...

mishaluba (6/27/2010)

---

Hello,

I have a table with three columns containing CSV values. I need to normalize them. The order of the CSV values is meaningful (explained more below) ...

Aha the 1,333 test:-) Curiously enough I had already carried out the analysis according to your CsvTest but on 10,000 Row X 1,333 Element table. Results available on request

Thanks for the feedback folks. Much appreciated:-)

Try using a format file. This will help you get started

SELECT * FROM OPENROWSET (BULK 'C:\TxtFile1.txt', FORMATFILE = 'C:\TxtFile1.fmt') AS Z

TxtFile1.fmt

7.0

9

1 SQLCHAR 0 ...

Jeff Moden (9/30/2010)

---

Jeff Moden (9/30/2010)

---

Heh... I've gotten way behind on things and I'm really tired this last week. Too many fires...

Have tested CROSS...

A CROSS APPLY variation

;WITH cte AS

(

SELECT DISTINCT ID

FROM @test-2

)

SELECT cte.ID, Z.RESULT

FROM cte

CROSS APPLY

(

SELECT

STUFF((SELECT SPACE(1) + NAM

...

I think there may be a slight problem Lowell with your SUBSTRING not being long enough for the last one.

Another variation

SELECT LEFT(Address, LEN(Address) - CHARINDEX(',', REVERSE(RTRIM(Address))))

FROM

(

VALUES

('John,Doe,120 jefferson...

OK then, another idea perhaps. How about this monster CROSS APPLY version based on some cool CROSS APPLY tricks 🙂 from Brad Schulz

http://bradsruminations.blogspot.com/search/label/CROSS%20APPLY?updated-max=2009-07-11T12%3A01%3A00-07%3A00&max-results=20

IF NOT OBJECT_ID('tempdb.dbo.#stage', 'U') IS NULL DROP TABLE...