Grouping Results (Summarizing)

I can't figure out how to take display my results like so...
NJ
--Trenton, John Doe
--Voorhees, Jane Smith
Pennsylvania
--Philadelphia, Ken James
--Pittsburgh, Alfred Herms
NY
--Brooklyn, Llyod Banks
--Syracuse, Howard Douglas
within a SQL statement..<vncntj@.hotmail.com> wrote in message
news:1140727759.857021.104520@.v46g2000cwv.googlegroups.com...
>I can't figure out how to take display my results like so...
>
> NJ
> --Trenton, John Doe
> --Voorhees, Jane Smith
> Pennsylvania
> --Philadelphia, Ken James
> --Pittsburgh, Alfred Herms
> NY
> --Brooklyn, Llyod Banks
> --Syracuse, Howard Douglas
> within a SQL statement..
>
Since you haven't told us what the table structure is, what your original
data looks like or what version of SQL Server you are are using I don't
think I can figure it out either. Read my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CREATE TABLE dbo.Table1
(
state nvarchar(50) NULL,
city nvarchar(50) NULL,
name nvarchar(50) NULL
)
INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
Doe')
INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
Smith')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PHILADELPHIA', 'Ken Jame')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PITTSBURGH', 'Alfred Herms')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
'Lloyd Banks')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
'Howard Douglas')
Is there a way to present the data as so...
NJ
-Trenton
-Voorhees
Pennsylvania
-Philadelphia
-Pittsburgh
NY
-Brooklyn
-Syracuse|||vncntj@.hotmail.com wrote:
> CREATE TABLE dbo.Table1
> (
> state nvarchar(50) NULL,
> city nvarchar(50) NULL,
> name nvarchar(50) NULL
> )
> INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
> Doe')
> INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
> Smith')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PHILADELPHIA', 'Ken Jame')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PITTSBURGH', 'Alfred Herms')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
> 'Lloyd Banks')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
> 'Howard Douglas')
> Is there a way to present the data as so...
> NJ
> -Trenton
> -Voorhees
> Pennsylvania
> -Philadelphia
> -Pittsburgh
> NY
> -Brooklyn
> -Syracuse
Given your original table any reporting tool will output data with
formatted bands like that. So unless you want to print or display
direct from your query tool it seems like it would be very inconvenient
to do all that formatting in a result set. SQL isn't a report writing
tool.
If you really have no other option then you could do something like
this:
SELECT s
FROM
(SELECT state, 1, state
FROM Table1
UNION
SELECT state, 2, '-- '+city
FROM Table1) AS T(t,o,s)
ORDER BY t,o ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--