I have table with values, described as:

select top(4) Name_Country From DBO.Country

I need values in pivot format as:

Can anyone help on this?

Thanks in advance

CodePudding user response：

You can use a subquery to generate a ROW_NUMBER() using the table insert order (ORDER BY (SELECT 1)), however, you should ORDER BY an actual column in your table. Your table should have a key you can use for this step.

Add more NAME_x row number columns to include more pivoted results:

SELECT 
         MAX(CASE WHEN a.rn = 1 THEN a.Name_Country END) AS NAME_1,
         MAX(CASE WHEN a.rn = 2 THEN a.Name_Country END) AS NAME_2,
         MAX(CASE WHEN a.rn = 3 THEN a.Name_Country END) AS NAME_3,
         MAX(CASE WHEN a.rn = 4 THEN a.Name_Country END) AS NAME_4
    FROM (SELECT b.Name_Country,
                 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn
            FROM Country b) a

Result

Fiddle here.

CodePudding user response：

select [1] as NAME_1
      ,[2] as NAME_2
      ,[3] as NAME_3
      ,[4] as NAME_4
from  (
       select *
              ,row_number() over(order by Name_Country desc) as rn
       from   t
       ) t 
       pivot(max(Name_Country) for rn in([1],[2],[3],[4])) p

Fiddle