How to Transforming Data
-
hi....
If I have a Temp Table having following values
--------------------------------------
1 A B C
2 D E F
3 D E F
4 I J K
Can I transform it to table
--------------------------------------
1 A B C
2,3 D E F
4 I J K
note: third row is same as second except first field so i put "," There
Thanks a lot in advance
-
Try an aggregate statement using the Group By statement BOL will be great help figuring out how to use it.
-
Is there multiple columns or only one big column there, Kanwar?
Can you give an example with the CREATE TABLE so we can see what the column definitions are?
Julian Kuiters
juliankuiters.id.au -
I have two tables having same structure.
Create table #temp1(
cID varchar(10),
Field1 varchar(10),
Field2 varchar(10),
Field3 varchar(10))
DATA
--------------------------------------
1 A B C
2 D E F
3 D E F
4 I J K
Second Table
Create table #temp2(
cID varchar(10),
Field1 varchar(10),
Field2 varchar(10),
Field3 varchar(10))
Can I transform it to second table (by select from first and insert into second)
DATA
--------------------------------------
1 A B C
2,3 D E F
4 I J K
Thanks for your time
-
A cursor would appear to be the only way. A bit slower but gets the job done OK.
/*
source table creation
create table groupings
(id int, col1(varchar(1), col2(varchar(1), col3(varchar(1))
insert into groupings values (1, 'A','B','C')
insert into groupings values (2, 'D','E','F')
insert into groupings values (1, 'D','E','F')
insert into groupings values (1, 'G','H','I')
dest table creation
create table groupings_output
(allcols varchar(3), ids nvarchar(255))
allcols is a concatenation of all three columns in source table
*/
declare @Groups varchar(3) -- this holds the three columns concatenated
declare @id int -- this holds the unique id on the source table
declare idList cursor for
select id from groupings
OPEN idList
FETCH NEXT FROM idList
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
select @Groups = col1+col2+col3 from groupings where
id = @id
If exists(select * from groupings_output where allcols = @Groups) -- duplicate found
BEGIN
update groupings_output set ids = convert(nvarchar(255),ids)
+ ',' + convert(nvarchar(255),@id) where allcols = @Groups
END
ELSE --- new entry
insert into groupings_output
select col1+col2+col3, id from groupings where id = @id
FETCH NEXT FROM idList
INTO @id
END
CLOSE idList
DEALLOCATE idList
------------------------------
The Users are always right - when I'm not wrong! -
Thanks Stokes thanks a lot.but my problem is bit diffrent
i have to do it without cursors
I have same structure for both the tables
I want to concatenate only IDs of rows having all data same in other columns
Like 2nd and 3rd row in example given above
Thanks
-
If you want to avoid cursors, you can try using a data driven query in dts. See link:
http://doc.ddart.net/mssql/sql2000/html/dtssql/dts_elemtsk1_9w2z.htm
This enables you to check for existience of a record and run an insert.
It will be quicker than using a cursor, but I do not think by much. Whatever you do, you are going to need to process row by row because of the checking of existing records.
------------------------------
The Users are always right - when I'm not wrong! -
You caould use another temp table and a loop (see below) but probably not what you want if you do not want to use a cursor. Otherwise your only hope is a udf.
Create table #temp2(
checkID varchar(10),
cID varchar(10),
Field1 varchar(10),
Field2 varchar(10),
Field3 varchar(10))
insert into #temp2
select min(cID),min(cID),
Field1,Field2,Field3
from #temp1
group by Field1,Field2,Field3
declare @rc int
set @rc = 1
while (@rc > 0)
begin
update t2
set t2.checkID =
(select min(t1.cID) from #temp1 t1
where t1.Field1 = t2.Field1
and t1.Field2 = t2.Field2
and t1.Field3 = t1.Field3
and t1.cID > t2.checkID)
from #temp2 t2
where t2.checkID IS NOT NULL
update #temp2
set cID = cID + ',' + checkID
where checkID IS NOT NULL
set @rc = @@ROWCOUNT
end
select * from #temp2
drop table #temp2
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply