"tshad" <tscheider***@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

>
> --
> --------------------------------------------------------------------------
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
>
> "tshad" <tscheider***@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

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

"tshad" <t**@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...
>> 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" <tscheider***@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 - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
>
> "tshad" <t**@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...
>>> 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" <tscheider***@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
>>> >
>>> >
>>>
>>>
>>
>>
>
>

"tshad" <tscheider***@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 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" <t**@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...
>>>> 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" <tscheider***@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
>>>> >
>>>> >
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"tshad" <tscheider***@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,bundle20,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" <tscheider***@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 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" <t**@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...
>>>>> 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" <tscheider***@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
>>>>> >
>>>>> >
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"tshad" <tscheider***@ftsolutions.com> wrote in message
news:OGRUGV0pFHA.1464@TK2MSFTNGP14.phx.gbl...
> "tshad" <tscheider***@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,bundle20,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.
>
> 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" <tscheider***@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 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" <t**@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...
>>>>>> 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" <tscheider***@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: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" <tscheider***@ftsolutions.com> wrote in message
> news:OGRUGV0pFHA.1464@TK2MSFTNGP14.phx.gbl...
>> "tshad" <tscheider***@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,bundle20,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.
>>
>> 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" <tscheider***@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 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" <t**@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...
>>>>>>> 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" <tscheider***@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
>>>>>>> >
>>>>>>> >
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>