Data manipulation problem

12 Aug 2005 9:19 AM

CJM

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?

--
cjmnew***@REMOVEMEyahoo.co.uk
[remove the obvious bits]

12 Aug 2005 9:41 AM

Uri Dimant

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
Show quote

"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?
>
> --
> cjmnew***@REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>

12 Aug 2005 11:13 AM

CJM

Show quote

"Uri Dimant" <u***@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

12 Aug 2005 1:11 PM

JT

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.

Show quote

"CJM" <cjmnews04@newsgroup.nospam> wrote in message
news:OYQvt7ynFHA.1412@TK2MSFTNGP09.phx.gbl...
>
> "Uri Dimant" <u***@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
>

12 Aug 2005 2:35 PM

AJL236

"" wrote:
Show quote

> 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?
>
> --
> cjmnew***@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 EquivNo’s ( 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-Data-manipulation-problem-ftopict247696.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=858608