How to tranpose data? 1

[aksm47]

Hi all,

I would like to know how to effectively transform
<TABLE 1> into <TABLE 2>

<TABLE 1>
ID | PRICE
1 | $2
1 | $3
1 | $4
2 | $5
2 | $6
3 | $7



<TABLE 2>
ID | PRICE_1 | PRICE_2 | PRICE_3 *
1 | $2 | $3 | $4
2 | $5 | $6 | NULL
3 | $7 | NULL | NULL

*The number of columns will depends on the count(*)
size of the same ID.

I am dealing with 700,000 records, so I cannot do them
manually.

Thank you very much for your help and any advice will
be appreciated.

Thanks.

Drew

 

[Pattycake245]

I made this stored procedure and tested it with your data. The only problem is that it contains 2 cursors. I know cursors are not advisable. So, on your 700,000 records this may take time. Anyway, try it if you wish

create procedure dbo.maketable

AS

declare @count char(3), @count2 char(3), @SQLstring nvarchar(1000), @fieldid nvarchar(3), @idcount int,
@insertlist nvarchar(1000), @price nvarchar(10)

--creates a temp table to basically find the record with
--the most prices so the proc knows how big to make
--the table

select fieldid, count(*) "idcount" into #testtable from worktable
group by fieldid

select @count=max(idcount) from #testtable

--Builds the table
set @count2=1
select @SQLstring = N'create table dbo.worktable2(fieldid nvarchar(5), '
while (@count2 < @count)
begin
select @SQLstring = @SQLstring + 'price_'+ltrim(rtrim(@count2))+' money,'
select @count2 = @count2 + 1
end
if @count2 = @count
begin
select @SQLstring = @SQLstring + 'price_'+ltrim(rtrim(@count2))+' money)'
end

execute sp_executesql @SQLstring

set @count2=0


declare insertcursor cursor for
select fieldid, count(*) idcount from worktable group by fieldid

open insertcursor
fetch next from insertcursor into @fieldid, @idcount

while @@fetch_status =0
begin


declare valuescursor cursor for
select price from worktable where fieldid = @fieldid

--builds the first part of the insert statement
set @count2=1
select @insertlist = N'insert into dbo.worktable2(fieldid'
while (@count2 < @idcount)
begin
select @insertlist = @insertlist + ',price_'+ltrim(rtrim(@count2))+''
select @count2 = @count2 + 1
end
if @count2 = @idcount
begin
select @insertlist = @insertlist + ',price_'+ltrim(rtrim(@count2))+') values('+@fieldid+''
end

--builds the second part of insert statement
open valuescursor
fetch next from valuescursor into @price

while @@fetch_status =0
begin
select @insertlist = @insertlist + ','+@price+''
fetch next from valuescursor into @price
end

close valuescursor
deallocate valuescursor
select @insertlist = @insertlist + ')'
execute sp_executesql @insertlist

--resets the string variable for next record
set @insertlist=''
fetch next from insertcursor into @fieldid, @idcount
end

close insertcursor
deallocate insertcursor

 

[tektipdjango]

I think I've found a solution without cursors.
I Haven't SQL server at the moment, but if anyone can test it

--variables

declare @rank char(3),@maxRank char(3),@SQLcreateTable2 nvarchar(1000)

--calculation of maximum number of prices (maxRank)

select @maxRank=max(idcount) from
(select id, count(*) as idcount
from table1
group by id) as testtable

--create a work table to set the 'rank' of the price for one item on asc order

create table workTable1
(
id nvarchar(5),
rank char(3),
price money
)

select into workTable1
id,0,price
from table1

--create Table2 which is the final table, with the correct number of price columns (maxRank)

set @rank=1
select @SQLcreateTable2 = N'create table dbo.Table2(id nvarchar(5), '
while (@rank< @maxRank)
begin
select @SQLcreateTable2 = @SQLcreateTable2 + 'price_'+ltrim(rtrim(@rank))+' money,'
select @rank= @rank+ 1
end
if @rank= @maxRank
begin
select @SQLcreateTable2 = @SQLcreateTable2 + 'price_'+ltrim(rtrim(@maxRank))+' money)'
end

Exec @SQLcreateTable2

--make a loop to insert the lower price for each itm, the the second one,... then the last one

set @rank=1
while (@rank<=@maxrank))
begin

--select each item with its minimum price, insert this price in the price_1 column then ...

EXEC 'update table2
set price_' +ltrim(rtrim(@rank))+ ' = t.price
from
(workTable1 t
inner join
(select
id,min(price) as minPrice
from
workTable1 t1
where rank=0) as tableMin
on t.id=tableMin.id and t.price=tableMin.minPrice)'
end

--Do the trick update the workTable1, the goal is to have on the next loop not the first lower price but the second one and so on ...

UPDATE workTable1
SET workTable1.rank = @rank
from
(workTable1 t
inner join
(select
id,min(price) as minPrice
from
workTable1 t1
where num=0) as tableMin
on t.id=tableMin.id and t.price=tableMin.minPrice)

this is the END

end

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[aksm47]

Thank you very much. I have tried both methods and they worked great. thanks.

 

[tektipdjango]

Great, did you compare the speed of both?

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​