Single SELECT

Question

Post reply

Single SELECT

Bhuvnesh

SSC Guru

Points: 59351
More actions
November 8, 2010 at 6:34 am

#97527

declare @t table
(
id int ,
Class varchar(20)
)
insert into @t
select 1, 'ADS' union
select 2, 'ggS' union
select 3, 'dS' union
select 4 , 'hhf'
--Output
1ADS4
2ggS4
3dS4
4hhf4
i need above output with SINGLE "select" keywords
cant use double "select" like below queries select t.id, t.class, (select count(id) counts from @t) tcount
from @t t
;
with cte(counts )
as
(
select count(id) counts from @t
)
select t.id, t.class, cte.counts
from @t t
cross join
cte
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Something like this ?

declare @t table

(

id int ,

Class varchar(20)

)

insert into @t

select 1, 'ADS' union

select 2, 'ggS' union

select 3, 'dS' union

select 4 , 'hhf'

select *,count(*) over (partition by 1) from @t

Clear Sky SQL
My Blog[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

thanks dave , but some times i get error that i need to use "order by" with "partition by" , Any idea ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

How

count(*) over (partition by 1)

section is working here ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

Can you be any more specific than "some times" ?

All the aggregate functions now support the partition by clause , in this case we dont want to partition so i have hardcoded the partitioning value to be 1.

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Bhuvnesh (11/8/2010)
i need above output with SINGLE "select" keywords
cant use double "select" like below queries

Why? What's the reasoning behind that odd limitation/restriction?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

GilaMonster (11/8/2010)

Why? What's the reasoning behind that odd limitation/restriction?

This was asked by one of my friend during a quiz

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)