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