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

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

split separated values into columns

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

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

View Complete Post

More Related Resource Links

Split comma separated values into columns




 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

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

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 :)

Custom Columns - Fill values based on value selected in a lookup column



I am working with Sharepoint 2007. I have a list of 130 buildings located in six different cities and three different provinces. I imported this list from an Excel spreadsheet. I also have a document library that contains various files associated with these buildings. I've added a custom column to the document library that contains a lookup field to the name of each building. I also have two additional custom columns that have the city and province. What I would like to accomplish is to have the city and province columns automatically be set based on the name of the building selected, such that these values are taken from the same row of this list as the building name.

Hopefully my question makes sense.

Thank you, James

Urgent:Create/Update Winforms grid columns and rows with config file values


I have legacy winforms datagrid , I have to add/replace columns with new values, My new column names and row values are changes as per new requirements , so I am looking to update datagrid form any config file , so in future for any new requirements , If I update my config file , it hast update existing datagrid.

Please suggest me the code; I am trying from 4 days and its urgent now.



Urgent:Create/Update Winforms grid columns and rows with config file values


I have legacy winforms datagrid , I have to add/replace columns with new values, My new column names and row values are changes as per new requirements , so I am looking to update datagrid form any config file , so in future for any new requirements , If I update my config file , it hast update existing datagrid.

Please suggest me the code; I am trying from 4 days and its urgent now.




SP Designer 2010 Conditional Formatting with Hidden Values/Columns


I have a list with 30~40 columns. I want to do conditional formatting on it using SharePoint 2010 Designer (instead of creating a visual webpart with vs2010).

Of the 30-40 columns, i am only viewing 5 columns that i need (ie: first name, last name, etc...)

I can create the XSLT ListView Webpart on the page within SharePoint 2010. I can edit it with SharePoint 2010 Designer, i can also apply conditional formating to the row (ie: select row within XSLT webpart > Options > Conditional Formatting).

However, i cannot apply the conditional formatting too columns that are not listed in the <ViewFields> of the <XmlDefinition>. What gives? I want to do conditional formatting on the entire row of the item using the hidden ten or so columns and i don't want the user to see all the columns, but i want to do conditional formatting on them (its for æsthetics reasons, i don't want a grid list of 15+ columns on someones poor 1024 by 768 resolution/monitor... plus they don't need to see it until they click into the list item....)


<FieldRef Name="SomeColumnName" Hidden="True" /> does not work
<FieldRef Name="SomeColumnName" ReadOnly="True" /> does not work either

Possible to use CASE in select to select different columns/values?


Hey everyone,

I'm trying to work some SQL to work like the following (which doesn't work/isn't allowed). Is there a way in SQL to do this?

declare @mode int
set @mode = 1

Declare @Profiles varchar(400) 
set @Profiles=''

Declare @Count int

-- This is where it's tricky.
-- If mode is 1, then we want to return the count of the records.
-- If not, we want to return the values. 
-- What we don't want to do is have to write the entire select
-- statement twice. :-)
select case @mode
	when 1 then @Count=count(1) 
	else *
from CLACX x inner join
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