Pivoting to a Single Column in SQL

Question

Hi All I have a Database table structure in the following format.. ID Value 100 ADE 100 BCD 100 CEF 101 MNO 101 PQR 101 XYZ 102 EFG I want a query to retrieve the results of table in the following format. ID Results 100 ADEBCDCEF 101 MNOPQRXYZ 102 EFG I know that we can accomplish this by PIVOT Keyword but not able to finalize the query can you guys help me out Please Thanks in Advance Vishwas

Jignesh Trivedi · Answer

hi you may use Stuff keyword to get comma seperated values try following TSQL. create table #temp ( Id int value varchar(20) ) insert into #temp values(100 'ADE' ) (100 'BCD' ) (100 'CEF' ) (101 'MNO' ) (101 'PQR' ) (101 'XYZ' ) (102 'EFG' ) select * from #temp select Id Results = STUFF((SELECT ' ' + value FROM #temp b WHERE b.Id = a.id FOR XML PATH('')) 1 2 '') from #temp a group by Id hope this will help you.

Satyapriya Nayak · Answer

Untitled Page using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; namespace Display_column_data_into_rows { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString; SqlCommand com; string str; DataSet ds; SqlDataAdapter sqlda; protected void Page_Load(object sender EventArgs e) { if (!IsPostBack) { bindgrid(); } } void bindgrid() { SqlConnection con = new SqlConnection(connStr); con.Open(); str = select IDSubstring((select(' '+ Value) from t1 t where t1.ID= t.ID order by IDValue for xml path(''))31000)AS Results from t1 group by ID; com = new SqlCommand(str con); sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds t1); GridView1.DataSource = ds; GridView1.DataMember = t1; GridView1.DataBind(); con.Close(); } } }