How do I put different values for the same field in a row

Question

0.00/5 (No votes)

See more:

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

Posted 22-Aug-18 10:27am

reed_s

Updated 22-Aug-18 18:58pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2018-08-22T11:09:00

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

SQL

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

SQL

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

which returns

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

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

SQL

; 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

which will return this

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

Santosh kumar Pithani · Accepted Answer · 2018-08-22T18:58:00

SQL

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

How do I put different values for the same field in a row

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0