Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Babli
Yak Posting Veteran
53 Posts |
Posted - 2007-10-06 : 07:10:27
|
Hi, I have the following table:Col001------a,b,ce,f,gI want a query that will convert it to:Col001------abcefgThe no of rows may vary and also the number of elements in it...how to go about it....?please help |
|
Kristen
Test
22859 Posts |
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-10-06 : 09:09:11
|
Hi Babli,start with thisCREATE TABLE #T ( Col VARCHAR(1000))INSERT INTO #TSELECT 'a,b,c' UNION ALLSELECT 'e,f,g'UPDATE #T SET Col = '<d>' + REPLACE(Col, ',', '</d><d>') + '</d>'ALTER TABLE #T ALTER COLUMN Col XMLSELECT T2.Split.value('.', 'VARCHAR(100)') AS 'Col'FROM #TCROSS APPLY Col.nodes('/d') as T2(Split) DROP TABLE #T |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-06 : 13:03:51
|
You could just use CROSS APPLY with a UDF - avoiding the XML-conversion step, which is a bit one-way!Kristen |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-08 : 04:18:32
|
http://www.datamodel.org/NormalizationRules.htmlMadhivananFailing to plan is Planning to fail |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-10-08 : 23:14:11
|
Heh... how odd... everone's getting on Babli for not having normalized data... ever consider that might be why the question was asked to begin with? To split the data so it could be normalized?--Jeff Moden |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-09 : 03:34:19
|
quote: Originally posted by Jeff Moden Heh... how odd... everone's getting on Babli for not having normalized data... ever consider that might be why the question was asked to begin with? To split the data so it could be normalized?--Jeff Moden
It is becuase some people want to save formatted data in the table (No problem if the data come from different system with comma delimited and we can split it) MadhivananFailing to plan is Planning to fail |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 04:14:03
|
"everone's getting on Babli for not having normalized data"I pointed it out and then ..."To split the data so it could be normalized?"... explained how to Split it.madhi then posted a useful link about Normalisation, which is an extremely good read, even for folk like me who think they know that sort of stuff!PeterNeo posted a solution.I don't think "everone's getting on Babli for not having normalized data" at allKristen |
|
|
|
|
|
|
|