query help-union? 1

[LloydDobler]

need to be able to make the following table structure:

issue#, date, cust, part1, qty1,part2, qty2, part3, qty3...(up to 6 parts)

look like:

issue date cust part qty
222 3/3/07 GM 123 4
222 3/3/07 GM 246 2
222 3/3/07 GM 444 7
223 3/8/07 GM 123 9
224 3/10/07 HONDA xyz 2
224 3/10/07 HONDA zxz 7
225 3/13/07 FORD bt9 9

One record in the DB can have up to 6 parts and 6 quantities selected, issue is UID. How do I show as the example? I've tried UNION but I'm getting 'error converting NVARCHAR to Float' after the 5th part# is added to my statement. Checked all syntax but can't seem to get over this hump. Maybe there's an easier way altogether?

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[mrdenny]

A union all is exactly what you need. This will work fine assuming that the data types of all the part fields is the same and that the data types of all the qty fields is the same. If it's not you'll need to do converting to make it the same.

select [issue#], date, cust, part1 as part, qty1 as qty
from table
union all
select [issue#], date, cust, part2, qty2
from table
union all
select [issue#], date, cust, part3, qty3
from table
union all
select [issue#], date, cust, part4, qty4
from table
union all
select [issue#], date, cust, part5, qty5
from table
union all
select [issue#], date, cust, part6, qty6
from table
order by 1, 2, 3

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LloydDobler]

UNION ALL!!!!! ARRGHHHH. I should have at least thought of that!!! Oh well, you get to pad your stats with that one Denny. thanks!

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."