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

Top 5 Contributors of the Month

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

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

pls elaborate u r qestion properly...


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


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

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


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   


Try this...!

;With CTEs
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]


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

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