Data manipulation problem

I have a table of PartNo's. Some of these Parts are equivalent and are
interchangeable.
PartNo - EquivNo
WEBC013 - 1
WEDN033 - 2
WE16067 - 3
3177964 - 1
70330455 -1
I want to create a view that twists the data into the following format:
EquivNo - PartNo1 - PartNo2 - PartNo3 - PartNoX
1 - WEBC013 - 3177964 - 70330455
2 - WEDN033 - Null - Null
3 - WE16067 - Null - Null
There is no limit on the number of equivalent parts, so this view can and
will vary in width.
Can anyone suggest a sensible way of approaching this?
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]CJM
CREATE TABLE Category(PartNo VARCHAR(16), EquivNo INT)
INSERT Category VALUES ('WEBC013', 1)
INSERT Category VALUES ('WEDN033', 2)
INSERT Category VALUES ('WE16067', 3)
INSERT Category VALUES ('3177964', 1)
INSERT Category VALUES ('70330455', 1)
select EquivNo,
max(case cnt when 1 then PartNo else null end) PartNo1,
max(case cnt when 2 then PartNo else null end) PartNo2,
max(case cnt when 3 then PartNo else null end) PartNo3,
max(case cnt when 4 then PartNo else null end) PartNo4
from (select *, (select count(*) cnt
from category c2
where c2.EquivNo=c1.EquivNo and
c2.PartNo>=c1.PartNo ) cnt
from category c1 ) x
group by EquivNo
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:emHqh7xnFHA.764@.TK2MSFTNGP14.phx.gbl...
>I have a table of PartNo's. Some of these Parts are equivalent and are
>interchangeable.
> PartNo - EquivNo
> WEBC013 - 1
> WEDN033 - 2
> WE16067 - 3
> 3177964 - 1
> 70330455 -1
> I want to create a view that twists the data into the following format:
> EquivNo - PartNo1 - PartNo2 - PartNo3 - PartNoX
> 1 - WEBC013 - 3177964 - 70330455
> 2 - WEDN033 - Null - Null
> 3 - WE16067 - Null - Null
> There is no limit on the number of equivalent parts, so this view can and
> will vary in width.
> Can anyone suggest a sensible way of approaching this?
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eYUl$HynFHA.1204@.TK2MSFTNGP12.phx.gbl...
> CJM
>
> select EquivNo,
> max(case cnt when 1 then PartNo else null end) PartNo1,
> max(case cnt when 2 then PartNo else null end) PartNo2,
> max(case cnt when 3 then PartNo else null end) PartNo3,
> max(case cnt when 4 then PartNo else null end) PartNo4
> from (select *, (select count(*) cnt
> from category c2
> where c2.EquivNo=c1.EquivNo and
> c2.PartNo>=c1.PartNo ) cnt
> from category c1 ) x
> group by EquivNo
Uri,
Thanks for that - it works well. It's actually for a colleague of mine,
working in Access, but it ports across ok.
It's a shame that the columns have to be fixed, but it's still very useful
Chris|||Querying with a variable number of columns would involve dynamic SQL or
perhaps looping through a cursor and adding / populating the columns one at
a time, and this would not port well (if at all) to MS Access. Another
option would be use a cross-tab or pivot table in Excel.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OYQvt7ynFHA.1412@.TK2MSFTNGP09.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eYUl$HynFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Uri,
> Thanks for that - it works well. It's actually for a colleague of mine,
> working in Access, but it ports across ok.
> It's a shame that the columns have to be fixed, but it's still very useful
> Chris
>|||"" wrote:
> I have a table of PartNo's. Some of these Parts are equivalent
> and are
> interchangeable.
> PartNo - EquivNo
> WEBC013 - 1
> WEDN033 - 2
> WE16067 - 3
> 3177964 - 1
> 70330455 -1
> I want to create a view that twists the data into the
> following format:
> EquivNo - PartNo1 - PartNo2 - PartNo3 - PartNoX
> 1 - WEBC013 - 3177964 - 70330455
> 2 - WEDN033 - Null - Null
> 3 - WE16067 - Null - Null
> There is no limit on the number of equivalent parts, so this
> view can and
> will vary in width.
> Can anyone suggest a sensible way of approaching this?
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
Hey CJM
There a couple of ways to do this. The best solution depends on the
format you want the result data, whether it is on a web page, in CSV
format, in a temp table etc..
You could build a table of distinct EquivNos ( col01 being EquivNo,
col02 being empty ). Then using a cursor cycle through each row of
this table and use another cursor to add relevant results to col02,
separeted by a suitable delimter ie pipe character ( | ).
Then export this data to a file ( using DTS maybe ) using the same
delimeter to a .txt or .csv file. Then when the file is opened in a
spreadsheet program part numbers will be in separate columns.
Alternatively use ActiveX script to acheive a similar result in
VBScript.
Cheers
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...r />
7696.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=858608