April 11, 2008 at 4:31 pm
I couold use some help with a case statement. I need a query that takes rows of data and makes them into columns (basic right?) Well then have at it:
Currently I Have :
Product_ID MIN_QTY MAX QTY PRICE
1 1 10 $100
1 11 100 $90
2 1 5 $200
2 6 55 $190
3 1 200 $300
3 201 432 $290
What I Need is
Product_ID MIN_QTY1 MAX QTY1 PRICE1 MIN_QTY2 MAX QTY2 PRICE2
1 1 10 $100 11 100 $90
2 1 5 $200 6 55 $190
3 1 200 $300 201 432 $290
Can someone help with a SQL query to do this? I would be grateful.
April 12, 2008 at 12:01 am
declare @Tmp table(Row Int Identity,pid int,minqty1 float,maxqty1 float,price1 float,minqty2 float default 0,maxqty2 float default 0,price2 float default 0)
declare @Tmp1 table(RowNum Int Identity,pid int,minqty float,maxqty float,price float)
insert into @Tmp1 values(1,1,10,100)
insert into @Tmp1 values(1,11,100,90)
insert into @Tmp1 values(2,1,5,200)
insert into @Tmp1 values(2,6,55,190)
insert into @Tmp1 values(3,1,200,300)
insert into @Tmp1 values(3,201,432,290)
insert into @Tmp
select pid,minqty,maxqty,price,0,0,0 from @Tmp1 where RowNum%2<>0
select * from @Tmp
declare @pid int
declare @min-2 float
declare @max-2 float
declare @price float
DECLARE pCursor CURSOR FOR
SELECT pid,minqty,maxqty,price FROM @Tmp1 where Rownum%2=0
OPEN pCursor
FETCH NEXT FROM pCursor INTO @pid,@min,@Max,@price
WHILE @@FETCH_STATUS = 0
BEGIN
update @Tmp set minqty2=@min,maxqty2=@Max,price2=@price where @pid=pid
FETCH NEXT FROM pCursor INTO @pid,@min,@Max,@price
END
CLOSE pCursor
DEALLOCATE pCursor
select pid,minqty1,maxqty1,price1,minqty2,maxqty2,price2 from @Tmp
U can use the data from your first table directly instead of declaring @Tmp1 and inserting the values as i have done.
hope this helps.
but using cursors when there are large number of rows might be time consuming.
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 12, 2008 at 6:44 am
Oh sorry - this is for 200,000 rows. This solution is interesting but would take too long. I was thinking along the pivot lines of SUM and CASE, but was having a hard time figuring out how to accomodate the randomness in the quantity columns. There is no regularity there..........any ideas?
April 12, 2008 at 9:49 am
I found this but it does need seem to work when I run something similar for my needs in MSSQL 2000
> The SELECT command will return:
> 2002
> 2003
> 2004
> 2005
>
> So I would like to construct following select:
>
> select T1.WEEK,
> SUM (case T1.YEAR when '2002' then T1.PRICE else 0 END) Y_02,
> SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
> SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
> SUM (case T1.YEAR when '2005' then T1.PRICE else 0 END) Y_05
> from T1
> group by T1.week
>
> which will return pivot table with 5 columns:
> WEEK, Y_02, Y_03, Y_04, Y_05,
Still struggling........................
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply