SQL command to get two row values in single row

Saafia NM

TableName:Upload

ID	Keyword
1116	Dot Net
2836	Dot Net
5636	Dot Net

TableName:Index_list (Resultant Table)

ID	Keyword
1116,2836,5636	Dot Net

Sir i need the sqlcommand query to get resultant table in asp .net(c# language)

I tried the following command,but it gives the result same as Upload table.

SqlCommand cmnd= new SqlCommand("select Upload.Keyword,ID=replace((select ID as [data()] from Index_list where Index_list.Keyword=Upload.Keyword order by ID for XML PATH('')),'',',') from Upload group by Keyword", con);

it works in SQL Server Management Studio,but not in Visual Studio 2012 sqlcommand .Please help.....

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shubham Kumar10y ago
Accepted answer
if you want to insert this value in index table make query like this

string query = @"INSERT INTO Index_list SELECT STUFF((SELECT ', ' + CAST(Id AS VARCHAR(10)) [text()] FROM Upload WHERE Keyword = t.Keyword FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') Id,Keyword FROM Upload t GROUP BY Keyword ";
SqlCommand cmnd = new SqlCommand(query, con);
Shubham Kumar10y ago
welcome !!! if your problem is resolve plzz mark post as "asccept as ans"
Saafia NM10y ago
@Shubham Kumar
Saafia NM10y ago
Yes i got the Result!!! Thank you very much sir and too C-Sharpcorner....
Amit Kumar Singh10y ago
http://www.c-sharpcorner.com/UploadFile/860b29/concatenate-multiple-rows-within-single-row-in-sql-server-20/
Shubham Kumar10y ago
no its not possible i tested this query it gives result like these

ID Keyword
-------------------------- ----------
10, 100, 1000, 1000 a
Saafia NM10y ago
Great it works!!!Thank you sir.But i got the result as :
ID Keyword
10 a
10,100 a
10,100,1000 a

What will do???
Saafia NM10y ago
Sir it does not work .Please help me....
Saafia NM10y ago
Sir, I have to get the result in the table "Index" from table Upload as:
ID Keyword
1,100,1000 a
Am asking the query does not mention the table name "Index" which will give the result,then how can i get it in the table Index?
Thank you!!!
Shubham Kumar10y ago
Not getting clearly your mean

you have to copy the resultset into another table i m right
Saafia NM10y ago
sir thank you for your quick replay.... I have one more doubt. I have to get the result in another table name as "Index".But it does not mentioned in the query. The how can i get it in the table "Index" with ID and Keyword as columns???
Shubham Kumar10y ago
yes why not just make the string of the query and pass it on command
string query = @"SELECT STUFF((SELECT ', ' + CAST(Id AS VARCHAR(10)) [text()] FROM Upload WHERE Keyword = t.Keyword FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') Id,Keyword FROM Upload t GROUP BY Keyword ";
SqlCommand cmnd = new SqlCommand(query, con);
Saafia NM10y ago
Is it possible to query this in sqlcommand???
Shubham Kumar10y ago
Hi,

Just try this query //change table as you want
DECLARE @Upload TABLE(ID INT, Keyword VARCHAR(50))
INSERT INTO @Upload VALUES (10,'a'),(100,'a'),(1000,'a'),(1000,'a')

SELECT STUFF((SELECT ', ' + CAST(Id AS VARCHAR(10)) [text()]
FROM @Upload
WHERE Keyword = t.Keyword
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') Id,Keyword
FROM @Upload t
GROUP BY Keyword

ID	Keyword
10	a
10,100	a
10,100,1000	a

ID	Keyword
1,100,1000	a