October 9, 2009 at 11:04 am
I HAVE A COLUMN THAT CONTAINS CSV DATA LIKE THIS:
428.0,414.8,414.00,426.11,426.3,401.9,250.00,496,593.9,V45.81,V15.82,,,,
727.83,275.49,712.36,715.36,733.92,401.9,V43.65,V45.61,V45.77,V16.9,,,,,
I WANT SPLIT THE CSV COLUMN INTO A MAXIMUM OF 20 SEPARATE COLUMNS.
THANK YOU AND PARDON THE OLD MAN'S UPPERCASE :hehe:
October 9, 2009 at 12:07 pm
Take a look at the article on Tally tables in my signature. It will explain how to use this table to split strings of this nature.
October 9, 2009 at 1:13 pm
Thanks for the post. I was made aware of the tally table method yesterday.
However, I would like to keep the split results in the same row in multiple columns. Does anyone know the best way to do that in a single query?
October 10, 2009 at 5:26 am
If you need to keep the values within that row you'd have to do a pivot on the result set you've got when using Jeffs tally Table method.
Basically, there are two options: you can look up PIVOT function in BOL or read another one of Jeffs great articles: Cross Tabs and Pivots[/url]. Btw: It's the next link in Garadins signature after the tally table link... 😉
I assume you'll always have 20 columns... Otherwise you should search for dynamic cross tabs on this site - but the solution you'll find may not use a single query...
So here's an example with a single query:
DECLARE @t table (id int,para VARCHAR(8000))
INSERT INTO @t
SELECT 1,'428.0,414.8,414.00,426.11,426.3,401.9,250.00,496,593.9,V45.81,V15.82,,,,' UNION all
SELECT 2,'727.83,275.49,712.36,715.36,733.92,401.9,V43.65,V45.61,V45.77,V16.9,,,,,'
SELECT
id,
MAX(CASE WHEN row = 1 THEN value ELSE '' END) AS Col1,
MAX(CASE WHEN row = 2 THEN value ELSE '' END) AS Col2,
MAX(CASE WHEN row = 3 THEN value ELSE '' END) AS Col3,
MAX(CASE WHEN row = 4 THEN value ELSE '' END) AS Col4,
MAX(CASE WHEN row = 5 THEN value ELSE '' END) AS Col5,
MAX(CASE WHEN row = 6 THEN value ELSE '' END) AS Col6,
MAX(CASE WHEN row = 7 THEN value ELSE '' END) AS Col7,
MAX(CASE WHEN row = 8 THEN value ELSE '' END) AS Col8,
MAX(CASE WHEN row = 9 THEN value ELSE '' END) AS Col9,
MAX(CASE WHEN row = 10 THEN value ELSE '' END) AS Col10,
MAX(CASE WHEN row = 11 THEN value ELSE '' END) AS Col11,
MAX(CASE WHEN row = 12 THEN value ELSE '' END) AS Col12,
MAX(CASE WHEN row = 13 THEN value ELSE '' END) AS Col13,
MAX(CASE WHEN row = 14 THEN value ELSE '' END) AS Col14
FROM
(SELECT
id,
row_number() OVER (partition BY id order BY N) AS row,
SUBSTRING((',' + para + ','),N+1,CHARINDEX(',',(',' + para + ','),N+1)-N-1) AS value
FROM @t
CROSS apply
dbo.Tally
WHERE N < LEN(para) + 2
AND SUBSTRING((',' + para + ','),N,1) = ','
) r
GROUP BY id
/* result set
idCol1Col2Col3Col4Col5Col6Col7Col8Col9Col10Col11Col12Col13Col14
1428.0414.8414.00426.11426.3401.9250.00496593.9V45.81V15.82
2727.83275.49712.36715.36733.92401.9V43.65V45.61V45.77V16.9*/
October 12, 2009 at 11:50 am
THANKS. I DON'T SUPPOSE THERE IS A WAY TO THIS WITHOUT USING A GROUP BY?
October 12, 2009 at 11:56 am
What would be the reason for not using the group by clause?
Did you try what effect the grouping has on the code below?
October 12, 2009 at 12:08 pm
I PREFER NOT TO GROUP BECAUSE I HAVE SEVERAL OTHER COLUMNS THAT NEED TO BE INCLUDED IN THE OUTPUT.
October 12, 2009 at 12:19 pm
You can simply add those addtl cols to the select and group by list.
You can also use a sub-query, a CTE, an inline table function or a temp table to generate the output as stated in my first post and join it to your original table.
Whatever fits best and will give the best performance in your scenario...
You should compare and test those options and use the one that fits best (e.g. by comparing execution plans and both, I/O and time statistics).
Edit: btw: is there a special reason for yelling at us (using all upper case letters)?
October 12, 2009 at 6:39 pm
Use Lutz's fine example as a derived table and do a join between that and your other column sources. Don't add extra columns to the GROUP BY that are not a part of the Pivot because you'll really slow down the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2009 at 8:40 am
THANKS FOR THE RESPONSE. I WAS THINKING ABOUT THAT NOW THAT I HAVE CONFIRMATION THAT THE EXTERNAL DATA HAS A UNIQUE IDENTIFIER.
October 13, 2009 at 9:11 am
montgomery johnson (10/13/2009)
THANKS FOR THE RESPONSE. I WAS THINKING ABOUT THAT NOW THAT I HAVE CONFIRMATION THAT THE EXTERNAL DATA HAS A UNIQUE IDENTIFIER.
Also, the caps are annoying. I know you're already aware of this, and apologizing and typing in them anyways doesn't really work. Please take half a second to turn off your caps lock before posting in the future.
October 13, 2009 at 9:22 am
[font="Times New Roman"]Don't get old. :hehe:[/font]
November 1, 2011 at 12:11 pm
what will be the code if the no.of columns are unknown like how to write the code dynamically???
November 1, 2011 at 4:24 pm
ramanamreddy (11/1/2011)
what will be the code if the no.of columns are unknown like how to write the code dynamically???
Kind of like the code in the following article...
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply