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
--sql

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 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
--

Grouping Problem

Hi Friends

i have records at different level, i.e, accounts in a tree fomat. i want to display top most account, then on drill down it should show its child accounts and so on.

PROBLEM: levels are different, few account have grand children account and few doesn't have.

Is it possible to achieve what i am trying.

Hope i explained my problem.
Waiting for Sugesstions.

RaheemHave you tried looking at the help on Hierarchical Grouping? Might be what you're looking for.|||Thanks Very much|||it is working perfectly thanks again, but a small problem :-)

when i am using indent in hierarchical grouping options, even my hierarchichal summary fields are getting indented.

Any help in this Regard

Grouping problem

I am trying to get a table to display where my like rows would sum together,
but now matter how I do it there are 2 rows (in my example) that always show
as separate rows and I want to combine them.
For example:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
Should not show 0 0 0 0
Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
the 60 and 90 column should be on the same line.
This was done with the following statement:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (p1.PurchasedProductID = p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1
If I add the (ProductTypeID = 1) to the last line:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)
Then I get:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
This gets rid of the last line (which I wanted).
What I would like it to look like is:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 30 0 20 10
(5) 60-Day Posting 5 0 5 0
How can I make it do that?
I assume I have to group it, but I can't seem to make that work with these
subqueries. I get errors, such as you can't use a subquery in a group by
clause.
Here is the table and data (really cut down).
drop table PurchasedProducts
go
CREATE TABLE [dbo].[PurchasedProducts] (
[PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductTypeID] [int] NULL,
[ProductName] [varchar] (20) NULL ,
[PostingsLeft] [int] NULL ,
[DateExpires] [datetime] NULL
) ON [PRIMARY]
GO
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('30-Day Posting',1,1,'11/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,10,'1/24/2006')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,20,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(5) 60-Day Posting',1,5,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('Should not show',2,90,'12/01/2005')
go
Thanks,
TomThe easiest way to solve this is to group your results as illustrated below:
SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
SUM(days60) AS [60], SUM(days90) AS [90]
FROM (select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
Days30 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
Days60 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
Days90 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)) AS a
GROUP BY productname
- Peter Ward
WARDY IT Solutions
"tshad" wrote:

> I am trying to get a table to display where my like rows would sum togethe
r,
> but now matter how I do it there are 2 rows (in my example) that always sh
ow
> as separate rows and I want to combine them.
> For example:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> Should not show 0 0 0 0
> Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
> the 60 and 90 column should be on the same line.
> This was done with the following statement:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (p1.PurchasedProductID = p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1
> If I add the (ProductTypeID = 1) to the last line:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)
> Then I get:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> This gets rid of the last line (which I wanted).
> What I would like it to look like is:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 30 0 20 10
> (5) 60-Day Posting 5 0 5 0
> How can I make it do that?
> I assume I have to group it, but I can't seem to make that work with these
> subqueries. I get errors, such as you can't use a subquery in a group by
> clause.
> Here is the table and data (really cut down).
> drop table PurchasedProducts
> go
> CREATE TABLE [dbo].[PurchasedProducts] (
> [PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProductTypeID] [int] NULL,
> [ProductName] [varchar] (20) NULL ,
> [PostingsLeft] [int] NULL ,
> [DateExpires] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('30-Day Posting',1,1,'11/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,10,'1/24/2006')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,20,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(5) 60-Day Posting',1,5,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('Should not show',2,90,'12/01/2005')
> go
>
> Thanks,
> Tom
>
>|||"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:89A22616-2111-4C1B-9D6D-F9AF452FB238@.microsoft.com...
> The easiest way to solve this is to group your results as illustrated
below:
Does it!!
You just treated my select as another table. I can never seem to come up
with that myself. I always understand it when I see it, but I can't seem to
see it when I need it.
Not really sure of the thought process to come up with it.
I was almost there, but couldn't quite see it.
Thanks,
Tom
> SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
> SUM(days60) AS [60], SUM(days90) AS [90]
> FROM (select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> Days30 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> Days60 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> Days90 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)) AS a
> GROUP BY productname
>
> - Peter Ward
> WARDY IT Solutions
> "tshad" wrote:
>
together,
show
in
these
by
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]

Grouping Multiple Dimensions?

We use Excel 2007 as our front end to our AS2005 cube. Is there a way to group/display multiple dimensions under a single area? I know that you can use Display folders with measures.

Are you wanting to have a folder displayed under which attributes from multiple dimensions are displayed, or are you just wanting attributes from multiple dimensions displayed on an axis of your pivot table?

B.

|||Hi Brian,

The former. I'd like to have a folder or section where attributes from multiple dimensions can be displayed in a group. For example, let's say I have a Products dimension with a Product Line attribute and a Ship Node dimension with a Ship Node Name attribute. I'd like to have the Product Line and Ship Node attributes under a grouping or displayed in a section called "xxx" so users would just navigate to "xxx" in the Field List to find the aforementioned attributes. Does that make sense? And is that possible in Excel 2007?
|||

When you pull data from an OLAP cube directly into Excel 2007, it gives the option of generating a Pivot Table or a Pivot Table & Chart. Either way, you get that field list on the side of the window that organizes everything the way it is organized in the cube.

I am not aware of an easy way to alter this. One thing that comes to mind is possibly connecting directly to the relational data warehouse that feeds to cube (but this would require you to by-pass SSAS security). But then, everything would be jumbled.

Another would be to have an SSRS report with a single table of the elements you want and then call the report's URL with rendering instructions for either CSV or XML (or just have the report generate an XLS and open that yourself) and then generating a pivot table off the Excel data set. Still, I can't really see going into production with the SSRS solutoin.

B.

|||

Hello! I do not think it is possible.

Actually I have created named sets with crossjoin of two separate dimensions like product and customer in AS2005 and the previous version. It is possible to build them on the server, but the problem is that no client I have seen, like ProClarity Professional, will show them(and support them). These sets(or attributes from different dimensions) are not supported in any client that I know about. They will not show up in dimension tools in clients.

Since I do not now about every client on the market I can be wrong.

HTH

Thomas Ivarsson

|||Thanks for the input Brian and Thomas! I'll give it to the rest of the day to see if I can come up with anything.

Grouping in columns rather than rows using table control?

Is there a way to transform the table object to display group data in columns
instead of rows? Here is my example:
Report services table can do this when grouping on YEAR
[1 GROUP Header (YEAR)
[Header
[ BODY Parameter1 Parameter 2 Parameter 3
[FOOTER
[1 GROUP Footer (SUM)
Example
Year 2000
Mike John Mary
5 1 4
5 2 2
SUM 10 3 6
Year 2001
Mike John Mary
1 6 5
2 2 2
SUM 3 8 7
What I want is this:
[ Group Header ] [Table Header] [DATA] [Table Footer] [Group
Footer]
YEAR Parameter 1
SUM
Parameter 2
Parameter 3
2000 SUM 2001 SUM
Mike 5 5 10 1 2 3
John 1 2 3 6 2 8
Mary 4 2 6 5 2 7
So the idea is to group by Year but display the SUMs in a column not in a
row. I just can't figure out how to use the Matrix control, I want to use the
table control functionality but with column output.
Thanksyou can use a matrix to do just that
"Ramez" wrote:
> Is there a way to transform the table object to display group data in columns
> instead of rows? Here is my example:
> Report services table can do this when grouping on YEAR
> [1 GROUP Header (YEAR)
> [Header
> [ BODY Parameter1 Parameter 2 Parameter 3
> [FOOTER
> [1 GROUP Footer (SUM)
> Example
> Year 2000
> Mike John Mary
> 5 1 4
> 5 2 2
> SUM 10 3 6
> Year 2001
> Mike John Mary
> 1 6 5
> 2 2 2
> SUM 3 8 7
> What I want is this:
> [ Group Header ] [Table Header] [DATA] [Table Footer] [Group
> Footer]
> YEAR Parameter 1
> SUM
> Parameter 2
> Parameter 3
> 2000 SUM 2001 SUM
> Mike 5 5 10 1 2 3
> John 1 2 3 6 2 8
> Mary 4 2 6 5 2 7
> So the idea is to group by Year but display the SUMs in a column not in a
> row. I just can't figure out how to use the Matrix control, I want to use the
> table control functionality but with column output.
> Thanks

Grouping based on multiple fields

I am using crystal report version 7.
I am linking the stored procedure to crystal report and display it's fields. I want to create the group having 2 fields and sum the amount field. At present, I can create group with only one field and sum the amount field based on this field.
How can I have the group defined by 2 fields?Create a formula joining the two fields:
{field1}+{field2}

and then group on that formula|||Thanks Anonymous2,
That resolved my problem!

Grouping and counting

Hi,
I will try to explain:
I want the proper grouping and display counts. This query
works fine, see below some of the returned results.
SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
FROM
TempE INNER JOIN TempD
ON TempE.Numb = TempD.Numb
WHERE(TempE.sp in('bm','pm') or
TempE.sp2 in('bm','pm'))
GROUP BY TempD.state,TempD.City,TempD.Zip
ORDER BY TempD.state, TempD.City
Some of the results returned:
State City Zip total
Alabama ALBASTER 35007 2
Alabama BIRMINGHAM 35292 1
Arizona YUMA 85364 1
California PALMDALE 93550 1
Connecticut NEW LONDON 06320 2
Connecticut WOODBRIDGE 06525 1
Delaware CLAYMONT 19703 1
Delaware WILMINGTON 19801 3
North Carolina ASHEVILLE 28815 10
North Carolina ASHEVILLE 28816 3
South Carolina CHAPIN 29036 1
South Carolina CHARLESTON 29401 116
South Carolina CHARLESTON 29402 8
I want two more columns after total for "bm" and "pm" and with
the totals broke down like the following
State City Zip total
bm pm
Alabama ALBASTER 35007 2 1
1
Alabama BIRMINGHAM 35292 1 1 0
Arizona YUMA 85364 1 0
1
California PALMDALE 93550 1 1
0
Connecticut NEW LONDON 06320 2 2 0
Connecticut WOODBRIDGE 06525 1 0 1
Delaware CLAYMONT 19703 1 1 0
Delaware WILMINGTON 19801 3 3 0
North Carolina ASHEVILLE 28815 10 7 3
North Carolina ASHEVILLE 28816 3 0 3
South Carolina CHAPIN 29036 1 1
0
South Carolina CHARLESTON 29401 116 86 30
South Carolina CHARLESTON 29402 8 2 6
hope I was clear, Thanks
gvTry using a "case" expression.
SELECT
TempD.state,
TempD.City,
TempD.Zip,
count(TempE.ID)AS total,
sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as bm,
sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as pm
from
..
AMB
"gv" wrote:

> Hi,
> I will try to explain:
> I want the proper grouping and display counts. This query
> works fine, see below some of the returned results.
> SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
> FROM
> TempE INNER JOIN TempD
> ON TempE.Numb = TempD.Numb
> WHERE(TempE.sp in('bm','pm') or
> TempE.sp2 in('bm','pm'))
> GROUP BY TempD.state,TempD.City,TempD.Zip
> ORDER BY TempD.state, TempD.City
> Some of the results returned:
> State City Zip total
> Alabama ALBASTER 35007 2
> Alabama BIRMINGHAM 35292 1
> Arizona YUMA 85364 1
> California PALMDALE 93550 1
> Connecticut NEW LONDON 06320 2
> Connecticut WOODBRIDGE 06525 1
> Delaware CLAYMONT 19703 1
> Delaware WILMINGTON 19801 3
> North Carolina ASHEVILLE 28815 10
> North Carolina ASHEVILLE 28816 3
> South Carolina CHAPIN 29036 1
> South Carolina CHARLESTON 29401 116
> South Carolina CHARLESTON 29402 8
> I want two more columns after total for "bm" and "pm" and with
> the totals broke down like the following
> State City Zip total
> bm pm
> Alabama ALBASTER 35007 2 1
> 1
> Alabama BIRMINGHAM 35292 1 1 0
> Arizona YUMA 85364 1
0
> 1
> California PALMDALE 93550 1 1
> 0
> Connecticut NEW LONDON 06320 2 2 0
> Connecticut WOODBRIDGE 06525 1 0 1
> Delaware CLAYMONT 19703 1 1
0
> Delaware WILMINGTON 19801 3 3 0
> North Carolina ASHEVILLE 28815 10 7 3
> North Carolina ASHEVILLE 28816 3 0
3
> South Carolina CHAPIN 29036 1 1
> 0
> South Carolina CHARLESTON 29401 116 86 30
> South Carolina CHARLESTON 29402 8 2 6
> hope I was clear, Thanks
> gv
>
>
>|||Thanks!!
gv
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1996E862-0C8C-4C22-A154-57D5B728A650@.microsoft.com...
> Try using a "case" expression.
> SELECT
> TempD.state,
> TempD.City,
> TempD.Zip,
> count(TempE.ID)AS total,
> sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as
> bm,
> sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as
> pm
> from
> ...
>
> AMB
> "gv" wrote:
>

group numbering issue

Hi everyone,

The problem is about numbering groups. I'm trying to display data like this-

1. U.S

1.1 San Francisco

1.1.1 employee1

1.1.2 employee2

1.2 Dallas

1.2.1 employee3

2. UK

2.1 London

2.1.1 employee4

I've tried using RunningValue(1, Sum, "group_name") but it gives erroneous results. I'm using 3 lists, one at each group level. The outermost list (1st list) is grouped by "country" (group_1), 2nd one is grouped by city (group_2) and the innermost (3rd one) by employee name (group_3).

I can't figure whats wrong. Can anyone help me?

Thanks in advance.

We were using Access reports before moving to SQL server, and this numbering works fine in Access. So, I'm wondering if this is a bug in Reporting Services. Is it?

Does anyone know how to do this numbering in SQL Server/Reports?

Thanks

|||

You have to use the CountDistinct aggregation to count the members across the group e.g.

Country Level

= RunningValue(Fields!Country.Value, CountDistinct, Nothing).ToString() + "."

City Level

= RunningValue(Fields!Country.Value, CountDistinct, Nothing).ToString() + "."
+ RunningValue(Fields!City.Value, CountDistinct,"group_country").ToString()

Employee Level

= RunningValue(Fields!Country.Value, CountDistinct, Nothing).ToString() + "."
+ RunningValue(Fields!City.Value, CountDistinct,"group_country").ToString() + "."
+ RunningValue(Fields!Employee.Value, CountDistinct,"group_city").ToString()

|||

Thank you so much. It works!

The SUM aggregation was created by Visual studio when I imported the Access report. That was misleading. Anyways, this issue was bothering me for so long, I'm so glad its resolved finally.

Thanks again.

Group Name in Page Header

When using Grouping, I need to display the value of the grouped field
in the page header (I'm also inserting a page break at the end of each
group). Is this possible?Yes this is possible in your case. Assuming you have a textbox called
GroupName in the grouping header which shows the current value of the
grouping, you can just add another textbox in the page header which
references the value of the GroupName textbox:
=ReportItems!GroupName.Value
Note: only the ReportItems collection is accessible in the page
headers/footers, but not the Fields collection.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"J Chang" <julia.chang@.maritz.com> wrote in message
news:946c3e64.0410221341.3018ec18@.posting.google.com...
> When using Grouping, I need to display the value of the grouped field
> in the page header (I'm also inserting a page break at the end of each
> group). Is this possible?

Group Min Record

Hello Guyz,

A small problem here, I have the below table and I need to group and display the record that has the minimum value in the table (this table is derived from a query that permutates some records to give me this result).

F1 F2 F3
QQQ C 2
QQQ B 1
QQQ A 3

expected result:
QQQ B 1

my result:
when I group by F1, First(F2) and MIN(F3):
QQQ C 1

when I group by F1, MIN(F2) and MIN(F3):
QQQ A 1

when I group by F1, F2 and MIN(F3):
QQQ C 2
QQQ B 1
QQQ A 3

Any help would be very much appreciated..

CyherusYou don't GROUP BY the aggregate for one thing. What you need is something like this in this particular example:

SELECT
t1.F1,
t1.F2,
t1.F3
FROM
table t1
INNER JOIN (
SELECT MIN(F3) AS F3 FROM table) t2 ON t1.F3 = t2.F3

This is assuming you're using SQL Server, which you probably aren't.

Group Header

Hi,
In my group header I have a cell that I want to display "the group number".
Ex.
Group A - 1
XXXXXXXXXX
Group B - 2
XXXXXXXXXX
Group C - 3
XXXXXXXXXX
etc.
I can't find an easy way to calculate the groupnumber.
I cannot use SUM distinctvalue for the grouping since the tabel is grouped
on two fields.
How can i get lastvalue for the textbox + 1.
Can somebody pleas help me out here!!
Thanks!!!
--
daniel_bTry doing a google search on "Reporting Services" and "Sleazy Hacks" -
I believe they cover this topic somewhat loosely in the "Green-band"
article.|||Thank you. That helped alot!!!
"papaboom" wrote:
> Try doing a google search on "Reporting Services" and "Sleazy Hacks" -
> I believe they cover this topic somewhat loosely in the "Green-band"
> article.
>|||You're welcome.

GROUP BY without Aggregation -- Is it possible ?

Here is what I want to do.

I have a database with HotelInfo

hotelid
hotelname
hotelstate
hotelcity
hotelphone etc etc

I want to display all hotels I have grouped by state

SELECT hotelname, hotelcity, hotelphone
FROM HotelInfo
GROUP BY hotelstate

So basically I was For eg.

TX
hotel1 Dallas xxx-xxx-xxxx
hotel4 Plano xxx-xxx-xxxx

CA
hotel2 San Fransisco xxx-xxx-xxxx

How can I group by without Aggregation ?Why wouldn't you just do an order by instead of group by? And then let your application do the formatting.

SELECT hotelname, hotelcity, hotelphone
FROM HotelInfo
ORDER BY hotelstate|||humm... sounds good.. I thought group by will be better, so I was never thinking in direction of order by.

When I display result on my page here is how I want:

AK
------
------

CA
-----
-----

TX
------
-----
------

I guess I can leave with Orderby also......|||Well you should be able to do this with a datarepeater or datalist. Check out those controls, which should have a group header band.

http://www.codeproject.com/useritems/GridGroupFormat.asp shows a way to do this in a datagrid. http://www.datawebcontrols.com/faqs/DataLists/GroupingByCategory.print.shtml shows how to do this using a datalist. HTH|||Tell me one more thing from design point of view

Is it better to have a seperate look up table "States" with stateID, stateCode and StateName

for eg.
1 AR Arkansa
2 AZ Arizona
43 TX Texas
etc ...........

and then in Table "HotelInfo" where it is "hotelState" put the stateID ? i.e. 1 OR 2 OR 43

I have 2 other table which also needs state info.

Is it good idea OR it is better to put StateName in HotelInfo (AR OR AZ) ?|||Is it possible to have set of range specified in SQL

SELECT xx, xxx , xxxxx
FROM XYZ
WHERE ID BETWEEN 1 and 35 AND
ID BETWEEN 37 AND 50

???

If I write like this I get error ...

Group By Issue

I was asked to display two fields plus a total counts of the table but only need to group by one field and leave the other field as Display field. For example:

Select A, B, Count(*)
From Table
where ...
Group By B

It won't work on SQL unless you group both fields. Any way to get around this?

Thanks!

J827Think about it, and you'll see that it makes no logical sense.

If value A remains constant for any value B, then go ahead and group by A, as it will not affect your output.

Select A, B, Count(*)
From Table
where ...
Group By A, B

If value A varies for any given value B, then which value are you going to show in your output? You need some type of criteria for deciding. You could, for instance, use the lowest value of A:

Select min(A) as A, B, Count(*)
From Table
where ...
Group By B

I think you need to better define, or at least better explain, what your objective is.|||blindman,

Thank you for your quick response!

The value A actually is coming from different table and it is not a constant for any value B. If not grouping B, they are just part of combination running results based on the business logic but my business partner would like to display both fields in the report for no calculations should be run off of A field. Is this feasible or not?

J827|||Calculate your B totals in a subquery:

select TableA.A, Subquery.B, Subquery.RecordCount
from TableA
left outer join (select B, count(*) as RecordCount from TableB group by B) Subquery
on TableA.B = Subquery.B|||GROUP BY can return one row for each column you specify in the GROUP BY clause, plus any additional aggregates of that group as a column.

So if you have TableA, with columns Title, Sales, Price, a valid GROUP BY would be:

SELECT Title, SUM(Sales) AS Sales, MAX(PRICE) AS MaxPrice, (SUM(SALES) * (SUM(PRICE)) AS Total FROM TableA GROUP BY Title

If the values for ValueB are constant with respect to a specfic value of ValueA, then you can kinda fudge this by using a MIN or MAX function. In which case you don't need to include the column in the GROUP BY clause because MIN and MAX are aggregate functions.

group by help?

I am trying to get the last occurance of a display name in a login user log database. Basically I have a table that looks like this:

id user fname lname
-----------
1 jdoe Jane Doe
2 jdoe John Doe
3 jdoe Fred Flinstone

I run the MySQL query: SELECT name, max(id) as max_id, user FROM `logins` GROUP BY user

I get back:
id user fname lname
-----------
3 jdoe Jane Doe

I actually want:
id user fname lname
-----------
3 jdoe Fred Flinstone

Any that can help it would be greatly Appreciated!!by "last" occurrence you mean the one with the largest id?
select id
, user
, fname
, lname
from logins as ZZ
where id
= ( select max(id)
from logins
where user = ZZ.user )|||I tried this result and am still having difficulties? Do you know if this works with all versions of MySQL? I get the following error from phpmyadmin:

You have an error in your SQL syntax near 'select max(id) from logins where user=ZZ.user

Any other thoughts?|||good guess -- subqueries are not supported prior to version 4.1

how come it took you two and a half weeks to try my solution?|||If a correlated subquery is not supported, let's hope a join (and a group by) is?
Could you try this one: select a.id, a.user, a.fname, a.lname
from logins as a, logins as b
where a.user = b.user
and a.id <= b.id
group by a.id, a.user, a.fname, a.lname
having count(*) = 1

gridview binding with sqlConnection objects...

Hello All, I am new to data access and

i have got the problem to display the data into the page by binding the gridview with sqlConnection, sqlCommand and sqlDataReader objects. The actually code is written as:

protectedvoid Page_Load(object sender,EventArgs e)

{

if (!Page.IsPostBack)

{

SqlConnection myConnection;

SqlCommand myCommand;

SqlDataReader myReader;

myConnection =newSqlConnection();

myConnection.ConnectionString =ConfigurationManager.ConnectionStrings["LatteConnectionString"].ConnectionString;

myCommand =newSqlCommand();

myCommand.CommandText ="select * from AntiVirusVendors";myCommand.CommandType =CommandType.Text;

myCommand.Connection = myConnection;

myCommand.Connection.Open();

myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

GridView1.DataSource = myReader;

GridView1.DataBind();

myCommand.Dispose();

myConnection.Dispose();

}

}

and the GridView in html is listed as:

<div>

<asp:GridViewID="GridView1"runat="server">

</asp:GridView>

</div>

So the problem is --> there is nothing shown in the page, no errors no anything... just the empty page.

Any ideas would be appreciated. Thanks in advance!

Joe

sorry, the problem has been solved.

Grid display

How would I take a bunch of records and make it display in a Grid Format
(actually going to go into a DataGrid) in one select statement - or can you?
If I have 12 records:
CREATE TABLE [dbo].[Rentals] (
[RentalID] [int] IDENTITY (1, 1) NOT NULL ,
[NumberOfDays] [int] NULL ,
[NumberOfRentals] [int] NULL ,
[RentalCost] [money] NULL
) ON [PRIMARY]
GO
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,1,100)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,5,400)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,10,800)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,20,1600)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,1,175)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,5,700)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,10,1400)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,20,2800)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,1,225)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,5,900)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,10,1750)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,20,3500)
And I want it to display like so (with or without the headings) where the
number of days is in the parenthesis:
Type: Individual: Bundle (5) Bundle (10) Bundle (20)
30 Day $100 $400 $800 $1,600
60 Day $175 $700 $1,400 $2,800
90 Day $225 $900 $1,750 $3,500
The rows are grouped by days and the columns are ordered by NumberOfDays,
NumberOfRentals.
I could read them record by record and then place them into the grid, but I
would prefer to let the Select order it for me.
Thanks,
TomAs Tom says in a message a few hours ago, thanks for the DDL. It made it
easy to help you. Generally speaking it is usually suggested to do this in
the UI, not use SQL to manipulate the dat to fit the UI. On the other hand,
if you are talking small load it is fine to do it this way:
select numberOfDays,
sum(case when numberOfRentals = 1 then rentalCost else 0 end) as
Individual,
sum(case when numberOfRentals = 5 then rentalCost else 0 end) as
[Bundle(5)],
sum(case when numberOfRentals = 10 then rentalCost else 0 end) as
[Bundle(10)],
sum(case when numberOfRentals = 20 then rentalCost else 0 end) as
[Bundle(20)]
from rentals
group by numberOfDays
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e8xikzRpFHA.1444@.tk2msftngp13.phx.gbl...
> How would I take a bunch of records and make it display in a Grid Format
> (actually going to go into a DataGrid) in one select statement - or can
> you?
> If I have 12 records:
> CREATE TABLE [dbo].[Rentals] (
> [RentalID] [int] IDENTITY (1, 1) NOT NULL ,
> [NumberOfDays] [int] NULL ,
> [NumberOfRentals] [int] NULL ,
> [RentalCost] [money] NULL
> ) ON [PRIMARY]
> GO
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,1,100)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,5,400)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (30,10,800)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (30,20,1600)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,1,175)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,5,700)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (60,10,1400)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (60,20,2800)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,1,225)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,5,900)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (90,10,1750)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (90,20,3500)
> And I want it to display like so (with or without the headings) where the
> number of days is in the parenthesis:
> Type: Individual: Bundle (5) Bundle (10) Bundle (20)
> 30 Day $100 $400 $800 $1,600
> 60 Day $175 $700 $1,400 $2,800
> 90 Day $225 $900 $1,750 $3,500
> The rows are grouped by days and the columns are ordered by NumberOfDays,
> NumberOfRentals.
> I could read them record by record and then place them into the grid, but
> I would prefer to let the Select order it for me.
> Thanks,
> Tom
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OBWQYFTpFHA.2152@.TK2MSFTNGP14.phx.gbl...
> As Tom says in a message a few hours ago, thanks for the DDL. It made it
> easy to help you. Generally speaking it is usually suggested to do this
in
> the UI, not use SQL to manipulate the dat to fit the UI. On the other
hand,
> if you are talking small load it is fine to do it this way:
> select numberOfDays,
> sum(case when numberOfRentals = 1 then rentalCost else 0 end) as
> Individual,
> sum(case when numberOfRentals = 5 then rentalCost else 0 end) as
> [Bundle(5)],
> sum(case when numberOfRentals = 10 then rentalCost else 0 end) as
> [Bundle(10)],
> sum(case when numberOfRentals = 20 then rentalCost else 0 end) as
> [Bundle(20)]
> from rentals
> group by numberOfDays
That would work great, but is there a way to do this by separating it by the
grouping. In otherwords, I don't know that it will always be 5, 10 and 20.
It might be some other grouping so I would like to do it where I am not
doing an "= 1", "= 2" type of scenario.
My boss might change it 6 months from now and have a bundle of 15.
Thanks,
Tom
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e8xikzRpFHA.1444@.tk2msftngp13.phx.gbl...
(30,1,100)
(30,5,400)
(60,1,175)
(60,5,700)
(90,1,225)
(90,5,900)
the
(20)
NumberOfDays,
but
>|||The only way is to use dynamic sql. You would automate the select clause
from the values in the table. Personally if the change is very seldom I
would just make it something that you change whenever it changes in the
table as it will take you longer to make this change than it will to hard
code the values five or six times, including testing.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"tshad" <tfs@.dslextreme.com> wrote in message
news:ehlDmvUpFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OBWQYFTpFHA.2152@.TK2MSFTNGP14.phx.gbl...
> in
> hand,
> That would work great, but is there a way to do this by separating it by
> the
> grouping. In otherwords, I don't know that it will always be 5, 10 and
> 20.
> It might be some other grouping so I would like to do it where I am not
> doing an "= 1", "= 2" type of scenario.
> My boss might change it 6 months from now and have a bundle of 15.
> Thanks,
> Tom
> --
> (30,1,100)
> (30,5,400)
> (60,1,175)
> (60,5,700)
> (90,1,225)
> (90,5,900)
> the
> (20)
> NumberOfDays,
> but
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uXsuKCcpFHA.2904@.TK2MSFTNGP12.phx.gbl...
> The only way is to use dynamic sql. You would automate the select clause
> from the values in the table. Personally if the change is very seldom I
> would just make it something that you change whenever it changes in the
> table as it will take you longer to make this change than it will to hard
> code the values five or six times, including testing.
The problem is that this is one we are using and there are other companies
that will use the system that may not use the Bundles we are using so it
would not be just one change.
How would you use Dynamic Sql to do this?
This will be read into a DataGrid, and it would be easy to make the columns
visible/invisible based on the number of columns that are returned.
Thanks,
Tom
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "tshad" <tfs@.dslextreme.com> wrote in message
> news:ehlDmvUpFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||I also tried to add in the Rental ID to select statement and can't make it
work with the column titles. I tried using the titles from the "as column",
but got an error in the Group clause
I tried to change your statement to:
select numberOfDays,
single = case when numberOfRentals = 1 then rentalCost else 0 end,
bundle5 = case when numberOfRentals = 5 then rentalCost else 0 end,
bundle10 = case when numberOfRentals = 10 then rentalCost else 0 end,
bundle20 = case when numberOfRentals = 20 then rentalCost else 0 end,
singleID = case when numberOfRentals = 1 then rentalID else 0 end,
bundle5ID = case when numberOfRentals = 5 then rentalID else 0 end,
bundle10ID = case when numberOfRentals = 10 then rentalID else 0 end,
bundle20ID = case when numberOfRentals = 20 then rentalID else 0 end
from rentals
group by
numberOfDays,single,bundle5,bundle10,bun
dle20,singleID,bundle5ID,bundle10ID,
bundle20ID
and got:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'singleID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'bundle5ID'.
etc
I assumed you used the "sum" so you wouldn't have to list it in the "group"
clause (of course, I could be wrong here), as there is only 1 Rental Cost
for each NumberOfDays/NumberOfRentals.
Can I not use the title I set up in the select statement in the Group
clause?
thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7tHB7ypFHA.320@.TK2MSFTNGP09.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uXsuKCcpFHA.2904@.TK2MSFTNGP12.phx.gbl...
> The problem is that this is one we are using and there are other companies
> that will use the system that may not use the Bundles we are using so it
> would not be just one change.
> How would you use Dynamic Sql to do this?
> This will be read into a DataGrid, and it would be easy to make the
> columns visible/invisible based on the number of columns that are
> returned.
> Thanks,
> Tom
>|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O2ZAZK0pFHA.3732@.TK2MSFTNGP09.phx.gbl...
>I also tried to add in the Rental ID to select statement and can't make it
>work with the column titles. I tried using the titles from the "as
>column", but got an error in the Group clause
> I tried to change your statement to:
> select numberOfDays,
> single = case when numberOfRentals = 1 then rentalCost else 0 end,
> bundle5 = case when numberOfRentals = 5 then rentalCost else 0 end,
> bundle10 = case when numberOfRentals = 10 then rentalCost else 0 end,
> bundle20 = case when numberOfRentals = 20 then rentalCost else 0 end,
> singleID = case when numberOfRentals = 1 then rentalID else 0 end,
> bundle5ID = case when numberOfRentals = 5 then rentalID else 0 end,
> bundle10ID = case when numberOfRentals = 10 then rentalID else 0 end,
> bundle20ID = case when numberOfRentals = 20 then rentalID else 0 end
> from rentals
> group by
> numberOfDays,single,bundle5,bundle10,bun
dle20,singleID,bundle5ID,bundle10I
D,bundle20ID
> and got:
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'singleID'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'bundle5ID'.
> etc
> I assumed you used the "sum" so you wouldn't have to list it in the
> "group" clause (of course, I could be wrong here), as there is only 1
> Rental Cost for each NumberOfDays/NumberOfRentals.
I was able to get it to work using your set and the sum statement. Not sure
if this is the best way, but it does work.
select numberOfDays,
sum(case when numberOfRentals = 1 then rentalCost else 0 end) as
Individual,
sum(case when numberOfRentals = 5 then rentalCost else 0 end) as
[Bundle(5)],
sum(case when numberOfRentals = 10 then rentalCost else 0 end) as
[Bundle(10)],
sum(case when numberOfRentals = 20 then rentalCost else 0 end) as
[Bundle(20)],
sum(case when numberOfRentals = 1 then rentalID else 0 end) as
IndividualID,
sum(case when numberOfRentals = 5 then rentalID else 0 end) as
[Bundle(5)ID],
sum(case when numberOfRentals = 10 then rentalID else 0 end) as
[Bundle(10)ID],
sum(case when numberOfRentals = 20 then rentalID else 0 end) as
[Bundle(20)ID]
from rentals
group by numberOfDays
thanks,
Tom
> Can I not use the title I set up in the select statement in the Group
> clause?
> thanks,
> Tom
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:u7tHB7ypFHA.320@.TK2MSFTNGP09.phx.gbl...
>|||You don't want it to be in the group, but it has to be part of an aggregate.
Hence the sum. As long as it doesn't hurt performance this is a fine way to
do it.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OGRUGV0pFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:O2ZAZK0pFHA.3732@.TK2MSFTNGP09.phx.gbl...
> I was able to get it to work using your set and the sum statement. Not
> sure if this is the best way, but it does work.
> select numberOfDays,
> sum(case when numberOfRentals = 1 then rentalCost else 0 end) as
> Individual,
> sum(case when numberOfRentals = 5 then rentalCost else 0 end) as
> [Bundle(5)],
> sum(case when numberOfRentals = 10 then rentalCost else 0 end) as
> [Bundle(10)],
> sum(case when numberOfRentals = 20 then rentalCost else 0 end) as
> [Bundle(20)],
> sum(case when numberOfRentals = 1 then rentalID else 0 end) as
> IndividualID,
> sum(case when numberOfRentals = 5 then rentalID else 0 end) as
> [Bundle(5)ID],
> sum(case when numberOfRentals = 10 then rentalID else 0 end) as
> [Bundle(10)ID],
> sum(case when numberOfRentals = 20 then rentalID else 0 end) as
> [Bundle(20)ID]
> from rentals
> group by numberOfDays
> thanks,
> Tom
>|||Isn't always the case?
As soon as I have it set up (as you suggested), it is necessary to make it
completely flexible (could be bundles of 17, 22, 80, etc). You just can't
win.
Tom
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uEm20w0pFHA.3064@.TK2MSFTNGP15.phx.gbl...
> You don't want it to be in the group, but it has to be part of an
> aggregate. Hence the sum. As long as it doesn't hurt performance this is
> a fine way to do it.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:OGRUGV0pFHA.1464@.TK2MSFTNGP14.phx.gbl...
>