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	ADE,BCD,CEF
101	MNO,PQR,XYZ
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 ID,Substring((select(', '+ Value) from t1 t where t1.ID= t.ID order by ID,Value for xml path('')),3,1000)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(); } } }

Pivoting to a Single Column in SQL

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)