SQLTeam.com | Weblogs | Forums

How to Select Column Values and Output to the right

Transact-SQL
#1

Hi experts,
FOr example if I have Table1 with 3 rows:

Number Name
1 John
2 Mary
3 Sam

How can I code a Select to get the ouput as:
John Mary Sam each name in its own column?
Would a Pivot do that? Can anyone give the sql syntax please?
Thanks

#2

yes pivot would it

1 Like
#3

It's SQL 2019

#4

or this

declare @h table(Number int, Name varchar(50))

insert into @h
select 1 ,'John' union
select 2 ,'Mary' union
select 3 ,'Sam'


select Number,
     max(case Number when 1 then Name end ) as 'John',
	 max(case Number when 2 then Name end ) as 'Mary'
  from @h
  group by number
1 Like
#5 

;WITH cte_allow_numbers_to_have_gaps AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY Number) AS row_num
    FROM dbo.your_table_name
)
SELECT
    MAX(CASE WHEN row_num = 1 THEN Name END) AS Name1,
    MAX(CASE WHEN row_num = 2 THEN Name END) AS Name2,
    MAX(CASE WHEN row_num = 3 THEN Name END) AS Name3 /*,...*/
FROM cte_allow_numbers_to_have_gaps
1 Like