SQL command to get two row values in single row

Question

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.....

Shubham Kumar · 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)')12' ') IdKeyword FROM Upload t GROUP BY Keyword ; SqlCommand cmnd = new SqlCommand(query con);

Shubham Kumar · Answer

welcome !!! if your problem is resolve plzz mark post as asccept as ans

Shubham Kumar · Answer

no its not possible i tested this query it gives result like these ID Keyword -------------------------- ---------- 10 100 1000 1000 a

Shubham Kumar · Answer

Not getting clearly your mean you have to copy the resultset into another table i m right

Shubham Kumar · Answer

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)')12' ') IdKeyword FROM Upload t GROUP BY Keyword  ; SqlCommand cmnd = new SqlCommand(query con);

Shubham Kumar · Answer

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)' )12 ' ' ) IdKeyword FROM @Upload t GROUP BY Keyword

Saafia NM · Answer

@Shubham Kumar

Saafia NM · Answer

Yes i got the Result!!! Thank you very much sir and too C-Sharpcorner....

Amit Singh · Answer

http://www.c-sharpcorner.com/UploadFile/860b29/concatenate-multiple-rows-within-single-row-in-sql-server-20/

Saafia NM · Answer

Great it works!!!Thank you sir.But i got the result as : ID Keyword 10 a 10100 a 101001000 a What will do???

Saafia NM · Answer

Sir it does not work .Please help me....

Saafia NM · Answer

Sir I have to get the result in the table Index from table Upload as: ID Keyword 11001000 a Am asking the query does not mention the table name Index which will give the resultthen how can i get it in the table Index? Thank you!!!

Saafia NM · Answer

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???

Saafia NM · Answer

Is it possible to query this in sqlcommand???