SQL Help required - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Help required

Author

Topic

koushikchandra
Starting Member

24 Posts

Posted - 2008-09-26 : 01:34:53

Hi,

I am very new to sql server. We are using SQL Server-2000 version database.

I have a table whose data like below :

col1 col2 col3
A B src1.tab1.col1
C D src2.tab2.col2,src3.tab3.col3

Can you please help me to achive the output in the following way :

col1 col2 col3_1 col3_2 col3_3
A B src1 tab1 col1
C D src2 tab2 col2
C D src3 tab3 col3

In oracle I can do the in the following way :

WITH cntr AS
(
SELECT LEVEL AS n
FROM dual
CONNECT BY LEVEL <= 10
)
, no_comma AS
(
SELECT col1
, col2
, REGEXP_SUBSTR (col3, '[^,]+', 1, n) AS col3_item
FROM table_x
CROSS JOIN cntr
)
SELECT col1
, col2
, REGEXP_SUBSTR (col3_item, '[^\.]+', 1, 1) AS col3_1
, REGEXP_SUBSTR (col3_item, '[^\.]+', 1, 2) AS col3_2
, REGEXP_SUBSTR (col3_item, '[^\.]+', 1, 3) AS col3_3
FROM no_comma
WHERE col3_item IS NOT NULL
order by col1,col2;

But how could I achive this in SQL Server-2000 Version.

Regards,
Koushik

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:44:39

refer this for some of methods

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:51:29

Also consider normalising your table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources