.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Split comma separated values into columns

Posted By:      Posted Date: October 10, 2010    Points: 0   Category :Sql Server



 I have data like this in my table:


AppId   Gender

1         x

2         y

3         x, y

4         x, y, z


I need to transform like this:

AppID          Gender

1                  x

2                  y

3                  x

3                  y

4                  x

4                  y

4                  z


How to do this?


Thanks in advance

View Complete Post

More Related Resource Links

split separated values into columns


Hi @all,

here is my current data structure...


item1   item2

1;2;3   4;5;6


I need to transform the structure to that one. How can i do that?

item1          item2

1                  4

2                  5

3                  6

How to list comma separated values in column to multiple rows




I need to list comma seperated values form one to column in multiple rows..ex:

id                           call_rec 

1                            508190,737079,1004618,1075562,1272753,1390841,1544201,2591141

 2                           335395,359380,741049,1113212,2283999,2908851,2001205615 

3                            1212358,2451853,2795175,2001196872 

4                            2283999,2451853 

I want the call_rec for each bug ID to be listed



Split Parameter and asign comma seperated values to different parameters?


Hi All,

I have a paramter field @param that gets passed a comma seperated list of values eg ('Value1','Value2','Value3').


What i would like to be able to do is split these values out and assign them to paramters for later use.  So if for example 'Value1','Value2','Value3' was passed i would count the number of values and in this case its 3 so create 3 parameters named


@One = Value1

@Two = Value2

@Three = Value3


Is this even possible?


Many Thanks,





gridview help on each dynamic row solve this show values based on all columns

<asp:GridView style="Z-INDEX: 101; LEFT: 1px; POSITION: absolute; TOP: 183px" id="Gridview1" runat="server" Width="613px" Height="127px" ForeColor="Teal" Font-Size="12pt" Font-Names="Palatino Linotype" Font-Bold="True" Visible="False" BorderColor="Transparent" AutoGenerateColumns="false" CellSpacing="2" GridLines="None" OnRowCreated="Gridview1_RowCreated" SelectedIndex="5" ShowFooter="true" OnRowDataBound="Gridview1_RowDataBound"><Columns>  <asp:TemplateField HeaderText="ItemCode"><ItemTemplate> <asp:DropDownList id="DropDownList1" tabIndex=16 runat="server" AutoPostBack="True" DataValueField=" ItemCode " DataTextField=" ItemCode" ></asp:DropDownList></ItemTemplate></asp:TemplateField>  <asp:TemplateField HeaderText="ItemName"><ItemTemplate> <asp:DropDownList id="DropDownList2" tabIndex=16 runat="server" AutoPostBack="True" DataValueField="ItemName" DataTextField="ItemName" ></asp:DropDownList> </ItemTemplate> </asp:TemplateField>  <asp:TemplateField HeaderText="Category"&

split a column into multiple columns

Hi i have a column with the following values from my source FILENAME ComplianceStatus_ER_06022010.xml ComplianceStatus_AZER_07052010.xml ComplianceStatus_GEL_06022010.xml ComplianceStatus_AFF_05022010.xml ComplianceStatus_Good_2010.xml   I want to split the date into different columns as follows using ssis   FILENAME                            CLIENT      DATE ComplianceStatus_ER_06022010.xml    ER          06022010 ComplianceStatus_AZER_07052010.xml  AZER        07052010 ComplianceStatus_GEL_06022010.xml   GEL         06022010 ComplianceStatus_AFF_05022010.xml   AFF         05022010 ComplianceStatus_Good_2010.xml      Good        2010  

Comparing columns with NULL values--Merge says unmatched when data is matched.

I found the article below describing this same issue with the Oracle merge statement.  It also described a work-around:    http://searchoracle.techtarget.com/tip/Merging-datasets-with-NULL-values I have been unable to find a solution to this issue for SQL Server 2008.  I am trying to do basic ETL from Staging table to a Type II dimension table in a Data Warehouse.  I am using the Merge statement with the Unique key minus the modification date as my merge key list.  I have a handful of columns (in both Staging and the DW) containing nulls in my merge key list.  I have tried the ISNULL function and the ANSI_NULL db option (OFF) with no success.  When I put zeros in the null columns, it works as expected.  I am about to try separate INSERT and UPDATE statements, but I fear that the NULL comparison issue will bite me there with the JOIN statement.  Has anyone else experienced this?  NULL values are valid in our Data Warehouse.  How can I make T-SQL handle them? Thank you for your help, Jesse

insert mulitple comma seperated values in a temp table

I have a reporting services report with a single parameter that can take up to 10 numbers which are comma delimited. I need to insert those numbers into a temp table with one column. So if the numbers were 1 - 10 I would expect to see something like this if querying the temp table Acct_Num 1 2 3 4 5 6 7 8 9 10 Is there a way to do this insert into the table using a comma delimeted string of numbers? ThanksFJK

Comma Separated list returned from XML query

I found an example of how to do this, but I must have something wrong as I can't get it to execute without syntax errors or errors. Here's an example XML:   <GrantList> <GRANT SAPGrantNumber="12345" PersonID="168658" SequenceNumber="9" ItemTypeID="1" > <PEOPLE Investigators="Smith, Many" /> <PEOPLE Investigators="Brown, Esmond" /> <PEOPLE Investigators="White, Christine" /> <PEOPLE Investigators="Saga, John" /> <PEOPLE Investigators="Mog, Maureen" /> <PEOPLE Investigators="LastName, FirstName" /> </GRANT> <GRANT SAPGrantNumber="12645" PersonID="168658" SequenceNumber="10" ItemTypeID="1" /> <GRANT SAPGrantNumber="16546" PersonID="168658" SequenceNumber="11" ItemTypeID="1" /> <GRANT SAPGrantNumber="684684351" PersonID="168658" SequenceNumber="12" ItemTypeID="1" > <PEOPLE Investigators="Smith, Many" /> <PEOPLE Investigators="Smith, John" /> <PEOPLE Investigators="Mog, Maureen" /> <PEOPLE Investigators="Xiansa, Ming" /> </GRANT> </GrantList> Here's my query where the xml above is in the

i need the row index value in the form of "comma Separated value"

hi, i am new to the C# in one of my page i need to pass the gridview rowindex vaule where the check box is checked true [i.e in comma sepparated vaule] this is my html code <tr>  <td>  <asp:GridView ID="GridView1" runat="server" EmptyDataText="THERE IS NO DATA FOR THIS REQUEST">  <Columns>  <asp:BoundField DataField="Tablelevelid" HeaderText="att_ID" />  <asp:TemplateField>  <HeaderTemplate>  <asp:CheckBox ID="checkAll" runat="server" onclick="checkAll(this);" /></HeaderTemplate>  <ItemTemplate>  <asp:CheckBox ID="CheckBox1" runat="server" onclick="Check_Click(this)" /></ItemTemplate>  </asp:TemplateField>  <asp:BoundField DataField="NAME" HeaderText="Name" />  <asp:BoundField DataField="PROJECT_ID" HeaderText="Project" />  <asp:TemplateField HeaderText="Comments">  <ItemTemplate>  <asp:TextBox ID="txtComments" runat="server" Text="" MaxLength="250"></asp:TextBox></ItemTemplate>  </asp:TemplateField>  <asp:TemplateField>  <ItemTemplate>  <asp:Require

Error comparing two identical columns in Condtional Split


(SQL 2005)

I'm reading in one SQL table and doing a lookup against another SQL table which is an identical copy of the first table, used for temporary storage during the load event.

I want to compare each field to find if there are any changes in the temporary table.  When I compare two string fields, Customer != temp_Customer I get no error message.  When I compare two decimal fields, Width != temp_Width I get an error message: DT_WST and DT_Numeric are incompatible with !=.

The two fields are BOTH declared as decimal 19,6 in their respective tables.  Why can't I run this comparision???

GridView split over multiple columns



I have a project where I need the one datagrid displaying all values on the one screen, thus I don't have paging enabled.

Is there a way to get a gridview to 'split' over two columns?

ie instead of having

|<- column 1 ->|<- column 2 ->|<-button->|

item 1


Item 30


|<- column 1 ->|<- column 2 ->|<-button->|<- column 1 ->|<- column 2 ->|<-button->|

Item 1                                                            Item 16


Item 15                                                          Item 30

Mapping custom values and columns to destination table **Newb question**



I am copying data from one source to destination SQL CE table using the data flow task. I have two extra requirements which I am not sure how to achieve.

1.)  One source column needs to be mapped to two destination columns. In the column mapping dropdown the column name disappears after mapping it to one destination column.

2) some custom values for columns need to be added, which are not present in source. For e.g. a default value for some destination columns which is not present.

Any pointers would be extremely appreciated. Thanks,

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

How to split columns in gridview?


Hi there,

I am using Visual Studio 2010 where I have a gridview that displays many columns and because of that the user has to scroll horizontally in order to view the rest of the columns.

Is there a way to split the columns so that each row will contain some columns and some columns underneath them?

For example, I have 10 columns where each row will show 5 columns with their headers and the other 5 columns below with their headers.

Your help will be greatly appreciated.

Saving CheckBox Values from a GridView with AutoGenerated Columns


Is it possible to first add controls (ie. CheckBoxes) to a GridView with AutoGenerateColumns set to true. Then to click a single save button which loops thru the controls in the GridView, gets their values (ie. Checked/Not Checked) and then saves those values to a database?


Hiding Columns in Matrix (RS 2005) Report Based on Columns' Values



Is there a way to hide automatically columns in a Matrix type raport in RS 2005.  The columns I want to hide have only 'N/A' values in them because there is no data available to populate them.  Here is the example:

  Fall 2010 Fall 2009

Split Address into 3 columns using a udf


Hi All,


I have a column which consists of data as City,state Zip. They are combined in this way together. EX:



How to split this into 3 separate columns using a function so that i can use this function in a view .




display / insert values by comparing 2 columns of a table



i have a table which has the following columns 

A_ID, A_Value,A_Date,B_ID,B_Value_B_Date


i have another table as follows

A_ID,B_ID,A_Value,B_Value, Date


this is to be done 

* if A_date and B date are equal, one record shoould be entered to the TABLE 02. the values should be A_ID,B_ID,A_Value_B_Value and A_Date (since the date is equal, no problem; even can insert B_Date instead of A_Date)

* if A_Date and B_Date are different, then the following should happen

insert one record to TABLE02 as A_ID,B_ID, A_Value, 0 , A_Date

insert another record to TABLE02 as A_ID,B_ID, 0 , B_Value, B_Date


(As you can see, when the date differs we enter the single record of Table 01 into 2 recoerds in table 02. the Value column and Date column are related in such a way that when A's date is entered, the value for B should be 0 and vise versa..)


i tried having a UDF but in halfway remembered that we cannot include INSERTS in UDFs..

can anyone please explain me how can i solve this problem ??


PS: For your reference , i've sketched a diagram. please click on this link to view it for further clarifications



thanks in advance

-novicedba :)

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend