Converting columns to row for Start with connect b

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)

Converting columns to row for Start with connect b

Author

Topic

koushik
Starting Member

11 Posts

Posted - 2010-10-07 : 02:21:53

Hi,

I have a dataset like below (the output below is equivalent to start with connect by) :

----Col1----|-----Col2----
------------|-------------
L3_TAB1_01 | L2_TAB1_03
L3_TAB1_02 | L2_TAB2_02
L3_TAB1_03 | L2_TAB2_03
L3_TAB1_04 | L2_TAB2_01
L2_TAB2_01 | L1_TAB2_01
L1_TAB2_01 | Feed2_01
L2_TAB2_03 | L1_TAB3_03
L1_TAB3_03 | Feed3_03
L2_TAB2_02 | L1_TAB1_02
L2_TAB2_02 | L1_TAB2_02
L2_TAB2_02 | L1_TAB3_02
L1_TAB3_02 | Feed3_02
L1_TAB2_02 | Feed2_02
L1_TAB1_02 | Feed1_02
L2_TAB1_03 | L1_TAB1_01
L2_TAB1_03 | L1_TAB3_01
L1_TAB3_01 | Feed3_01
L1_TAB1_01 | Feed1_01

Now I want the output in the format like :

Final Tbl | lvl1 | lvl2 | lvl3
------------|---------------|---------------|-----------
L3_TAB1_01 | L2_TAB1_03 | L1_TAB1_01 | Feed1_01
L3_TAB1_01 | L2_TAB1_03 | L1_TAB3_01 | Feed3_01
L3_TAB1_02 | L2_TAB2_02 | L1_TAB1_02 | Feed1_02
L3_TAB1_02 | L2_TAB2_02 | L1_TAB2_02 | Feed2_02
L3_TAB1_02 | L2_TAB2_02 | L1_TAB3_02 | Feed3_02
L3_TAB1_03 | L2_TAB2_03 | L1_TAB3_03 | Feed3_03
L3_TAB1_04 | L2_TAB2_01 | L1_TAB2_01 | Feed2_01

Can you please help me to get it in the best possible way.

Actually this is a sample data I have prepared. In actual scenario table names might not start or end with any specific key word like L1 or L3 etc. Also the level here is clearly 3 but in actual scenario I will not be aware of the level at the beginning. It can be anything 7,8,12,90 .. anything.

Regards,
Koushik Chandra

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-10-07 : 02:44:36

use pivot. tons of examples available

koushik
Starting Member

11 Posts

Posted - 2010-10-07 : 02:49:18

It will be very helpful, if you can please provide a query for the output I want.

Regards,
Koushik Chandra

koushik
Starting Member

11 Posts

Posted - 2010-10-07 : 09:11:44

Hi,

I am able to achieve this using SYS_CONNSCT_BY_PATH in Oracle. Please find that query below :

WITH t AS (SELECT 'L3_TAB1_01' AS Col1, 'L2_TAB1_03' AS Col2 FROM DUAL UNION ALL
SELECT 'L3_TAB1_02', 'L2_TAB2_02' FROM DUAL UNION ALL
SELECT 'L3_TAB1_03', 'L2_TAB2_03' FROM DUAL UNION ALL
SELECT 'L3_TAB1_04', 'L2_TAB2_01' FROM DUAL UNION ALL
SELECT 'L2_TAB2_01', 'L1_TAB2_01' FROM DUAL UNION ALL
SELECT 'L1_TAB2_01', 'Feed2_01' FROM DUAL UNION ALL
SELECT 'L2_TAB2_03', 'L1_TAB3_03' FROM DUAL UNION ALL
SELECT 'L1_TAB3_03', 'Feed3_03' FROM DUAL UNION ALL
SELECT 'L2_TAB2_02', 'L1_TAB1_02' FROM DUAL UNION ALL
SELECT 'L2_TAB2_02', 'L1_TAB2_02' FROM DUAL UNION ALL
SELECT 'L2_TAB2_02', 'L1_TAB3_02' FROM DUAL UNION ALL
SELECT 'L1_TAB3_02', 'Feed3_02' FROM DUAL UNION ALL
SELECT 'L1_TAB2_02', 'Feed2_02' FROM DUAL UNION ALL
SELECT 'L1_TAB1_02', 'Feed1_02' FROM DUAL UNION ALL
SELECT 'L2_TAB1_03', 'L1_TAB1_01' FROM DUAL UNION ALL
SELECT 'L2_TAB1_03', 'L1_TAB3_01' FROM DUAL UNION ALL
SELECT 'L1_TAB3_01', 'Feed3_01' FROM DUAL UNION ALL
SELECT 'L1_TAB1_01', 'Feed1_01' FROM DUAL),
t2 AS
(SELECT LTRIM(SYS_CONNECT_BY_PATH(col2, '-'), '-') p
FROM t
CONNECT BY col2 = PRIOR col1),
t3 AS
(SELECT DISTINCT SUBSTR(p, 1, INSTR(p, '-') - 1) AS startcol
FROM (SELECT p
FROM t2
MINUS
SELECT SUBSTR(p, INSTR(p, '-') + 1)
FROM t2))
SELECT (SELECT REVERSE(MAX(SYS_CONNECT_BY_PATH(REVERSE(col1), '-'))) || startcol
FROM t
START WITH col2 = startcol
CONNECT BY PRIOR col1 = col2) AS path
FROM t3;

Can we write any equivalent query in Sql-Server?

Regards,
Koushik Chandra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources