Display query results as a treeview

Question

Post reply

Display query results as a treeview

a2zwd

SSCrazy

Points: 2356
More actions
February 6, 2009 at 5:29 am

#199914

Hi
I have two tables
1. Countries
2. Provinces
I want to retrieve the results in a tree view
Ex:
USA
----AR
----AZ
UK
----Middlesex
----Midlands
Can anybody give me the query displays as above?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

What have you tried so far?

Please post table structures and sample data. You may find it helpful to read the article in the link below.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rob Fisk SSCommitted Points: 1861 More actions · Answer 2

It's Friday I can't be bothered with real work so i thought I'd give this a few minutes. the ROW_NUMBER stuff is one of the most useful things I have found in SQL 2k5 along with the unpivot.

WITH list AS

(

SELECT ROW_NUMBER() OVER(PARTITION BY [countryName] ORDER BY [provinceName]) As [itemNumber],

countryName,

p.provinceName,

p.countryID

FROMCountries c

JOINProvinces p

ONp.countryID = c.countryID

UNION --Added this so that the provinces started the row below countries rather than on the same level

SELECT0,CountryName,NULL,countryid

FROMCountries

)

SELECTCASE itemNumber

WHEN 0 THEN countryName

ELSE NULL -- or '---' if you want the hyphens of course

END AS [Country],

ProvinceName as Province

FROMlist

order by CountryID,ItemNumber

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

How about some sample data with that, Rob? 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

a2zwd SSCrazy Points: 2356 More actions · Answer 4

Hi

Thanks for the replies. I have tried this and got the result as I want.

select provinceid, case when provinceid = 0 then '|__ ' + description else '| |______ ' + description end as description from

(

select countryid, countryname as description, 0 as provinceid from countries

union

select countryid, provincename as description, provinceid from provinces

) tab order by countryid, provinceid

I tried already using WITH (CTE), but my sql server compatability level is 80, so it couldnt support.

Thanks again for the replies