hierarchy table from excel outline

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)

hierarchy table from excel outline

Author

Topic

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-26 : 13:19:47

Hi,
I have a hierarchy in excel in the following format.

country1,NULL,NULL
NULL,state1,null
null,null,city1
null,null,city2
country2,null,null
null,state2,null
null,state3,null

I need to have this in a table like this format:-

country1 state1 city1
country1 state1 city11
country1 state1 city111
country2 state2
country3 state3

Any suggestions on the best logic for this ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-27 : 02:11:07

put the current excel data into a staging table of following format.

CREATE TABLE Excel_Staging
(
ID int IDENTITY(1,1),
Country varchar(100),
State varchar(100),
City varchar(100)
)
then try using this query

select t1.Country,tmp.State,tmp.City
from table t1
outer apply (select min(ID)
             from table
             where  Country is not null
             and ID > t1.ID)t2
left outer join
(
select st1.ID,st1.State,st3.City
from table st1
outer apply ( select min(ID)
              from table
              where State is not null
              and ID > st1.ID)st2
left outer join table st3
on st3.ID > st1.ID
and (st3.ID < st2.ID or st2.ID is null)
and t3.City is not null
and t1.State is not null)tmp
on tmp.ID > t1.ID
and (tmp.ID < t2.ID or t2.ID is null)
and t1.Country is not null

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-27 : 08:44:08

I didnt follow anything from there Visakh, can you just relook at what you sent me and post it again.Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-27 : 11:19:20

quote:
Originally posted by sakets_2000

I didnt follow anything from there Visakh, can you just relook at what you sent me and post it again.Thanks

I was asking you to transfer data from excel as it is to a new table of format shown. Then you may try using the query i gave to see if you get exepected results and use this to insert to your table.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-27 : 12:58:53

i was unclear on the query part,doesn't work, i can't make out what logic you are using there to re-write it on my own.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-27 : 23:15:06

quote:
Originally posted by sakets_2000

i was unclear on the query part,doesn't work, i can't make out what logic you are using there to re-write it on my own.

you have data in format given i your excel. I was telling you to do this steps

1.Export the data from excel as it is including NULL values to a temp table.
2.Use my query to get data in form you wanted to your final table.
Unforunately query i gave had some issues. I had mailed it from home where i didnt have a sql box to test. I've tested it now and made a few changes. Now its returning results as expected. You may now use it to populate your final table

--staging table to put excel values directly
declare @start table
(
ID int identity(1,1),
Country varchar(50),
State varchar(50),
City varchar(50)
)

--you populate this from excel. I'm manually inserting for example sake
insert into @start (Country,State,City) values ('country1',NULL,NULL) 
insert into @start (Country,State,City) values (NULL,'state1',null)
insert into @start (Country,State,City) values (null,null,'city1')
insert into @start (Country,State,City) values (null,null,'city2')
insert into @start (Country,State,City) values ('country2',null,null)
insert into @start (Country,State,City) values (null,'state2',null)
insert into @start (Country,State,City) values (null,'state3',null)
insert into @start (Country,State,City) values ('country3',null,null)
insert into @start (Country,State,City) values (null,'state4',null)
insert into @start (Country,State,City) values (null,null,'city3')
insert into @start (Country,State,City) values ('country4',null,null)
insert into @start (Country,State,City) values (null,'state5',null)
insert into @start (Country,State,City) values (null,'state6',null)


select * from @start--check data inserted


INSERT INTO Result (Country,State,City)--your final table
select t1.Country,tmp.State,tmp.City
from @start t1
outer apply (select min(ID) as ID
             from @start
             where  Country is not null
             and ID > t1.ID)t2
left outer join
(
select st1.ID,st1.State,st3.City
from @start st1
outer apply ( select min(ID) as ID
              from @start
              where State is not null
              and ID > st1.ID)st2
left outer join @start st3
on st3.ID > st1.ID
and (st3.ID < st2.ID or st2.ID is null)
and st3.City is not null
WHERE st1.State is not null
)tmp
on tmp.ID > t1.ID
and (tmp.ID < t2.ID or t2.ID is null)
WHERE t1.Country is not null


select * from Result--check final data

output
---------
Country              State                City
-------------------- -------------------- --------------------
country1             state1               city1
country1             state1               city2
country2             state2               NULL
country2             state3               NULL
country3             state4               city3
country4             state5               NULL
country4             state6               NULL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-28 : 01:18:02

and use this to export data to table from EXCEL
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-28 : 02:02:54

Thanks a lot for that ...unfortunately I am working on sql server 2000 and "apply" keyword doesn't work here ...I should have told this before ..Thanks anyhow..I'll work on the query myself..have troubled ya enough already :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 02:46:29

Not a problem...Let me see how i can crack this in 2000....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 03:33:37

quote:
Originally posted by sakets_2000

Thanks a lot for that ...unfortunately I am working on sql server 2000 and "apply" keyword doesn't work here ...I should have told this before ..Thanks anyhow..I'll work on the query myself..have troubled ya enough already :)

here is 2000 compatible solution:-

declare @start table
(
ID int identity(1,1),
Country varchar(20),
State varchar(20),
City varchar(20)
)

insert into @start (Country,State,City) values ('country1',NULL,NULL) 
insert into @start (Country,State,City) values (NULL,'state1',null)
insert into @start (Country,State,City) values (null,null,'city1')
insert into @start (Country,State,City) values (null,null,'city2')
insert into @start (Country,State,City) values ('country2',null,null)
insert into @start (Country,State,City) values (null,'state2',null)
insert into @start (Country,State,City) values (null,'state3',null)
insert into @start (Country,State,City) values ('country3',null,null)
insert into @start (Country,State,City) values (null,'state4',null)
insert into @start (Country,State,City) values (null,null,'city3')
insert into @start (Country,State,City) values ('country4',null,null)
insert into @start (Country,State,City) values (null,'state5',null)
insert into @start (Country,State,City) values (null,'state6',null)



select * from @start--view inserted data

--final output
select t1.Country,tmp.State,tmp.City from
(
select t.ID,t.Country,(select min(ID) 
             from @start
             where  Country is not null
            and ID > t.ID
             )as MinNextCtyID
from @start t) t1
left outer join
(
select st1.ID,st1.State,st3.City from
(
select t.ID,t.State,( select min(ID) 
              from @start
              where State is not null
              and ID > t.ID)as MinNextID--,st3.City
from @start t) st1

left outer join @start st3
on st3.ID > st1.ID
and (st3.ID < st1.MinNextID or st1.MinNextID is null)
and st3.City is not null
WHERE st1.State is not null
)tmp
on tmp.ID > t1.ID
and (tmp.ID < t1.MinNextCtyID or t1.MinNextCtyID is null)
WHERE t1.Country is not null

output
------------------
inserted data
--------------
ID          Country              State                City
----------- -------------------- -------------------- --------------------
1           country1             NULL                 NULL
2           NULL                 state1               NULL
3           NULL                 NULL                 city1
4           NULL                 NULL                 city2
5           country2             NULL                 NULL
6           NULL                 state2               NULL
7           NULL                 state3               NULL
8           country3             NULL                 NULL
9           NULL                 state4               NULL
10          NULL                 NULL                 city3
11          country4             NULL                 NULL
12          NULL                 state5               NULL
13          NULL                 state6               NULL


output data
---------------

Country              State                City
-------------------- -------------------- --------------------
country1             state1               city1
country1             state1               city2
country2             state2               NULL
country2             state3               NULL
country3             state4               city3
country4             state5               NULL
country4             state6               NULL

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-28 : 05:07:57

Ahh.. works just fine .. thanks a lot !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources