Pivoting to a Single Column in SQL

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.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Display_column_data_into_rows._Default" %>





 


 

 

 
 
 

 

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();
        }
    }
}