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.googlegr oups.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

grouping problem...

I am migrating an Oracle database to SQL. I can't the following query to
work
insert into JDE911Z1
(vnani,vnaa,vndgm,vndgd,vndgy,vnctry,vndct,vnexa,v nlt,vnedbt)
select approp_no + '.1354', sum(isnull(tot_cost,0)),
datepart(mm, GETDATE()) mm, datepart(dd, GETDATE()) dd,
substring( cast(DATEPART(yy, GETDATE()) as varchar(4)), 3,2) yy ,
'0','JE','DASNY CHARGEBACK','AA','AA'+@.client+cast (@.pay_no as varchar(2))
from perptot
where pay_no = @.pay_no and client = @.client'
and approp_no is not null
and b_cat = 'B'
group by approp_no + '.1354', datepart(mm, getdate()),
datepart(dd, getdate()), substring( cast(DATEPART(yy, GETDATE()) as
varchar(4)), 3,2),
'O', 'JE','DASNY CHARGEBACK','AA','AA','AA'+ @.client + cast (@.pay_no as
varchar(2))
The error I get is "GROUP BY expressions must refer to column names that
appear in the select list"
Group by approp_no + '.1354', datepart(mm, getdate()), datepart(dd,
getdate()) works. As soon as I add substring( cast(DATEPART(yy, GETDATE())
as varchar(4)), 3,2) it breaks.
That value just uses getdate() so should be a constant as far as the query is concerned and so does not need to appear in the group by clause.

GridView delete function

This is killing me. I've searched the forums for hours and can't find the answer. My SQLDataSource is working fine except when I want to delete. I've allowed the delete function to be shown on the gridview. This is my SQLDataSource:

 <asp:SqlDataSource ID="IndexDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:IndexConnectionString%>" SelectCommand="SELECT * FROM [Index] WHERE (Type LIKE '%' + @.SearchText2 + '%') OR (Product LIKE '%' + @.SearchText2 + '%') OR (Version LIKE '%' + @.SearchText2 + '%') OR (Binder LIKE '%' + @.SearchText2 + '%') OR (Language LIKE '%' + @.SearchText2 + '%') OR (CDName LIKE '%' + @.SearchText2 + '%') OR (Details LIKE '%' + @.SearchText2 + '%') OR (ISOLink LIKE '%' + @.SearchText2 + '%')" DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.original_ID" UpdateCommand="UPDATE [Index] SET Type = @.Type, Product = @.Product , Version = @.Version, Binder = @.Binder, Language = @.Language, CDName = @.CDName, Details = @.Details, ISOLink = @.ISOLink WHERE ID = @.ID"> <SelectParameters> <asp:ControlParameter Name="SearchText2" Type="String" ControlID="SearchText2" PropertyName="Text" ConvertEmptyStringToNull="False" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Type" /> <asp:Parameter Name="Product" /> <asp:Parameter Name="Version" /> <asp:Parameter Name="Binder" /> <asp:Parameter Name="Language" /> <asp:Parameter Name="CDName" /> <asp:Parameter Name="Details" /> <asp:Parameter Name="ISOLink" /> </UpdateParameters> </asp:SqlDataSource>

It doesn't give me an error if I click delete but it doesn't delete the record. I've tried changing the DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> but it gives me the error "Must declare the scaler variable of '@.ID'"... I saw in this post http://forums.asp.net/p/1077738/1587043.aspx#1587043 that the answer was that "The variable you have declared in the definition of the proc isdifferent from the variable you are using in the WHERE clause." when they are both the same. Thanks for any help.

-Brandan

Hello

What if you add a semicolumn after @.original_ID ? like this"DELETE FROM [Index] WHERE [ID] = @.original_ID;"

|||

Is the ID column your table's primary key? If yes, you need to make sure that the ID is set in your GridView's DadaKeyNames and you should change your DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> and DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.ID"

If you cannot make this work, please post your GridView part here and if you can list all your columns' name instead of a * in your SELECT statement, that would be great. Thanks.

|||

If it doesn't fixes it it might just be the DataKeyNames field of the gridview that need to be set to ID .

|||

**RESOLVED**

It was definitely the DataKeyNames. I had recreated the gridview so many times I forgot to put it back in. thanks ya'll.