如何在一行中为同一个字段添加不同的值 [英] How do I put different values for the same field in a row

问题描述

我正在尝试将不同的注释值显示在用户的一条记录中。我能够得到我想要的评论，但它创建了多条记录，每条记录都出现在自己的记录中而不是单个记录中。



我有什么试过：



SELECT

UserID

，（SELECT Comments

WHERE主题='评论1评论'

）AS评论1

，（SELECT CmEvent.Comments

WHERE Subject ='Comment2 Comment'

）AS评论2

，（SELECT CmEvent.Comments

WHERE Subject ='Comment3 Comment'

）AS Comment3

来自Table1（NOLOCK）

WHERE ID = 1

I am trying to different comment values to appear in one record for a user. I am able to get the comments I want but it creates multiple records with each appearing in its own record instead of in a single record.

What I have tried:

SELECT
UserID
,(SELECT Comments
WHERE Subject = 'Comment1 Comment'
) AS Comment1
,(SELECT CmEvent.Comments
WHERE Subject = 'Comment2 Comment'
) AS Comment2
,(SELECT CmEvent.Comments
WHERE Subject = 'Comment3 Comment'
) AS Comment3
FROM Table1(NOLOCK)
WHERE ID = 1

推荐答案

你最好的办法是打破数据（规范化）到用户和注释的单独表中。

Your best method would be to break the data up (normalization) into separate tables for users and comments.

DECLARE @Users TABLE (
  UserID INT PRIMARY KEY NOT NULL
, UserName VARCHAR(100)
)

DECLARE @Comments TABLE (
  CommentID INT PRIMARY KEY NOT NULL
, UserID INT NULL
, CommentTime DATETIME NULL
, CommentSubject NVARCHAR(100) NULL
, CommentText NVARCHAR(1000) NULL
)

INSERT @Users (UserID, UserName)
VALUES (1, 'Me')
,      (2, 'You')

INSERT @Comments (CommentID, UserID, CommentTime, CommentSubject, CommentText)
VALUES (1, 1, GetDate(), 'Comment1', 'My Initial Entry')
,      (2, 1, GetDate(), 'Comment2','My Second Entry')
,      (3, 2, GetDate(), 'Comment1','Your First Entry')
,      (4, 2, GetDate(), 'Comment2','Your Last Entry')

从那里你可以加入表格

And from there you can either join the tables

SELECT u.UserName, c.CommentTime, c.CommentText
FROM   @Users        u
INNER JOIN @Comments c ON u.UserID = c.UserID
WHERE  u.UserID = 1

返回

UserName     CommentTime     CommentText
--------     -----------     ------------
Me           2018-08-22      Initial Entry
Me           2018-08-22      Second Entry

或者您可以使用 PIVOT 来获取多个评论栏

Or you could use a PIVOT to get your multiple comment columns

; WITH cte AS (
  SELECT  u.UserID, u.UserName, c.CommentTime, c.CommentText, c.CommentSubject
  FROM   @Users        u
  INNER JOIN @Comments c ON u.UserID = c.UserID
)

SELECT  UserName, y.Comment1, y.Comment2 
FROM   cte
PIVOT (
  Max(CommentText)
  FOR CommentSubject IN ([Comment1], [Comment2] )
) as y

WHERE UserID = 1

将返回此

UserName  Comment1          Comment2
--------  ---------------   ----------------
Me        My Initial Entry  My Second Entry

CREATE TABLE #temp( Id INT,Comments NVARCHAR(50), CommentText NVARCHAR(1000));
INSERT INTO #temp (Id,Comments, CommentText)
VALUES (1,'Comment1', 'My Initial Entry')
      ,(2,'Comment2','My Second Entry')
      ,(1,'Comment3','Your third Entry')
      ,(1,'Comment4','Your Last Entry');

DECLARE @pivtcols VARCHAR(MAX),@Query NVARCHAR(MAX);

SELECT @pivtcols=STUFF((SELECT DISTINCT ','+QUOTENAME(Comments) FROM #temp where ID=1 FOR XML PATH('')),1,1,'');

 SET @Query=N'SELECT * FROM #TEMP PIVOT(MAX(CommentText) FOR Comments IN('+@pivtcols+'))As pvt WHERE ID=1';

 EXEC(@Query);

 DROP TABLE #temp;

 --OUTPUT:-
 -----------------------------------------------------
Id |	Comment1	   |      Comment3	  |        Comment4
---------------------------------------------------------
1	My Initial Entry	Your third Entry	Your Last Entry

这篇关于如何在一行中为同一个字段添加不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！