Databases
StackOverflow2013
Postsdbo
POMake a single column value from multiple rows column
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMake a single column value from multiple rows column
    text
    copied!<p>I am facing a very isolated problem regarding to the <code>dynamic sql query</code>. I have two queries running on a single <code>stored procedure</code>. They are following</p> <p>First query:</p> <pre><code>SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY viwPerformance.LastModifiedOn DESC) AS rowNumber,viwPerformance.* FROM viwPerformance WHERE OrgId=218 AND EmployeeId = 1668 AND IsTerminate = 0 AND TagId LIKE '%' + CAST(2893 AS VARCHAR) + '%' AND Archive='False' AND SmartGoalId IS NOT NULL ) AS E WHERE rowNumber &gt;= 1 AND rowNumber &lt; 11 </code></pre> <p>it results all the column values and the SmartGoalId as</p> <pre><code>4471,2815,4751,4733,4863,4690,4691,4692,4693,4694 </code></pre> <p>And the second query (here I need only SmartgoalId from the above query so I use stuff)</p> <pre><code>SELECT @strGoalIds = STUFF((SELECT ',' + CAST(SmartGoalId AS VARCHAR) FROM ( SELECT ROW_NUMBER() OVER(ORDER BY viwPerformance.LastModifiedOn DESC) AS rowNumber,viwPerformance.* FROM viwPerformance WHERE OrgId=218 AND EmployeeId = 1668 AND IsTerminate = 0 AND TagId LIKE '%' + CAST(2893 AS VARCHAR) + '%' AND Archive='False' AND SmartGoalId IS NOT NULL ) AS E WHERE rowNumber &gt;= 1 AND rowNumber &lt; 11 FOR XML PATH('')), 1, 1, '') </code></pre> <p>and it's results the SmartgoalId as</p> <pre><code>4471,2815,4751,4733,4863,4651,4690,4691,4692,4693 </code></pre> <p>Please note that the last id "4694" is not available from above query as the "4651"is added to it but it's not available from first query and this is correct that "4651" should not be in the second query result.</p> <p>So my main point is why the second query gives different results as it's the same as the first query. </p> <p>Note: Am I right that the <code>Stuff</code> function reversing the values and not giving them in correct order.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload