> I have a report currently, It shows everything I need it to show.
>
> Heres how it looks.  It shows what areas have completed what courses
> and how many total completions it has
>
> Area           Course ID       Total
> EAST           11111             100
> EAST            12222             110
> WEST          11111              200
> WEST           12341             50
>
> Now what I want to do is write a query to rearrange all this data to
> make it look like this
>
> CourseID     EAST   WEST
> 11111            100       200
> 12222            100       110
> 12341             0         50
>
>
> To generate the first table this is my query
>
> Select Area, CourseID, Count(CourseID) as Total
> from TotalAreaTrained
> Group by CourseID, Region
> Order by Region
>
> Real simple query.  Thanks in advance
>

"wmureports" wrote:

> I have a report currently, It shows everything I need it to show.
>
> Heres how it looks.  It shows what areas have completed what courses
> and how many total completions it has
>
> Area           Course ID       Total
> EAST           11111             100
> EAST            12222             110
> WEST          11111              200
> WEST           12341             50
>
> Now what I want to do is write a query to rearrange all this data to
> make it look like this
>
> CourseID     EAST   WEST
> 11111            100       200
> 12222            100       110
> 12341             0         50
>
>
> To generate the first table this is my query
>
> Select Area, CourseID, Count(CourseID) as Total
> from TotalAreaTrained
> Group by CourseID, Region
> Order by Region
>
> Real simple query.  Thanks in advance
>
>

> Try,
>
> select
>    CourseID,
>    isnull(max(case when Area = 'EAST' then Total end), 0) as 'EAST',
>    isnull(max(case when Area = 'WEST' then Total end), 0) as 'WEST'
> from
>    dbo.t1
> group by
>    CourseID
> go
>
> -- SQL Server 2k
> How to rotate a table in SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;175574&Product=sql
>
> -- SQL Server 2005
> Using PIVOT and UNPIVOT
> http://msdn2.microsoft.com/en-us/library/ms177410.aspx
>
>
> AMB
>
> "wmureports" wrote:
>
> > I have a report currently, It shows everything I need it to show.
> >
> > Heres how it looks.  It shows what areas have completed what courses
> > and how many total completions it has
> >
> > Area           Course ID       Total
> > EAST           11111             100
> > EAST            12222             110
> > WEST          11111              200
> > WEST           12341             50
> >
> > Now what I want to do is write a query to rearrange all this data to
> > make it look like this
> >
> > CourseID     EAST   WEST
> > 11111            100       200
> > 12222            100       110
> > 12341             0         50
> >
> >
> > To generate the first table this is my query
> >
> > Select Area, CourseID, Count(CourseID) as Total
> > from TotalAreaTrained
> > Group by CourseID, Region
> > Order by Region
> >
> > Real simple query.  Thanks in advance
> >
> >

> select
>    CourseID,
>    isnull(max(case when Area = 'EAST' then Total end), 0) as 'EAST',
>    isnull(max(case when Area = 'WEST' then Total end), 0) as 'WEST'
> from
>    dbo.t1
> group by
>    CourseID
> go
>
>
> The above query works for the two specified Areas, the problem is I
> want to remove the hardcoding where it specifically looks for East and
> West, because there are about 10 different regions besides east and
> west and new regions are always being added or removed or changed
> around.  Is there a way to modify the part where it specifically looks
> for EAST or WEST and make it just look at all the different regions
> listed in the table?
>

> wmureports wrote:
> > select
> >    CourseID,
> >    isnull(max(case when Area = 'EAST' then Total end), 0) as 'EAST',
> >    isnull(max(case when Area = 'WEST' then Total end), 0) as 'WEST'
> > from
> >    dbo.t1
> > group by
> >    CourseID
> > go
> >
> >
> > The above query works for the two specified Areas, the problem is I
> > want to remove the hardcoding where it specifically looks for East and
> > West, because there are about 10 different regions besides east and
> > west and new regions are always being added or removed or changed
> > around.  Is there a way to modify the part where it specifically looks
> > for EAST or WEST and make it just look at all the different regions
> > listed in the table?
> >
>
> Use dynamic SQL.  Build your query as a string, dynamically inserted the
> ISNULL(MAX...) portion for each region in your table.  Then execute the
> string using EXEC.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

> Ok, ill have to look for some good resources regarding dynamic sql.
> Ive never wrote queries like that before, Im still kind of new :)
>
> Thanks for the info.  Much appreciated.
>
> Tracy McKibben wrote:
> > wmureports wrote:
> > > select
> > >    CourseID,
> > >    isnull(max(case when Area = 'EAST' then Total end), 0) as 'EAST',
> > >    isnull(max(case when Area = 'WEST' then Total end), 0) as 'WEST'
> > > from
> > >    dbo.t1
> > > group by
> > >    CourseID
> > > go
> > >
> > >
> > > The above query works for the two specified Areas, the problem is I
> > > want to remove the hardcoding where it specifically looks for East and
> > > West, because there are about 10 different regions besides east and
> > > west and new regions are always being added or removed or changed
> > > around.  Is there a way to modify the part where it specifically looks
> > > for EAST or WEST and make it just look at all the different regions
> > > listed in the table?
> > >
> >
> > Use dynamic SQL.  Build your query as a string, dynamically inserted the
> > ISNULL(MAX...) portion for each region in your table.  Then execute the
> > string using EXEC.
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com

"Tracy McKibben" <tr***@realsqlguy.com> wrote in message
news:uyv3mLppGHA.4424@TK2MSFTNGP05.phx.gbl...
> Steve Dassin wrote:
>> "wmureports" <justin.a.mo***@gmail.com> wrote in message
>> news:1152803166.424129.170110@m73g2000cwd.googlegroups.com...
>>> woah, im reading about this dynamic sql.  Way over my head :(
>>
>> There's no reason to loose your head:) You can forget about dynamic sql
>> and all the rest of the pleasant experience of coding these kinds of
>> problems.Check out the RAC utility.It will give you your crosstab
>> using whatever data is available.Its simple and powerful.All you have
>> to do is to be able to spell:)
>>
>> www.rac4sql.net
>>
>>
>
> Yes, but then you're locked into a third-party solution, and haven't
> learned a new skill to work yourself out of a jam...

> "Tracy McKibben" <tr***@realsqlguy.com> wrote in message
> news:uyv3mLppGHA.4424@TK2MSFTNGP05.phx.gbl...
>> Steve Dassin wrote:
>>> "wmureports" <justin.a.mo***@gmail.com> wrote in message
>>> news:1152803166.424129.170110@m73g2000cwd.googlegroups.com...
>>>> woah, im reading about this dynamic sql.  Way over my head :(
>>> There's no reason to loose your head:) You can forget about dynamic sql
>>> and all the rest of the pleasant experience of coding these kinds of
>>> problems.Check out the RAC utility.It will give you your crosstab
>>> using whatever data is available.Its simple and powerful.All you have
>>> to do is to be able to spell:)
>>>
>>> www.rac4sql.net
>>>
>>>
>> Yes, but then you're locked into a third-party solution, and haven't
>> learned a new skill to work yourself out of a jam...
>
> There are no free lunches.But we don't underestimate RAC and we don't
> overestimate the need for new skills:) Besides where would we be if
> your logic was applied across the board??:P :)
>
> Your friendly marketing rep from:
> www.rac4sql.net
>
>

"wmureports" wrote:

> woah, im reading about this dynamic sql.  Way over my head :(
>
>
>
> wmureports wrote:
> > Ok, ill have to look for some good resources regarding dynamic sql.
> > Ive never wrote queries like that before, Im still kind of new :)
> >
> > Thanks for the info.  Much appreciated.
> >
> > Tracy McKibben wrote:
> > > wmureports wrote:
> > > > select
> > > >    CourseID,
> > > >    isnull(max(case when Area = 'EAST' then Total end), 0) as 'EAST',
> > > >    isnull(max(case when Area = 'WEST' then Total end), 0) as 'WEST'
> > > > from
> > > >    dbo.t1
> > > > group by
> > > >    CourseID
> > > > go
> > > >
> > > >
> > > > The above query works for the two specified Areas, the problem is I
> > > > want to remove the hardcoding where it specifically looks for East and
> > > > West, because there are about 10 different regions besides east and
> > > > west and new regions are always being added or removed or changed
> > > > around.  Is there a way to modify the part where it specifically looks
> > > > for EAST or WEST and make it just look at all the different regions
> > > > listed in the table?
> > > >
> > >
> > > Use dynamic SQL.  Build your query as a string, dynamically inserted the
> > > ISNULL(MAX...) portion for each region in your table.  Then execute the
> > > string using EXEC.
> > >
> > >
> > > --
> > > Tracy McKibben
> > > MCDBA
> > > http://www.realsqlguy.com
>
>