Cross Apply, Help me ?

Question

Cross Apply, Help me ?

nguyennd

SSCommitted

Points: 1881
More actions
March 5, 2010 at 2:52 am

#219640

create table A(A1 int)
insert into A(A1)values(1)
insert into A(A1)values(2)
insert into A(A1)values(3)
I have a variabe -> @i
IF @i = 3 then I want result of query is:
1
2
3
1
2
3
1
2
3
IF @i = 2 then I want result of query is:
1
2
3
1
2
3
IF @i = 1 then I want result of query is:
1
2
3
I have a solution:
declare @a int = 10
select A.* from
A
cross apply
(
select top ( @a ) sys.columns.column_id from sys.columns
) t
So i think it's not good. Pls help me. Thanks
http://unitech.vn

Viewing 9 posts - 1 through 8 (of 8 total)

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

Apart from the order of the result, I don't see what's wrong with your own solution. If you want the result in the order you specified in your example try this:

declare @i int = 4

select

a.a1

from

a

cross apply

(

select Number from master..spt_values where Type = 'P' and Number < @i

) x

order by

x.Number, a.a1

nguyennd SSCommitted Points: 1881 More actions · Answer 2

nguyennd

SSCommitted

Points: 1881

March 5, 2010 at 3:44 am

#1129064

Thanks Peter Brinkhaus.

http://unitech.vn

nguyennd SSCommitted Points: 1881 More actions · Answer 3

thanks Peter Brinkhaus. so it's will wrong if @i > 2100

http://unitech.vn

matzer SSCrazy Eights Points: 9727 More actions · Answer 4

Yep, actually it goes wrong when @i > 2048. spt_values contains numbers from 0 to 2047. If @i can be greater than that in your situation then you have to create your own number (or tally) table (either on the fly or persistent). Just do a search on tally table at this site, it contains tons of examples.

Peter

matzer SSCrazy Eights Points: 9727 More actions · Answer 5

Here's an example with @i <= 65536

declare @i int = 65536;

with

Tally4(N) as (select 1 union all select 1 union all select 1 union all select 1) ,

Tally16(N) as (select 1 from Tally4 t1 cross join Tally4 t2),

Tally256(N) as (select 1 from Tally16 t1 cross join Tally16 t2),

Tally65536(N) as (select 1 from Tally256 t1 cross join Tally256 t2),

Tally(Number) as (select top (@i) ROW_NUMBER() over (order by (select 0)) Number from Tally65536)

select

a.a1

from

a

cross apply

(

select Number from Tally

) x

order by

x.Number, a.a1

Peter

nguyennd SSCommitted Points: 1881 More actions · Answer 6

Ah, Tally table. Thanks Peter Brinkhaus.

http://unitech.vn

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

To learn more about how a Tally table can be used as a set based replacement for certain While Loops, please see the following article...

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 8

If, for some reason, a persistent Numbers table is not an option, you can generate one as required using an in-line table function:

CREATE FUNCTION dbo.GetNumbers

(@n AS BIGINT)

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

WITH

L0 (n) AS (SELECT 1 UNION ALL SELECT 1),

L1 (n) AS (SELECT 1 FROM L0 A, L0 B),

L2 (n) AS (SELECT 1 FROM L1 A, L1 B),

L3 (n) AS (SELECT 1 FROM L2 A, L2 B),

L4 (n) AS (SELECT 1 FROM L3 A, L3 B),

L5 (n) AS (SELECT 1 FROM L4 A ,L4 B),

Num (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM L5)

SELECT TOP (@n)

n

FROM Num

ORDER BY n;

Example usage:

SELECT Numbers.n

FROM dbo.GetNumbers (5000) Numbers;

Just an alternative.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi