.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

Transforming a multi-row notes table into a single-row notes table

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server

Hi guys,

I'm new to SSIS and trying to design a package that will take a multi-row notes table and concatenate it into a single-row notes table. For example, the source fields may include Customer#, Note#, NoteText, Sequence#. In the current format, a long note may be stored in 2, 3, 4 rows. My SSIS package somehow needs to loop through each row, ordered by Sequence#, and concatenate the NoteText field into a single output field. For example:


Customer# Note# NoteText      Sequence#
--------- ----- --------      ---------
1234   1   Example of a multi 1
1234   1   line note.     2
5555   2   Single line note.  3
6666   3   Example of a multi 4
6666   3   line note that spans5
6666   3   three rows.     6


Needs to output the following:


Customer# Note# NoteText 
--------- ----- --------   
1234   1   Example of a multi line note.
5555   2   Single line note.
6666   3   Example of a multi line note that spans three rows. 


Does anyone know what the best method is to accomplish this? I tried using a Script Task and writing it in VB.NET but I wasn't able to figure out how to access multiple rows of data at once. I don't have much experience writing T-SQL Stored Procedures, but I could learn quickly if this is the best method.

Any suggestions?

View Complete Post

More Related Resource Links

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

Cutting Edge: Creating a Multi-table DataGrid in ASP.NET


If you bind a multi-table DataSet to a DataGrid, only the first table is recognized. Here Dino Esposito writes a custom solution the the multi-table problem.

Dino Esposito

MSDN Magazine August 2003

no of rows in single table

hi friends...i need one count...one sql table how many rows able to create or write in sql server 2005

wrapping a table into a multi column page

I have a table populated with some data fields that spans 5 pages.  I am trying to set up the layout to be a multi column report (just two columns) in which the table wraps at the end to populate the right section of the page How can this be done?Javier Guillen

Mark columns as Identity in multi-table views?

Hello! When you create a view of one table (i.e. SELECT * FROM Table), it marks the table's identity column as an identity col in the view as well. I have a few that selects columns from several nested tables, but they are all related in a main identity column I select as well, but SQL Server doesn't mark this column as identity, is there a way to do it?Shimmy

Tricky SELECT query from a Single Table

Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks

How to know the number of columns displayed on the last page for Multi column table

Hi, How will I determine how many columns to be displayed on the last page of the report if I am using a Multiple column table.  I am going to use the number of columns to multiply with the width of the body of the report. I am going to use a Function to get the product. 

To Search a word in all the columns of a Single Table

Dear Friends, How can I search a word in all the columns of a table?Please advise.

How to Listen to MSMQ and Database table from a single WCF service

Hi, I have one WCF service which is listenibg to MSMQ. Once message is added to MSMQ, it will start processing the business logic. But my requirement is to make this WCF service to listen to one database table also. If any message is logged to this table or any message added to MSMQ, the WCF service should start processing the business logic. Could you please let me know if it's feasible using WCF service. It would be great if you can tell me the alternate approaches to implement this functionality. Ram.  .NET related discussions

update a single table in edmx file


I'm working on the big project who has edmx file with lots of table. I want to update a table from DB. But when I update the edmx file, it  also refresh the other tables from DB. How do I update single table?

Multi table inserts



Please bear with me if this question sounds simple as SQL is definately not my forte.

I want to insert a new record into a table.  The table has relationships with other secondary tables, For example:

Table: Stores

Table StoreCategories (a store can have many of these)

I want to insert a new store, get its ID and insert some categories in one go.

How do I go about this?



Confusion on retriving single value from table


I am confused when pulling a single value from a table, and although I have a work-around, there would seem to be a more eloquent process that I am missing.

Say I have a table of friends, and I want to pull out a single integer value.  I would think that the following C# code in asp.net (with LINQ) would do the trick.

int i = from p in db.FRIENDS where p.name == "JohnSmith" select age;

If I run the above code though, I throw a cast exception.  Instead, I have to do the following:

var i = from p in db.FRIENDS where p.name == "JohnSmith" select age;
int a;

foreach(FRIENDS j in i)
    a = j.age 

Is there a better way of grabbing just a single record value when I know what the type?  Something that is cleaner to read?

How to take backup of single table in SQL server 2008 and SQL Server Management Studio Environment



I know how to take total database backup in SQL server 2008 and SQL Server Management Studio Environment but i failed to take single table backup in database.

It is possible to take single table backup in Oracle using PL/SQL Developer IDE.

Thanks in Advance..... 

It is possible to alter multiple columns within a single alter table statement?


It is possible to alter multiple columns within a single alter table statement?

I tried & searched not getting it.

Alter table au_de alter column m_user char(9),c_user char(9)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ','.


Alter table au_de alter column m_user char(9),alter column c_user char(9)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

question about multi rows update in SQL table


Hello, im tryin to do something without success. I have some SQL table with few columns (fileId(int), fileName, copies, color and finish), the page is build in a way, that the user upload few files (the files uploads to some folder, and the fileId and fileName are write to the above SQL table - so the other columns (copies, color, finish) are stay blank - which is OK!!!) after he finished uploading the files he can see the files names in GridView and in that grid view i put some textbox for copies, and 2 DDL for color and comments. I need to update the rows with the new data after the user click some button (the all rows) this is the code:


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
                                        BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataKeyNames="fileId"
                                        Width="100%" Font-Names="Arial" Font-Size="X-Small">
                                        <RowStyle ForeColor="#000066" />
                                            <asp:BoundField DataField="fileName" HeaderText

using table variable in single quote



I searched over internet but i couldn't find. How can i use table variable in single quote here some of code that i can do and i can't

for example if i can write like this it will work

declare @table table ([name] varchar(20))
insert into @table values('ahmet')
select * from @table
but if i can write like this does not work
exec('select * from ['+@table+']')
I can use any variable for table name like
declare @tableName varchar(20)
set @tableName = 'sampleTable'
exec('select * from ['+@tableName+

cascading drop down lists from single table


my one table consists of fields (id, flying from, flying to). thus i have one drop down for flying from and another drop down for flying to. i'm hoping to cascade them so 2nd drop down values are dependant on the values from 1st drop down. is it possible to do this using a single table? or must i use two tables and link the id's from both? and do i write anyting in the SelectedIndexChange event? thanks...

the sqldatasource code looks like this:

Flying From:

        <asp:DropDownList ID="ddlFlyingFrom" runat="server" DataSourceID="SqlDataSource1"
            DataTextField="FlyingFrom" DataValueField="Id" AutoPostBack="True" OnSelectedIndexChanged="ddlFlyingFrom_SelectedIndexChanged">

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
            SelectCommand="SELECT [Id], [FlyingFrom] FROM [Flights]"></asp:SqlDataSource>

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