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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 row to column..not the usual requirement

Author  Topic 

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-10-06 : 07:10:27
Hi,
I have the following table:
Col001
------
a,b,c
e,f,g

I want a query that will convert it to:
Col001
------
a
b
c
e
f
g

The no of rows may vary and also the number of elements in it...

how to go about it....?please help

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 07:40:35
Storing "a,b,f" in a single column means that your database is not normalised, it would be better to Normalise it.

You can Split the delimited data into multiple rows.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

but that is more for a single delimited value, not multiple rows of delimited values. There is a thread on SQL Team that handles that, I'll see if I can find it for you

Later: Babli: there is some discussion here using a User Defined Function and CROSS APPLY in SQL 2005

http://www.sommarskog.se/arrays-in-sql-2005.html

Kristen
Go to Top of Page

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-10-06 : 09:09:11
Hi Babli,

start with this

CREATE TABLE #T ( Col VARCHAR(1000))

INSERT INTO #T
SELECT 'a,b,c' UNION ALL
SELECT 'e,f,g'

UPDATE #T SET Col = '<d>' + REPLACE(Col, ',', '</d><d>') + '</d>'

ALTER TABLE #T ALTER COLUMN Col XML

SELECT T2.Split.value('.', 'VARCHAR(100)') AS 'Col'
FROM #T
CROSS APPLY Col.nodes('/d') as T2(Split)

DROP TABLE #T
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-08 : 04:18:32
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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
Go to Top of Page

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)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 all

Kristen
Go to Top of Page
   

- Advertisement -