CODEWITHSUNDEEP
sql-server-2008
sam
sam

Reputation: 1304

how to sort row_number() in dynamic sql in sql server 2008

I have to create a Pivot from the below given sample table -

State             Country
------------------------------
ALA Almaty        Kazakhstan 
AMD Ahmedabad     India
AMM Amman         Jordan
AMS Amsterdam     Netherlands
ATH Athens        Greece
AUH Abu Dhabi     United Arab Emirates
BAH Manama        Bahrain

I am doing this by dynamic sql as i want dynamic columns in pivot. My problem is that I am not able to sort values generated by ROW_NUMBER() in dynamic SQL. The variable @var1 in the below mentioned code is giving output as - 

 [1],[10],[11],[2],[3],[4],[5],[6],[7],[8],[9]

which is not in sorted order.

declare @myvar nvarchar(max)
declare @var1 nvarchar(max)
declare @new nvarchar(max)

set @var1 = (select stuff((select distinct '],[' + cast(ROW_NUMBER() over (     partition by country order by (select 1)) as varchar(100)) from sheet 
for xml path('')),1,2,'') + ']')
select @var1

set @myvar = 

'select * from 
(select *,ROW_NUMBER() over (partition by country order by (select 1)) as rn
from sheet
)as abc
pivot
(
max([state]) for rn
in (' + @var1 + ')
) as pvt'

set @new = 'select * into ##a from (' + @myvar + ') as t'

exec sp_executesql @new

Upvotes: 0

Views: 351

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You can try this:

WITH    cte
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY country ORDER BY ( SELECT 1 ) ) rn
               FROM     sheet
             )
    SELECT  @var1 = STUFF(( SELECT  '],[' + CAST(rn AS NVARCHAR(MAX))
                            FROM    cte
                            GROUP BY rn
                            ORDER BY rn
                          FOR XML PATH('') ), 1, 2, '') + ']'
SELECT  @var1

For test data it works:

DECLARE @t TABLE ( i INT )
DECLARE @var1 NVARCHAR(MAX)


INSERT  INTO @t
VALUES  ( 1 ),
        ( 1 ),
        ( 2 ),
        ( 2 ),
        ( 2 ),
        ( 3 ),
        ( 3 ),
        ( 3 ),
        ( 3 ),
        ( 3 ),
        ( 3 ),
        ( 3 ),
        ( 3 ),
        ( 3 ),
        ( 3 ),
        ( 3 ),
        ( 4 ),
        ( 4 );

WITH    cte
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY i ORDER BY ( SELECT 1 ) ) rn
               FROM     @t
             )
    SELECT  @var1 = STUFF(( SELECT  '],[' + CAST(rn AS NVARCHAR(MAX))
                            FROM    cte
                            GROUP BY rn
                            ORDER BY rn
                          FOR XML PATH('') ), 1, 2, '') + ']'
SELECT  @var1

Output:

[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11]

Upvotes: 1

Related Questions