Creating a heirarchical output from SQL statement

This may be a basic question, but defining anything other than a cursor
is preffered.

I have, as an example, 2 tables. One with customer data (addresses,
phones, etc), the other is a listing of all 50 states (a cross reference
for short state alias to long state name, i.e. FL - Florida, etc...).

I want to sort the out put by state long name, and show each customer in
the state ... BUT ...
the output needs to be like so:

Florida
ABC,Inc Address1 City, State Zip, other Info
Dummy Corp Address1 City, State Zip, other Info
...
Georgia
XYZ, Inc Address1 City, State Zip, other Info
...
etc ...

This is a basic heirarchical listing. Can this be done with a single
T-SQL statement or are cursors needed?

Thanks in advance.

"Excellence is achieved through 1% inspiration and 99% perspiration." A.
Einstein

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!>> the output needs to be like so: ... <<

That is a report; SQL is not a report writer; you need to do this in
the front end with a report writer. This is the whole idea of
client/server architecture.

CREATE XML SCHEMA COLLECTION ddl statement?

what's the purpose of defining a "relational schema name" in the CREATE XML SCHEMA COLLECTION ddl statement? i don't understand why it's an option.

All objects in SQL Server 2005 database distributed among schemas.

Default schema is dbo. So when you create table "MyTable", its full name dbo.MyTable or Sales.MyTable etc. For more examples, please, see database AdventureWorks.

XML SCHEMA COLLECTION also included into one of database schemas.

|||

Konstantin Kosinsky wrote:

All objects in SQL Server 2005 database distributed among schemas.

Thanks for your reply, but I already knew this. What I previously didn't understand was the purpose of schemas. Anyway, I found the following in the BOL:

"The roles of schemas are enhanced in SQL Server databases. All objects in a database are now located in schemas and are not owned by individual users. Each schema can be owned by roles, allowing multiple users to administer database objects. In earlier versions of SQL Server, users could not be dropped from a database without having to reassign the ownership of each individual object that they owned. Now ownership only has to be adjusted for the schema, not each object." http://msdn2.microsoft.com/en-us/library/ms170800.aspx

Aha! Now it all makes sense to me.