December 9, 2010 at 1:10 pm
I'm trying to use the query found here http://www.eggheadcafe.com/software/aspnet/31741612/split-column-into-multiple-rows-and-also-keep-other-columns-as-wel.aspx to split delimited data in one column into multiple rows. This works except that there is one more part to this that I need which I can't figure out. How do I add a where clause to this query so that I can specify a specific row to split. I really one want one row split into multiple.
SELECT customerid,
firstname,
lastname,
SUBSTRING(phone_numbers, n, CHARINDEX(' ', phone_numbers + ' ',
n) - n) AS phone,
n + 1 - LEN(REPLACE(LEFT(phone_numbers, n), ' ', '' )) AS
phone_idx
FROM Phones AS P
CROSS JOIN (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Numbers(n)
WHERE SUBSTRING(' ' + phone_numbers, n, 1) = ' '
AND n < LEN(phone_numbers) + 1
ORDER BY customerid, phone_idx
WHERE customerid = 1 -- How can I do this.
Currently when I try to add a where clause it get this error. Incorrect syntax near the keyword 'WHERE'.
Thanks
December 9, 2010 at 2:04 pm
The ORDER BY has to come after the WHERE. Also there is already a where clause so you'll want to just add to that.
SELECT customerid,
firstname,
lastname,
SUBSTRING(phone_numbers, n, CHARINDEX(' ', phone_numbers + ' ',
n) - n) AS phone,
n + 1 - LEN(REPLACE(LEFT(phone_numbers, n), ' ', '' )) AS
phone_idx
FROM Phones AS P
CROSS JOIN (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Numbers(n)
WHERE SUBSTRING(' ' + phone_numbers, n, 1) = ' '
AND n < LEN(phone_numbers) + 1
AND customerid = 1
ORDER BY customerid, phone_idx
December 9, 2010 at 2:58 pm
Ahh, this is really a slap in the forehead kind of question isn't it. 🙂
Thank you!
December 9, 2010 at 3:38 pm
OK, that did solve the problem though I'm now realizing there is more to this that I have to figure out.
Can you please explain how this query works? What's happening that is causing two rows to be generated from the one?
I have two more columns that I need to do the same thing to along with the first column.
I should just explain that I have two columns that have a semi-colon delimited list of names. Let's say for example the first column has two names and the second column has two names. I need to then split these names into two rows so that the first name in the two columns becomes the first row and the second name in the two columns becomes the second row.
I realize this complicates things. I don't know how much harder this is going to be.
December 9, 2010 at 3:47 pm
Aaron Prohaska (12/9/2010)
OK, that did solve the problem though I'm now realizing there is more to this that I have to figure out.Can you please explain how this query works? What's happening that is causing two rows to be generated from the one?
I have two more columns that I need to do the same thing to along with the first column.
I should just explain that I have two columns that have a semi-colon delimited list of names. Let's say for example the first column has two names and the second column has two names. I need to then split these names into two rows so that the first name in the two columns becomes the first row and the second name in the two columns becomes the second row.
I realize this complicates things. I don't know how much harder this is going to be.
It's about to become a LOT more complex. One of the keys to this is the crossjoin it's doing with the result set.
Alright, start simple. What it's doing is crossjoining against Numbers (basically, a Tally table), and only taking the Numbers.N where there's a separator in phone_numbers (in this case, the space).
This result gives you your two rows, because of the join.
Join on 3 columns, each splitting twice, and you're going to get 2^3 rows, or 8 rows.
What you're going to need is a way to identify each row coming out of the split. This is usually done with ROW_NUMBER() OVER (OrderBy <SomeConstantValueForTheData>), and then joining back in with both the ID AND the RowNumber to the different delimited lists.
When you've hit this point, you need to seriously think about a redesign, unless you're trying to extract poorly designed data out of someone else's system so you never have to do this again in your real querying.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply