.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Need a query

Posted By: ramya     Posted Date: April 15, 2011    Points:5   Category :Sql Server
I had a table like

FormCode score
3r 4
t5 6
y6 7
t5 4
r5 4


I want to display like as follows

S.No Score
0 0
1 0
2 0
3 0
4 3
5 0
6 1
7 1
8 0

Responses
Author: Mahesh             
Posted Date: April 17, 2011     Points: 20   

hi,
pls elaborate u r qestion properly...

Regards
Mahesh

Author: shankey             
Posted Date: April 18, 2011     Points: 20   

hi,

have a look at the following query which is based on sample temp table, these are created just to have the same scenario, you can replace #tb1 table with your own but you will need #tb2 in order to generate expected result.

---Temp table created just to hold your values------------
create table #tb1(FormCode varchar(max),score int)
insert into #tb1(FormCode,score)
select '3r',4
union all
select 't5', 6
union all
select 'y6', 7
union all
select 't5', 4
union all
select 'r5', 4

---------------------Temp table created this is mandatory to run the query--------------------------------
create table #tb2(score int)
insert into #tb2(score)
select 0
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8

Your query which will generate expected result specified by you
select #tb2.score, case when #tb1.score is null then 0 else count(*) end from #tb2
left join #tb1 on #tb1.score = #tb2.score
group by #tb2.score,#tb1.score
order by #tb2.score asc


Ask me for any doubt on this,

please reply if it was helpful


Shankey
Author: Senthilnathan             
Posted Date: April 19, 2011     Points: 20   

HI,

Try this,


declare @table(sno numeric(38,0) identity(0,1),score varchar(20))

insert into @table select score from table_name

Select * from @table

Author: Pandian             
Posted Date: April 22, 2011     Points: 20   

Hi

Try this...!

;With CTEs
as
(
select 0 [S.No]
Union All
select [S.No] +1 from CTEs where [S.No] <8
)

select c.[S.No],count(o.score) [Score] from CTEs c Left Join TableName o on (c.[S.No] = o.score)
group by c.[S.No]

Cheers

If not I then who, If not now then when


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend