sql-server SQL并排选择值

wooyq4lh 于 2022-10-31 发布在其他

关注(0)|答案(2)|浏览(221)

我有关于用户和目标点的数据。这是我的数据。

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

我需要此输出

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

用于测试的表脚本

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

你能帮助我关于sql查询吗

sql-server

来源：https://stackoverflow.com/questions/74093592/sql-select-values-side-by-side

2条答案

按热度按时间

i34xakig1#

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_

中的每一个
您可以使用类似这样的东西。如果目标的数量是未定义的，那么请谷歌 " 动态透视 SQL Server "

赞(0）回复(0）举报 2022-10-31

cetgtptt2#

您可以使用动态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
来源：Pivot in SQL Server
正如@Larnu所建议的
Fiddle with STRING_AGG
注意：可以从SQL Server 2017 (14.x) and later开始使用STRING_AGG

赞(0）回复(0）举报 2022-10-31

我来回答

sql-server SQL并排选择值

2条答案

相关问题

热门标签

最新问答