SQL Select values side by side-CodePudding

I have data about users and target points. This is my data.

User_   Target
UserA   Target1
UserA   Target2
UserA   Target3
UserB   Target1
UserB   Target3
UserB   Target6

I need this output

User     FirstTarget     SecondTarget      ThirdTarget       FourthTarget
UserA      Target1          Target2           Target3
UserB      Target1          Target3           Target6

Table script for testing

CREATE TABLE [dbo].[UserTarget](
    [User_] [varchar](50) NULL,
    [Target] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[UserTarget] ([User_], [Target]) VALUES (N'UserA', N'Target1')
INSERT [dbo].[UserTarget] ([User_], [Target]) VALUES (N'UserA', N'Target2')
INSERT [dbo].[UserTarget] ([User_], [Target]) VALUES (N'UserA', N'Target3')
INSERT [dbo].[UserTarget] ([User_], [Target]) VALUES (N'UserB', N'Target1')
INSERT [dbo].[UserTarget] ([User_], [Target]) VALUES (N'UserB', N'Target3')
INSERT [dbo].[UserTarget] ([User_], [Target]) VALUES (N'UserB', N'Target6')
GO

Could you help me about sql squery

CodePudding user response：

SELECT X.USER_,
MAX
(
  CASE
     WHEN X.XCOL=1 THEN X.TARGET
     ELSE ''
  END
)FIRST_TARGET,
MAX
(
 CASE
   WHEN X.XCOL=2 THEN X.TARGET
   ELSE ''
 END
)SECOND_TARGET,
MAX
(
 CASE
   WHEN X.XCOL=3 THEN X.TARGET
   ELSE ''
 END
)THIRD_TARGET,
MAX
(
 CASE
  WHEN X.XCOL=4 THEN X.TARGET
  ELSE ''
 END
)FOURTH_TARGET
FROM
(
   SELECT T.[User_],T.[Target],
   ROW_NUMBER()OVER(PARTITION BY T.USER_ ORDER BY T.[Target] ASC)XCOL
  FROM [dbo].[UserTarget] AS T
)X
GROUP BY X.User_

You can use something like this. If the number of targets is undefined then please google "dynamic pivot SQL Server"

CodePudding user response：

You can use Dynamic SQL PIVOT

/*Declare Variable*/  
DECLARE @Pivot_Column [nvarchar](max);  
DECLARE @Query [nvarchar](max);  
  
/*Select Pivot Column*/  
SELECT @Pivot_Column= COALESCE(@Pivot_Column ',','')  QUOTENAME([Target]) FROM  
(SELECT DISTINCT [Target] FROM [UserTarget])Tab  
  
/*Create Dynamic Query*/  
SELECT @Query='SELECT [User_], ' @Pivot_Column 'FROM   
(SELECT [User_] , [Target] FROM [UserTarget] )Tab1  
PIVOT  
(  
MAX(Target) FOR [Target] IN (' @Pivot_Column ')) AS Tab2  
ORDER BY Tab2.[User_]'  
  
/*Execute Query*/  
EXEC  sp_executesql  @Query

Sample Fiddle

Source: Pivot in SQL Server