Another Pivot Table
-
Hi all,
I have this kind of data :
Date ItemA QtyA ItemB QtyB ItemC QtyC
----- ----- ----- ----- ----- ----- -----
20051010 It-X 1 It-Y 10 It-Z 5
20051011 It-W 2 It-V 5
20051012 It-V 2 It-X 2
How to make the Column ItemXXX to become Rows... for ex, like this :
Date Item Qty
----- ----- -----
20051010 It-X 1
20051010 It-Y 10
20051010 It-Z 5
20051011 It-W 2
20051011 It-V 5
20051012 It-V 2
20051012 It-X 2
Thanks in advance,
-
SELECT [Date],ItemA AS [Item],QtyA AS [Qty]
FROM
UNION
SELECT [Date],ItemB,QtyB
FROM
UNION
SELECT [Date],ItemC QtyC
FROM
How many Item's are there, is it a fixed number?
Can Item/Qty be repeated for a [Date]?
Far away is close at hand in the images of elsewhere.
Anon. -
Hi, Thanks,
yes the columns is fixed, 10 columns to be frankly, so the column are ItemA..J and QtyA..J
sorry, what do you mean -> Item.Qty be repeated for a [Date] ?
-
Hi David,
I got your point,
so I just run T-SQL like this :
select [Date], ItemA as Item, QtyA as Qty
from
union
select [Date], ItemB as Item, QtyB as Qty
from
where ItemB is not null
union
select [Date], ItemC as Item, QtyC as Qty
from
where ItemC is not null
...
..
.
union
select [Date], ItemJ as Item, QtyJ as Qty
from
where ItemJ is not null
Done. Thanks man.. why didn't I think of that... hehe..
-
what do you mean -> Item.Qty be repeated for a [Date] UNION will suppress duplicates as in 'DISTINCT'
UNION ALL does not
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply