dynamically adding column and updating table

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 2000 Forums

SQL Server Development (2000)

dynamically adding column and updating table

Author

Topic

singhmonica
Starting Member

22 Posts

Posted - 2005-02-14 : 13:29:47

I want to dynamically add columns- path1 to pathn based on the hierarchy column in the table below, such that each node (/) represented in the hierarchy column is broken as seperate column values from path1 to pathn. The sql should be able to figure out how many columns to add based on the hierarchy column.

sample data bug table-

[level] [hierarchy] [father_id] [item_id] [bug_id]
[3] [0/2/0664/1232/ ] [664] [1232] [5]
[4] [0/2/0178/3236/2476/] [3236] [2476] [6791]
[9] [0/2/0664/1232/0665/0666/1335/1206/1359/1201/] [1359] [1201] [39]

Desired output-
[level] [hierarchy] [father_id] [item_id] [bug_id] p1 p2 p3 p4 p5 p6 p7 p8....pn
[3] [0/2/0664/1232/ ] [664] [1232] [5] 0 2 664 1232 null null null null null
[4] [0/2/0178/3236/2476/] [3236] [2476] [6791] 0 2 178 3236 2476 null null null null
[9] [0/2/0664/1232/0665/0666/1335/1206/1359/1201/] [1359] [1201] [39] 0 2 664 1232 0665 1335 1206 1359 1201

I have designed a sql, but dont know how to iterate it for the above scenario. Can someone help me with this?

DECLARE @x tinyint, @sql varchar(1000), @ColName varchar(50),@position int
SET @x = 1
WHILE @x <= 5
BEGIN
--Add column
SET @ColName = 'Path'+ '' + cast(@x as varchar)

SET @sql = 'ALTER TABLE Bug ADD ' +@ColName +' varchar(50)'

EXEC (@sql)

--Populate column
SET @position = 1
SET @sql = 'UPDATE bug SET '+@ColName +' = substring(J.hierarchy, @position,4)
FROM bug j

EXEC (@sql)

--increment counter
SET @position = @position +6
SET @x = @x + 1

END

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-14 : 15:35:55

I don't get it...why not just have 1 table with n columns

Brett

8-)

singhmonica
Starting Member

22 Posts

Posted - 2005-02-14 : 15:44:23

I am denormalizing the table, so that I can get a count of everything underneath a node, and not just at a node. Pls. read my previous post- http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=45014&SearchTerms=recursive

If I have each node (0000/0002/0664) broken out into seperate columns like 0, 2, 664, I can do a count of the unique node (eg.664)and query everything underneath it.

can this be done?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-14 : 16:05:40

monica - see the email I sent you.

I meant to say this in the email, but it belongs here as well. I don't think you want to create a column per node level. The beauty of the path column is that it allows you to do recursively difficult things in a surprisingly simple manner.

Let me know if my example didn't help you out!