Viewing 15 posts - 1 through 15 (of 208 total)
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...
March 2, 2011 at 6:08 am
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...
January 14, 2011 at 12:29 pm
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
...
November 10, 2010 at 4:19 am
Try this format file
7.0
13
1 SQLCHAR 0 0 "\"" 0 Quote
2 SQLCHAR 0 0 "\",\"" 1 Client
3 SQLCHAR 0 0 "\",\"" 2 Site_Ref_No
4 SQLCHAR 0 0 "\",\"" 3 Site
5 SQLCHAR 0 0 "\",\"" 4 Address_1
6 SQLCHAR 0 0 "\",\"" 5 Address_2
7 SQLCHAR 0 0 "\",\"" 6 Address_3
8 SQLCHAR 0 0 "\",\"" 7 City
9 SQLCHAR 0 0 "\",\"" 8 State
10 SQLCHAR 0 0 "\",\"" 9 Zip_Code
11 SQLCHAR 0 0 "\",\"" 10 Country
12 SQLCHAR 0 0 "\"," 11 Account
13 SQLCHAR 0 0 "\r\n" 12 Account_id
November 3, 2010 at 2:34 pm
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...
October 17, 2010 at 11:58 am
Another one to try:-)
;WITH cte AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY oid, Test, TestColumn, TestString ORDER BY IFFRN) AS RowAsc,
...
October 10, 2010 at 6:05 am
mishaluba (6/27/2010)
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) ...
October 3, 2010 at 9:13 am
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
October 2, 2010 at 2:29 pm
Thanks for the feedback folks. Much appreciated:-)
October 1, 2010 at 3:57 pm
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 ...
October 1, 2010 at 1:58 pm
Jeff Moden (9/30/2010)
Jeff Moden (9/30/2010)
steve-893342 (9/25/2010)
My conversation with Jeff was getting rather tall!
Heh... I've gotten way behind on things and I'm really tired this last week. Too many fires...
October 1, 2010 at 1:20 pm
Ram:) (9/30/2010)
Thanks Everybody..All these suggestions are good..
But It is taking more time ,suppose if we have around 1,00,000 rows.
Even i am trying to get better solutions for that..
Regards
Ram..
Have tested CROSS...
September 30, 2010 at 12:24 pm
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
...
September 30, 2010 at 1:14 am
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...
September 28, 2010 at 4:31 pm
OK then, another idea perhaps. How about this monster CROSS APPLY version based on some cool CROSS APPLY tricks 🙂 from Brad Schulz
IF NOT OBJECT_ID('tempdb.dbo.#stage', 'U') IS NULL DROP TABLE...
September 28, 2010 at 1:24 pm
Viewing 15 posts - 1 through 15 (of 208 total)