I need assistance with a SELECT query. 1

[elmorro]

I need assistance with a SELECT query. The problem I have is that I need to make two columns out of one column; the 'invtid' column.
What I mean is that under the invtid column we have the same inventory item twice. One of the inventory item has the suffix (fi) which means the item is for inventory.

Here is an example of what the data currently looks like:

invtid descr location
123ABC Computer 1KY
123ABC (fi) Computer 1KY
879HIJ Typewriter 1SC
456EFG Monitor 1SC
456EFG (fi) Monitor 1SC

Here is an example of what I want the data to look like after the query which adds the new column for the inventory items with the (fi) suffix:
invtid invtid_fi descr location
123ABC 123ABC (fi) Computer 1KY
879HIJ Typewriter 1SC
456EFG 456EFG (fi) Monitor 1SC

Thanks in advance,
elmorro

 

[druer]

What would you expect to see if you didn't have a 456EFG record without the (fi) on the end? Or what happens if the '456EFG' and '456EFG (fi)' records contain a different value for description and location? (And the answer they will ALWAYS be the same isn't the right one. Unless you tell me that the input really comes from a screen where the data 454EFG and description, location are captured and there is a checkbox that says "for inventory" and if the user checks it you created both records. Because in that case I'd say "why in the heck aren't you just creating a bit field that indicates 'for inventory.'

I've got very limited time right now, but I think to do what you want (not whether you should or not, but if) you are looking at a self join where you select items that have the FI, and you do a select where the substring fi is found in the id, and you keep the id to the left of it, then you join the two.

Hope it helps,
Dalton

 

[elmorro]

druer,
The records do always have the same description and location. You are correct, creating a bit field to indicate that the inventory item is for inventory would have been the smart thing to do, but I did not write the application nor the database.
Your suggestion about using a self join is exactly what I need to do. I just need to know the syntax. Could you help me with this?

Thank you,
elmorro

 

[unclerico]

try this out

CREATE TABLE #tmp_Inventory
(
	invtid 		varchar(15)	NOT NULL,
	descr		varchar(15)	NOT NULL,
	location	varchar(5)	NOT NULL
)

GO

INSERT INTO #tmp_Inventory
VALUES ('123ABC', 'Computer', '1KY')

INSERT INTO #tmp_Inventory
VALUES ('123ABC (fi)', 'Computer', '1KY')

INSERT INTO #tmp_Inventory
VALUES ('879HIJ', 'Typewriter', '1SC')

INSERT INTO #tmp_Inventory
VALUES ('456EFG', 'Monitor', '1SC')

INSERT INTO #tmp_Inventory
VALUES ('456EFG (fi)', 'Monitor', '1SC')

 
SELECT a.invtid, b.invtid invtid_fi, a.descr, a.location
  FROM #tmp_Inventory a
  LEFT JOIN (SELECT invtid
               FROM #tmp_Inventory
              WHERE invtid LIKE '%(fi)') b
    ON a.invtid = LEFT(b.invtid, 6)
 WHERE a.invtid NOT LIKE '%(fi)'

i will be amazed if some of the gurus don't have another more efficient, flexible solution than this but give it a try and see if it works ok for you. let me know if you need an explanation of the code.

 

[elmorro]

unclerico, YOU RULE!!!!

That did it!

Thanks a bunch,
elmorro

 

[druer]

Glad Unclerico was able to help you out.

 

[elmorro]

Thanks druer.

elmorro