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 query to search for a particular symbol

Hey Everyone,

I am new to SQL and need help.

I need to search an entire database called Moodle for a (ASCII) character " ? ". I wish to list every field / table that has this.

Then later on I wish to replace this symbol with an another UTF-8 symbol.

Is this possible? happy to post more information

Cheers,

Mark

Yes You can do it from SQL Server.

But I really worried about the performance, I recommand to do this task from the UI Applications.

Where you can easily built the quries & execute the dynamic quires on the db..

It might be simpler than what you try to achive at T-SQL..

|||

Hey,

Thanks for your reply.

Thats my problem I cant create queries from the application UI. We use Moodle which is a learning management system and its backend is SQL 2005. We migrated from WebCT which uses us-ASCII for encoding and Moodle uses UTF-8 which means some symbols dont work when we backup courses in Moodle which is in XML format.

I was thinking that updating the symbol via SQL would be easier. But I just wish to CREATE VIEW for symbol this database. Can this be done? and what would be the query syntax be?

Cheers,

Mark

|||

This query may help you...

Code Snippet

Createtable #Columns

(

TableNameNvarchar(1000)

,ColumnnameNVarchar(1000)

)

Execsp_msForEachtable'Insert Into #Columns

Select ''?'', name

From Syscolumns Where Xtype in (175,239,231,167)

And Id = Object_id(''?'')'

Droptable #QueryTable

Select

TableName,

'Select @.C = Count(*) From '+ TableName+' Where 1=0 '+

(Select' Or ['+ Columnname+'] Like ''%?%'''as [text()]From #Columnsas SubWhere Sub.TableName=Main.TableNameForXMLPath(''),ELEMENTS)as Query

,Row_Number()Over(OrderBy TableNameDesc) RowId1

,Row_Number()Over(OrderBy TableNameAsc) RowId2

Into #QueryTable

from

(Select TableNameFrom #ColumnsGroupBy TableName)as Main

Declare @.IInt

Select @.I= RowId1From #QueryTableWhere RowId2=1;

While @.I>0

begin

Declare @.QueryasNVarchar(max);

Declare @.TableasNVarchar(Max);

Declare @.Casint

Select

@.Query= Query,

@.Table= TableName

From

#QueryTable

Where

RowId1= @.I;

Execsp_executesql @.Query,N'@.C as Int OUTPUT',@.COUTPUT;

If @.C<> 0

Begin

Print 'Found at ' + TableName;

Select @.Query =

'Update ' + TableName + ' Set ' +

Substring((Select ',[' + Columnname + '] = Replace([' + Columnname + '],''?'',''' + Char(8) + ''') ' as [text()] From #Columns as Sub Where Sub.TableName=Main.TableName For XML Path(''), ELEMENTS),2,8000)

from

(Select TableName From #Columns Where TableName = @.Table Group By TableName) main

Exec (@.Query);

Print 'Values are replaced at ' + TableName

End

Select @.I= @.I-1;

End