Pivot String values on column

Question

I have table with values, described as:

select top(4) Name_Country From DBO.Country

Name_Country
USA
ENGLAND
ITALY
GERMANY

I need values in pivot format as:

NAME_1	NAME_2	NAME_3	NAME_4
USA	ENGLAND	ITALY	GERMANY

Can anyone help on this?

Thanks in advance

then please show us what you tried the highest answer is exactly what you need — nbk, Sep 16 '22 at 23:40
"When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order." (see" [docs for TOP](https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver16)) — Luuk, Sep 17 '22 at 13:12

griv · Accepted Answer · 2022-09-17T02:11:00.607

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

NAME_1	NAME_2	NAME_3	NAME_4
USA	ENGLAND	ITALY	GERMANY

Fiddle here.

score 1 · Answer 2 · answered Sep 17 '22 at 13:08

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

NAME_1	NAME_2	NAME_3	NAME_4
USA	ITALY	GERMANY	ENGLAND

Fiddle

Pivot String values on column

2 Answers2