Try this.....
Note:- Every run you get a different "disease"
WITH TCE_Shuffled AS
(
SELECT ROW_NUMBER() OVER (ORDER BY id ) AS ORIG_ROWNUM,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS NEW_ROWNUM,
*
from dbo.myTableDis
)
select t1.id, t1.name, t2.disease, t1.city, t1.age, t1.gender, t1.zipcode
from TCE_Shuffled t1
join TCE_Shuffled t2
on t1.ORIG_ROWNUM = t2.NEW_ROWNUM
private DataSet GetDataset()
{
string connstring = "server=.;"
+ "Trusted_Connection=yes;"
+ "database=test; "
+ "connection timeout=30";
DataSet dataset = new DataSet();
string queryString = "WITH TCE_Shuffled AS "
+ " ("
+ " SELECT ROW_NUMBER() OVER (ORDER BY id ) AS ORIG_ROWNUM,"
+ " ROW_NUMBER() OVER (ORDER BY NEWID()) AS NEW_ROWNUM,"
+ " *"
+ " from dbo.myTableDis"
+ " )"
+ " select t1.id, t1.name, t2.disease, t1.city, t1.age, t1.gender, t1.zipcode"
+ " from TCE_Shuffled t1"
+ " join TCE_Shuffled t2"
+ " on t1.ORIG_ROWNUM = t2.NEW_ROWNUM";
using (SqlConnection connection = new SqlConnection(connstring))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(queryString, connection);
adapter.Fill(dataset);
return dataset;
}
}