Creating select statement with seperate columns for different valu

I've been trying to figure out a select statement that would list a seperate
column with the count of a particular value for each value. Basically if I
have a table like this:
number value
-- --
10 good
10 bad
10 bad
12 good
14 bad
16 better
and I want to return all numbers with good or bad values and the totals for
those values, like this:
number good bad
-- -- --
10 1 2
12 1 0
14 0 1
How would I create the query?The best way to query this would be to use:
select num, count(value), value from checkcount
group by num, value
your result would be:
10 2 bad
14 1 bad
16 1 better
10 1 good
12 1 good
then you would want to create a user interface to format your result shown
in your example.
However, if you wanted SQL to bring your result back formatted as your
example, then you would want to inner join your table. hopefully, it is not
a large table. The following query would bring back your desired formatting:
select checkcount.num, isnull( thegood.good ,0) good, isnull( thebad.bad ,0)
bad from checkcount
left join (select num, count(value)as good from checkcount
where value = 'good'
group by num) theGood
on checkcount.num = thegood.num
left join (select num, count(value)as bad from checkcount
where value = 'bad'
group by num) thebad
on checkcount.num = thebad.num
where value in ('good','bad')
group by checkcount.num,thegood.good,thebad.bad
Thanks Kllyj64
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||If you're lucky enough to have SQL Server 2005, try the new PIVOT operator:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( number INT, xvalue VARCHAR(10) )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 10, 'good' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 12, 'good' )
INSERT INTO #tmp VALUES ( 14, 'bad' )
INSERT INTO #tmp VALUES ( 16, 'better' )
SET NOCOUNT OFF
SELECT *
FROM #tmp AS t
PIVOT
(
COUNT(xvalue) FOR xvalue In ( [good], [bad], [better] )
) AS x
That is my first PIVOT query! That's going to be useful!
Let me know how you get on.
Damien
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||Try,
select
number,
sum(case when value = 'good' then 1 else 0 end) as good,
sum(case when value = 'bad' then 1 else 0 end) as bad
from
t1
group by
number
go
How to rotate a table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
For SQL Server 2005, see PIVOT operator.
AMB
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?

creating reports on the fly...

Hi all
Hoping someone can help me out. I'm new to reporting services and cannot
quite seem to figure out what steps to take with the following.
1. Create reports on the fly... one of the problems I have is that I would
like to alter the rdl file before it is displayed to the user so I can
change things in it like the points below. The way I see it happening is
that I extract the rdl from the reports server... make the changes to the
file and then render this as html to display to the user.
Can anyone tell me if this would be the correct process and how to
accomplish this.
The following 2 issues would be obsolete if I can create reports on the run.
2. Alter the type of graph by passing a parameter to the report in which I
can specify whether it is ie: pie/donut/line etc.
3. I would like for my reports to be able to take an input parameter that is
not viewable on the report and is to be able to be dynamic.
Thanks
Kez.You'd have to get the RDL, change it and re-post it, then call the report as
you have said...
Regarding the bottom parameter item , you can make a parameter invisible by
simply not giving the label attribute a value... THe param can be passed in
via URL etc, but the report does not display it with the other parameters.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
> Hi all
> Hoping someone can help me out. I'm new to reporting services and cannot
> quite seem to figure out what steps to take with the following.
> 1. Create reports on the fly... one of the problems I have is that I would
> like to alter the rdl file before it is displayed to the user so I can
> change things in it like the points below. The way I see it happening is
> that I extract the rdl from the reports server... make the changes to the
> file and then render this as html to display to the user.
> Can anyone tell me if this would be the correct process and how to
> accomplish this.
>
> The following 2 issues would be obsolete if I can create reports on the
run.
> 2. Alter the type of graph by passing a parameter to the report in which
I
> can specify whether it is ie: pie/donut/line etc.
> 3. I would like for my reports to be able to take an input parameter that
is
> not viewable on the report and is to be able to be dynamic.
> Thanks
> Kez.
>|||Thanks for the feedback.
When you refer to re-posting the rdl I imagine you mean publishing it back
to the reports server. This is okay but multiple users will be accessing
the report. I only want the report rdl file to be changed for that
instance, not as a permanant thing.
I was also hoping for some direction in achieving this.
Thanks
Kez.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> You'd have to get the RDL, change it and re-post it, then call the report
> as
> you have said...
> Regarding the bottom parameter item , you can make a parameter invisible
> by
> simply not giving the label attribute a value... THe param can be passed
> in
> via URL etc, but the report does not display it with the other parameters.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
>> Hi all
>> Hoping someone can help me out. I'm new to reporting services and cannot
>> quite seem to figure out what steps to take with the following.
>> 1. Create reports on the fly... one of the problems I have is that I
>> would
>> like to alter the rdl file before it is displayed to the user so I can
>> change things in it like the points below. The way I see it happening is
>> that I extract the rdl from the reports server... make the changes to the
>> file and then render this as html to display to the user.
>> Can anyone tell me if this would be the correct process and how to
>> accomplish this.
>>
>> The following 2 issues would be obsolete if I can create reports on the
> run.
>> 2. Alter the type of graph by passing a parameter to the report in which
> I
>> can specify whether it is ie: pie/donut/line etc.
>> 3. I would like for my reports to be able to take an input parameter that
> is
>> not viewable on the report and is to be able to be dynamic.
>> Thanks
>> Kez.
>>
>|||Regarding the parameters... if I remove the prompt label... I get an error
and cannot figure it out.
The error... The properties for the currently selected item are not valid.
Please correct all errors before continuing.
Thanks again
Kez.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> You'd have to get the RDL, change it and re-post it, then call the report
> as
> you have said...
> Regarding the bottom parameter item , you can make a parameter invisible
> by
> simply not giving the label attribute a value... THe param can be passed
> in
> via URL etc, but the report does not display it with the other parameters.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
>> Hi all
>> Hoping someone can help me out. I'm new to reporting services and cannot
>> quite seem to figure out what steps to take with the following.
>> 1. Create reports on the fly... one of the problems I have is that I
>> would
>> like to alter the rdl file before it is displayed to the user so I can
>> change things in it like the points below. The way I see it happening is
>> that I extract the rdl from the reports server... make the changes to the
>> file and then render this as html to display to the user.
>> Can anyone tell me if this would be the correct process and how to
>> accomplish this.
>>
>> The following 2 issues would be obsolete if I can create reports on the
> run.
>> 2. Alter the type of graph by passing a parameter to the report in which
> I
>> can specify whether it is ie: pie/donut/line etc.
>> 3. I would like for my reports to be able to take an input parameter that
> is
>> not viewable on the report and is to be able to be dynamic.
>> Thanks
>> Kez.
>>
>|||If i understand correctly, i am doing a similar thing with a custom report
application. Basically, I have 'report template' rdl files on the server
which have no report data in them. I also have a class that collects all the
users choices in the client app. I use GetReportDefinition to get the
template rdl as xml, then use Xpath to add the custom data that the user has
chosen.
Once the new xml file is built, it is published to the server as a new
report, which the user can run.
that's a simplified description, but I hope it helps!
"Kez Bates" wrote:
> Thanks for the feedback.
> When you refer to re-posting the rdl I imagine you mean publishing it back
> to the reports server. This is okay but multiple users will be accessing
> the report. I only want the report rdl file to be changed for that
> instance, not as a permanant thing.
> I was also hoping for some direction in achieving this.
> Thanks
> Kez.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> > You'd have to get the RDL, change it and re-post it, then call the report
> > as
> > you have said...
> >
> > Regarding the bottom parameter item , you can make a parameter invisible
> > by
> > simply not giving the label attribute a value... THe param can be passed
> > in
> > via URL etc, but the report does not display it with the other parameters.
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> > news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
> >> Hi all
> >> Hoping someone can help me out. I'm new to reporting services and cannot
> >> quite seem to figure out what steps to take with the following.
> >>
> >> 1. Create reports on the fly... one of the problems I have is that I
> >> would
> >> like to alter the rdl file before it is displayed to the user so I can
> >> change things in it like the points below. The way I see it happening is
> >> that I extract the rdl from the reports server... make the changes to the
> >> file and then render this as html to display to the user.
> >> Can anyone tell me if this would be the correct process and how to
> >> accomplish this.
> >>
> >>
> >> The following 2 issues would be obsolete if I can create reports on the
> > run.
> >> 2. Alter the type of graph by passing a parameter to the report in which
> > I
> >> can specify whether it is ie: pie/donut/line etc.
> >>
> >> 3. I would like for my reports to be able to take an input parameter that
> > is
> >> not viewable on the report and is to be able to be dynamic.
> >>
> >> Thanks
> >> Kez.
> >>
> >>
> >
> >
>
>|||You can use the RDL generator for a ASP.NET or Winform to generate the RDL on
the fly no XML, no VS, no Xpath
http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
"mark-s" wrote:
> If i understand correctly, i am doing a similar thing with a custom report
> application. Basically, I have 'report template' rdl files on the server
> which have no report data in them. I also have a class that collects all the
> users choices in the client app. I use GetReportDefinition to get the
> template rdl as xml, then use Xpath to add the custom data that the user has
> chosen.
> Once the new xml file is built, it is published to the server as a new
> report, which the user can run.
> that's a simplified description, but I hope it helps!
> "Kez Bates" wrote:
> > Thanks for the feedback.
> > When you refer to re-posting the rdl I imagine you mean publishing it back
> > to the reports server. This is okay but multiple users will be accessing
> > the report. I only want the report rdl file to be changed for that
> > instance, not as a permanant thing.
> >
> > I was also hoping for some direction in achieving this.
> >
> > Thanks
> > Kez.
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> > > You'd have to get the RDL, change it and re-post it, then call the report
> > > as
> > > you have said...
> > >
> > > Regarding the bottom parameter item , you can make a parameter invisible
> > > by
> > > simply not giving the label attribute a value... THe param can be passed
> > > in
> > > via URL etc, but the report does not display it with the other parameters.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> > > news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
> > >> Hi all
> > >> Hoping someone can help me out. I'm new to reporting services and cannot
> > >> quite seem to figure out what steps to take with the following.
> > >>
> > >> 1. Create reports on the fly... one of the problems I have is that I
> > >> would
> > >> like to alter the rdl file before it is displayed to the user so I can
> > >> change things in it like the points below. The way I see it happening is
> > >> that I extract the rdl from the reports server... make the changes to the
> > >> file and then render this as html to display to the user.
> > >> Can anyone tell me if this would be the correct process and how to
> > >> accomplish this.
> > >>
> > >>
> > >> The following 2 issues would be obsolete if I can create reports on the
> > > run.
> > >> 2. Alter the type of graph by passing a parameter to the report in which
> > > I
> > >> can specify whether it is ie: pie/donut/line etc.
> > >>
> > >> 3. I would like for my reports to be able to take an input parameter that
> > > is
> > >> not viewable on the report and is to be able to be dynamic.
> > >>
> > >> Thanks
> > >> Kez.
> > >>
> > >>
> > >
> > >
> >
> >
> >|||Hi,
I would also suggest you to use ReportPortal for creating MS Reporting
Services reports on the fly as web intranet solution. Great and
productive !
You can order a free 60 days trial or even visit the demo report
portal site on internet and test it online.
Regards,
Marco Groeneveld
www.gmsbv.nl / www.reportportal.com
"=?Utf-8?B?SmVycnk=?=" <Jerry@.discussions.microsoft.com> wrote in message news:<C76C5CD6-439A-4E6A-883E-04478C9DB0A5@.microsoft.com>...
> You can use the RDL generator for a ASP.NET or Winform to generate the RDL on
> the fly no XML, no VS, no Xpath
> http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> "mark-s" wrote:
> > If i understand correctly, i am doing a similar thing with a custom report
> > application. Basically, I have 'report template' rdl files on the server
> > which have no report data in them. I also have a class that collects all the
> > users choices in the client app. I use GetReportDefinition to get the
> > template rdl as xml, then use Xpath to add the custom data that the user has
> > chosen.
> > Once the new xml file is built, it is published to the server as a new
> > report, which the user can run.
> >
> > that's a simplified description, but I hope it helps!
> >
> > "Kez Bates" wrote:
> >
> > > Thanks for the feedback.
> > > When you refer to re-posting the rdl I imagine you mean publishing it back
> > > to the reports server. This is okay but multiple users will be accessing
> > > the report. I only want the report rdl file to be changed for that
> > > instance, not as a permanant thing.
> > >
> > > I was also hoping for some direction in achieving this.
> > >
> > > Thanks
> > > Kez.
> > >
> > >
> > >
> > > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > > news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> > > > You'd have to get the RDL, change it and re-post it, then call the report
> > > > as
> > > > you have said...
> > > >
> > > > Regarding the bottom parameter item , you can make a parameter invisible
> > > > by
> > > > simply not giving the label attribute a value... THe param can be passed
> > > > in
> > > > via URL etc, but the report does not display it with the other parameters.
> > > >
> > > > --
> > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > Mariner, Charlotte, NC
> > > > www.mariner-usa.com
> > > > (Please respond only to the newsgroups.)
> > > >
> > > > I support the Professional Association of SQL Server (PASS) and it's
> > > > community of SQL Server professionals.
> > > > www.sqlpass.org
> > > >
> > > > "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> > > > news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
> > > >> Hi all
> > > >> Hoping someone can help me out. I'm new to reporting services and cannot
> > > >> quite seem to figure out what steps to take with the following.
> > > >>
> > > >> 1. Create reports on the fly... one of the problems I have is that I
> > > >> would
> > > >> like to alter the rdl file before it is displayed to the user so I can
> > > >> change things in it like the points below. The way I see it happening is
> > > >> that I extract the rdl from the reports server... make the changes to the
> > > >> file and then render this as html to display to the user.
> > > >> Can anyone tell me if this would be the correct process and how to
> > > >> accomplish this.
> > > >>
> > > >>
> > > >> The following 2 issues would be obsolete if I can create reports on the
> > > > run.
> > > >> 2. Alter the type of graph by passing a parameter to the report in which
> > > > I
> > > >> can specify whether it is ie: pie/donut/line etc.
> > > >>
> > > >> 3. I would like for my reports to be able to take an input parameter that
> > > > is
> > > >> not viewable on the report and is to be able to be dynamic.
> > > >>
> > > >> Thanks
> > > >> Kez.
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >