title: TSQL random value from table select in every row
body: <p>Using the <code>RAND()</code> function or <code>NEWID()</code> to generate a random value in each column as <a href="https://stackoverflow.com/questions/580639/how-to-randomly-select-rows-in-sql">described here</a> you can generate random values in a row. But... the value repeats the same in every row. </p>
  
  <p><img src="https://i.stack.imgur.com/isaBW.jpg" alt="enter image description here"></p>
  
  <p>How can you generate a random value in each row for every column? I would like to show in column B1 a random product which is also in group '102'. The same for column B2 but a different product. And the next row again two different products and so on. The result will show 2 alternative products from the same group per product (row). </p>
  
  <p>My code looks something like this:</p>
  
  <pre><code>DECLARE @B1 varchar(30)
  DECLARE @B2 varchar(30)
  SET @B1 = (SELECT TOP 1 ItemCode FROM items WHERE items.Assortment IN (102) ORDER  BY RAND())
  SET @B2 = (SELECT TOP 1 ItemCode FROM items WHERE items.Assortment IN (102) ORDER  BY NEWID())
  
  SELECT ProdCode, ProdName, @B1 as B1, @B2 as B2
  FROM Products
  WHERE ProductGroup IN (102) --use IN so you can select multiple groups
  </code></pre>
  
  <p>Thanks!</p>