-2

Personal_Details table

name   age
john   25
nikita 29

Sports table:

name   games
john   basketball
john   volleyball
john   football
nikita skating

Desired output:

name   games1     games2     games3
john   basketball volleyball football
nikita skating    null       null

i want to join both tables if there is match based on name variable then return the values of games in new variable . Let me know any suggestions how to achieve this!!

rajiv
  • 19
  • 4
  • Do you know the number of columns that you want in the result set? – Gordon Linoff Apr 15 '20 at 13:28
  • Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Ross Presser Apr 15 '20 at 13:47

2 Answers2

1

If you have a known or maximum number of columns, you can perform a simple PIVOT in concert with row_number()

Example

Declare @YourTable Table ([name] varchar(50),[games] varchar(50))  Insert Into @YourTable Values 
 ('john','basketball')
,('john','volleyball')
,('john','football')
,('nikita','skating')

Select *
 From  (
        Select [Name] 
              ,[Games]
              ,[Col]   = concat('games',row_number() over (partition by [name] order by [games]))
         From @YourTable
       ) src
 Pivot ( max([Games]) for Col in ([Games1],[Games2],[Games3],[Games4]) ) pvt

Returns

Name    Games1      Games2      Games3      Games4
john    basketball  football    volleyball  NULL
nikita  skating     NULL        NULL        NULL
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

Probably the simplest method is to aggregate the values into a string rather than in separate columns. This is not exactly what you are asking for but it might do what you want:

select name, string_agg(game, ',') as games
from sports
group by name;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes @Gordon it is working thanks.But can suggest how do i put the values in separate columns – rajiv Apr 16 '20 at 03:39