August 25, 2007 at 4:40 am
pls help me in constructing a query for the following data
I am having a table with only one field
Say table name is Test and field name is Type
and i an having following data in the table
Type
A
B
B
B
C
A
Now i want result by query as
A
August 25, 2007 at 2:09 pm
I'm not 100 per cent sure what you are trying to achieve, but a query like:
select top 1 Type from Test
or
select top 1 Type from Test order by Type
would get the result you asked for.
Regards,
Andras
August 25, 2007 at 3:42 pm
select min(type) from test?
N 56°04'39.16"
E 12°55'05.25"
August 26, 2007 at 12:06 am
What i want is count of A,B and C in columns
ie Result should be like this
A B C
1 2 1
August 26, 2007 at 12:07 am
What i want is count of A,B and C in columns
ie Result should be like this
A B C
1 2 1
August 26, 2007 at 12:10 am
What i want is count of similar datas in columns ie i my sample data A is twice so i want column heading as A and data as 2 similarly for B column heading as B and data as 3. this is what i want to achieve ie i want a cross tab query which will display data as columns
August 26, 2007 at 1:42 am
You could achieve this with pivots like:
SELECT [A], , [C] FROM ( SELECT [Type] FROM [Test]) AS SourceTable PIVOT (COUNT([Type]) FOR Type IN ([A], , [C])) AS PivotTable
Note that the columns A, B, C are fixed, if you need to specify them dynamically, you will need to construct the query using dynamic SQL.
Regards,
Andras
August 26, 2007 at 9:19 pm
Hi there...
You can use the following script to achieve what you are trying to. While this may not be the most sophisticated way but it's dynamic and it works. I used your example scenario to create this script. You may actually find that there is scope of improving this script. I just thought this might give you a start.
Cheers,
Shalini
create
table sm (sm_type char(1))
insert
into sm values ('A')
insert
into sm values ('B')
insert
into sm values ('B')
insert
into sm values ('B')
insert
into sm values ('C')
insert
into sm values ('A')
--select * from sm
--drop table #results, sm1, #results1
--SELECT DISTINCT sm_type from SM
select
sm_type, count(*) ItemCount
into
#Results
from
sm
group
by sm_type
declare
@sql_string varchar(100);
SET
@SQL_String = 'CREATE TABLE SM1 ('
DECLARE
@COlumnName char(1)
SELECT
TOP 1 @ColumnName = SM_TYPE FROM #Results
while
@@ROWCOUNT <> 0
BEGIN
SET @sql_string = @sql_string + @ColumnName + ' Integer, '
DELETE FROM #Results WHERE sm_type = @ColumnName
SELECT TOP 1 @ColumnName = SM_TYPE FROM #Results
END
SET
@sql_string = LEFT(@SQL_String, LEN(@sql_string) - 1) + ')'
exec
(@sql_string)
--Print @sql_string
select
sm_type, count(*) ItemCount
into
#Results1
from
sm
group
by sm_type
--select * from sm1
SELECT
TOP 1 @ColumnName = SM_TYPE FROM #Results1
set
@sql_string =
'insert into sm1 ('
+@COlumnName+')
select ItemCount
from #results1 sm
where sm.sm_type = '''
+@columnName+''''
--print @sql_string
exec(@sql_string)
--select * from sm1
delete
from #results1 where sm_type = @ColumnName
SELECT
TOP 1 @ColumnName = SM_TYPE FROM #Results1
while
@@ROWCOUNT <> 0
begin
set @sql_string = 'update sm1 set ' +@columnName + ' = ItemCount FROM #results1 WHERE sm_type = ''' + @ColumnName + ''''
--print @sql_string
exec(@sql_string)
delete
from #results1 where sm_type = @ColumnName
SELECT
TOP 1 @ColumnName = SM_TYPE FROM #Results1
end
select
* from sm1
/* Result
select * from sm1
A B C
2 3 1
*/
September 3, 2007 at 10:55 pm
Select
SUM(Case when field1='A' then 1 else 0) A,
SUM(Case when field1='A' then 1 else 0) B,
SUM(Case when field1='A' then 1 else 0) C
from table1
August 7, 2008 at 4:17 am
This could be simpler as mention below.
SELECT sm_type, count(sm_type) FROM sm group by sm_type
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply