multiple vendors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

multiple vendors

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-11-24 : 16:10:44

[code]CREATE TABLE parts2
(
IDxxx int IDENTITY(1,1) NOT NULL,
part varchar(35),
descrip varchar(255),
vendor varchar(6)
)

INSERT INTO parts2 (part, descrip, vendor)
VALUES(apart,adescript, avendor)

INSERT INTO parts2 (part, descrip, vendor)
VALUES(apart,adescript,bvend)

INSERT INTO parts2 (part, descrip, vendor)
VALUES(cpart,cdescript,avend)

INSERT INTO parts2 (part, descrip, vendor)
VALUES(dpart,ddescript,avend)

INSERT INTO parts2 (part, descrip, vendor)
VALUES(dpart,ddescript,dvend)

INSERT INTO parts2 (part, descrip, vendor)
VALUES(dpart,ddescript,evend)

Desired result

PART DESCRIP VEND1 VEND2 VEND3

apart adescript avend bvend NULL
cpart cdescript avend NULL NULL
dpart ddescript avend dvend evend[/code]

I wish someone would start an Official XML Rant Thread.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-24 : 16:25:06

[code]select
part,
descrip,
max(case when vendor_id = 'a' then vendor else null end) VEND1,
max(case when vendor_id IN ('b','d') then vendor else null end) VEND2,
max(case when vendor_id = 'e' then vendor else null end) VEND3
from
(
select left(part,1) part_id,part, descrip, left(vendor,1) vendor_id, vendor
from parts2
) d
group by part,descrip[/code]

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-11-24 : 16:45:32

Thanks ehorn, I gave bad sample data
Vendor can be any varchar(6), new ones frequently added.
Also part and descript can be anything as well


INSERT INTO parts2 (part, descrip, vendor)
VALUES('apart','adescript','jones')

INSERT INTO parts2 (part, descrip, vendor)
VALUES('apart','adescript', 'jill')

INSERT INTO parts2 (part, descrip, vendor)
VALUES('cpart','cdescript','jones')

INSERT INTO parts2 (part, descrip, vendor)
VALUES('dpart','ddescript','jones')

INSERT INTO parts2 (part, descrip, vendor)
VALUES('dpart','ddescript','jane')

INSERT INTO parts2 (part, descrip, vendor)
VALUES('dpart','ddescript','ed')

Desired result

PART DESCRIP VEND1 VEND2 VEND3

apart adescript jones jill NULL
cpart cdescript jones NULL NULL
dpart ddescript jones jane ed

I wish someone would start an Official XML Rant Thread.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-24 : 17:22:48

[code]
select
part,
descrip,
max(case when vend_id = 1 then vendor else null end) VEND1,
max(case when vend_id = 2 then vendor else null end) VEND2,
max(case when vend_id = 3 then vendor else null end) VEND3
from
(
select top 100 percent
IDxxx,
part,
descrip,
vendor,
(select count(1)+1 from parts2 p2 where p2.part = p.part and p2.IDxxx < p.IDxxx) as vend_id
from
parts2 p
order by
IDxxx, part
) d
group by part,descrip[/code]

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-11-24 : 18:28:47

thanks again. I have to try tomorrow and see if that fits what I need to get out of the real data. I am/was so stuck, the right approach to this seems to have become a victim of a destroyed part of the brain, don't know what to call it either, can't wait!

I wish someone would start an Official XML Rant Thread.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-11-26 : 09:12:26

Thanks ehorn it worked good against the real data.

I wish someone would start an Official XML Rant Thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources