April 1, 2015 at 3:00 am
Hi All,
Good Moring..
Is any one having idea on how to split a column data into multiple rows, below is the requirement ,please suggest if anyone have idea on this.
Create table #t3 (id int, country (varchar(max))
INSERT #t3 SELECT 1,' AU-Australia
MM-Myanmar
NZ-New Zealand
PG-Papua New Guinea
PH-Philippines'
Output shold be like below
1 ,AU-Australia
1,MM-Myanmar
1,NZ-New Zealand
1,PG-Paua New Guinea
1,PH-Phlippines
Note: we are getting source data from sqlserver tables.
I googled and found below way but did't get the output as required
SELECT A.id, a.country,
Split.a.value('.', 'VARCHAR(500)') AS String
FROM (SELECT id, country ,
CAST ('<M>' + REPLACE(country, ' ', '</M><M>') + '</M>' AS XML) AS String
FROM #t3) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
So,kindly help me on this.
April 1, 2015 at 3:43 am
Create table #t3 (id int, country varchar(max))
INSERT #t3 SELECT 1,' AU-Australia
MM-Myanmar
NZ-New Zealand
PG-Papua New Guinea
PH-Philippines'
SELECT id, Item
FROM #t3 a
CROSS APPLY dbo.DelimitedSplit8K(country, CHAR(10)) b;
GO
DROP TABLE #t3;
DelimitedSplit8K can be found here: Tally OH! An Improved SQL 8K “CSV Splitter” Function [/url]
Or if that's not right and you want one results column, you can easily just concatenate CAST(id AS VARCHAR)+Item.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 1, 2015 at 6:49 am
Dwain posted the exact way I would do it - with Jeff's DelimitedSplit8K.
However, I noticed that you're using a MAX instead of 8000. When you pass the MAX value to the function, it will only handle 8000 characters. If you change the data type of the function parameter to a MAX instead of 8000, performance is going to tank. It'll still function and perform okay, but it won't be the high-performance function we've all come to know and love.
I've been pretty far down this road, trying many different scenarios, and the bottom line is that LOBs (the MAX) simply don't like to be joined to. Even if you declare the parameter as a MAX and pass a Varchar(8000) that's less than 8000 in length, it still treats it as a MAX and performance deteriorates. Plus, you have the added bonus of a cast.
April 4, 2015 at 2:30 am
For completeness, what's missing is the removal of half of the CRLF combination (windows newline)
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#t3') IS NOT NULL DROP TABLE #t3;
Create table #t3 (id int, country varchar(max))
INSERT INTO #t3 (id,country)
SELECT 1,' AU-Australia
MM-Myanmar
NZ-New Zealand
PG-Papua New Guinea
PH-Philippines';
SELECT
a.id AS [id]
,LTRIM(REPLACE(b.Item,CHAR(13),'')) AS [country]
FROM #t3 a
CROSS APPLY dbo.DelimitedSplit8K(country, CHAR(10)) b;
April 6, 2015 at 6:03 am
Thanks to all for your valuable suggestions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply