November 17, 2013 at 3:43 am
Hi,
I have a table which has the data shown in the attachment(Table Data). I need the expected result. Please suggest.
Cheers
Jim
November 17, 2013 at 3:58 am
The solution is easy: it's called "Normalization"!
Move the column [Inventory] to a separate table with a reference to [Item] and move the rows with non-blank values.
Then it's just a simple inner Join.
For a coded answer please provide table def, sample data and expected result in a ready to use format as described in the first link in my signature.
November 17, 2013 at 5:16 am
Hi Lutz,
The requirement is something like this, there is a grouping on column Item. Each Item should have an Inventory value. This should be picked from the set of Items. For ex, for PC, Inventory value is Inventory(Only one row in the PC set will have the inventory column value). So all the PCs should have a value "Inventory". And there should not be any item without supplier. For ex, Row 3 is not having a supplier value. This should be removed.
Hence, the result set for PC is 3 rows. The same is applicable for other items like printer,etc.. Feel free to let me know for more clarifications.
Unfortunately, I can not create tables. Please suggest, with the existing table how can this be achieved. Thanks.
Cheers
Jim
November 17, 2013 at 6:06 am
If you can't change the designe then you'll need to apply the same concept using subqueries (or cte's).
You've already described the WHERE condition of the two subqueries involved. All that's left is to write the code.
For a coded answer please ... oh wait, I already wrote that....
November 17, 2013 at 9:47 am
You could try the following, but remember it is completely untested as there was no DDL (CREATE TABLE statement) for the table, or sample data (in the form of INSERT INTO statements).
with HeaderInfo as (
select
Item,
Description,
Inventory
from
UnNormalizedTable
where
Inventory <> '' -- assumes this field is blank (not null) in detail rows
), DetailInfo as (
select
Item,
Description,
Sourcing,
Distribution,
Rule,
Disabled,
Org,
Supplier,
Site,
Rank,
AllocationPercentage,
EndDate
from
UnNormalizedTable
where
Inventory = ''
)
select
di.Item,
di.Description,
hi.Inventory,
di.Sourcing,
di.Distribution,
di.Rule,
di.Disabled,
di.Org,
di.Supplier,
di.Site,
di.Rank,
di.AllocationPercentage,
di.EndDate
from
HeaderInfo hi
inner join DetailInfo di
on (hi.Item = di.Item and
hi.Description = di.Description);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply