November 27, 2008 at 3:17 am
i have one table "operational" which have three field one is "id" and other is "name" and last one "isbeginName"
and logic behind this table is isbeginName is true then is it bieginingname other wise it a endingname that means for one id inserted two row in table .. and some time only endingname or begining name -------
"id" "name" "isbeginName"
1 pratyush TRUE
1 Singh FALSE
2 kumar FALSE
3 sachin TRUE
i want merge table in this form
"id" "begingname","endingname"
1 pratyush singh
2 kumar
3 sachin
can some one help me
November 27, 2008 at 6:58 am
You'll need to give some more information... in your data you have included "pratyush" and "singh" in the same row but there is nothing in your source data to tie those values together.
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
November 27, 2008 at 7:41 am
You can split the data out into two seperate staging tables, one for Firstname and One for LastName
Then use another dataflow to load these both up and join them together either by using INNER JOIN on your query source, or by using a Look-up transformation.
November 27, 2008 at 8:40 am
You can split the data out into two seperate staging tables, one for Firstname and One for LastName
Then use another dataflow to load these both up and join them together either by using INNER JOIN on your query source, or by using a Look-up transformation
The above solution will also work. Following is an alternative.
CREATE TABLE operational(
id int NOT NULL,
name varchar(50) NULL,
isbeginName varchar(5) NOT NULL
) ON [PRIMARY]
GO
insert into operational values (1, 'pratyush', 'TRUE')
insert into operational values (1, 'Singh', 'FALSE')
insert into operational values (2, 'kumar', 'FALSE')
insert into operational values (3, 'sachin', 'TRUE')
CREATE PROCEDURE FullName AS
DECLARE C1 CURSOR LOCAL FOR SELECT DISTINCT id FROM operational ORDER BY id
DECLARE @Rec1id int;
DECLARE @beginname varchar(50);
DECLARE @endname varchar(50);
DECLARE @FullName table (id int, beginname varchar(50), endname varchar(50));
BEGIN
OPEN C1;
WHILE (1 = 1)
BEGIN
SET @beginname = NULL
SET @endname = NULL
FETCH NEXT FROM C1 INTO @Rec1id
IF (@@FETCH_STATUS = -1)
BREAK
SELECT @beginname = name FROM operational WHERE id = @Rec1id AND isbeginName = 'TRUE'
SELECT @endname = name FROM operational WHERE id = @Rec1id AND isbeginName = 'FALSE'
insert into @FullName values (@Rec1id, @beginname, @endname)
END
SELECT ID, ISNULL(beginname, '') AS beginname, ISNULL(endname, '') AS endname FROM @FullName
CLOSE C1
DEALLOCATE C1
END
EXECUTE FullName
IDbeginnameendname
1pratyush Singh
2 kumar
3sachin
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
November 27, 2008 at 8:48 am
Mazharuddin Ehsan (11/27/2008)
You can split the data out into two seperate staging tables, one for Firstname and One for LastName
Then use another dataflow to load these both up and join them together either by using INNER JOIN on your query source, or by using a Look-up transformation
The above solution will also work. Following is an alternative.
CREATE TABLE operational(id int NOT NULL,
name varchar(50) NULL,
isbeginName varchar(5) NOT NULL
) ON [PRIMARY]
GO
insert into operational values (1, 'pratyush', 'TRUE')
insert into operational values (1, 'Singh', 'FALSE')
insert into operational values (2, 'kumar', 'FALSE')
insert into operational values (3, 'sachin', 'TRUE')
CREATE PROCEDURE FullName AS
DECLARE C1 CURSOR LOCAL FOR SELECT DISTINCT id FROM operational ORDER BY id
DECLARE @Rec1id int;
DECLARE @beginname varchar(50);
DECLARE @endname varchar(50);
DECLARE @FullName table (id int, beginname varchar(50), endname varchar(50));
BEGIN
OPEN C1;
WHILE (1 = 1)
BEGIN
SET @beginname = NULL
SET @endname = NULL
FETCH NEXT FROM C1 INTO @Rec1id
IF (@@FETCH_STATUS = -1)
BREAK
SELECT @beginname = name FROM operational WHERE id = @Rec1id AND isbeginName = 'TRUE'
SELECT @endname = name FROM operational WHERE id = @Rec1id AND isbeginName = 'FALSE'
insert into @FullName values (@Rec1id, @beginname, @endname)
END
SELECT ID, ISNULL(beginname, '') AS beginname, ISNULL(endname, '') AS endname FROM @FullName
CLOSE C1
DEALLOCATE C1
END
EXECUTE FullName
IDbeginnameendname
1pratyush Singh
2 kumar
3sachin
This solution requires a Cursor so I suggest not using this, it may work fine for 3 rows of data, but will grind to a halt with larger loads.
Cursors should only be used when absolutely necessary, and with SSIS and Joins available I do not see a reason to recommend a cursor
November 27, 2008 at 9:35 am
steveb is right. Following is the sql for the solution provided by him. In fact I missed this idea earlier. I am just modifying his idea a little. There is no need to have two separate staging tables. We can make do with a single sql statement which uses two derived table[/url] queries 'A' & 'B'.
SELECT ISNULL(A.id, B.id) id, ISNULL(A.name, '') beginname, ISNULL(B.name, '') endname
FROM
(SELECT id, name FROM operational WHERE isbeginName = 'TRUE') A
FULL JOIN
(SELECT id, name FROM operational WHERE isbeginName = 'FALSE') B
ON A.id = b.id
order by id
idbeginnameendname
1pratyushSingh
2 kumar
3sachin
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply