I have a need for a "run once" query (so performance isn't that critical), using tables similar to these (simplified versions of the actual):
CREATE TABLE #Items
( ItemPK int not null primary key
,ItemDescription varchar(25) not null
)
INSERT INTO #Items VALUES (1,'rock')
INSERT INTO #Items VALUES (2,'wood')
INSERT INTO #Items VALUES (3,'plastic')
CREATE TABLE #ItemTypes
( ItemType char(1) not null primary key
,ItemTypeDescription varchar(25) not null
)
INSERT INTO #ItemTypes VALUES ('A','Type A')
INSERT INTO #ItemTypes VALUES ('B','BBBBB')
INSERT INTO #ItemTypes VALUES ('C','Color')
CREATE TABLE #ItemInfo
( InfoPK int not null primary key identity(1,1)
,ItemPK int not null foreign key references #ListOfItems.ItemPK
,ItemType char(1) not null foreign key references #ItemTypes.ItemType
,ItemValue varchar(10) not null
)
INSERT INTO #ItemInfo VALUES (1,'A','hard')
INSERT INTO #ItemInfo VALUES (1,'A','natural')
INSERT INTO #ItemInfo VALUES (1,'B','cold')
INSERT INTO #ItemInfo VALUES (1,'C','gray')
INSERT INTO #ItemInfo VALUES (2,'B','grain')
INSERT INTO #ItemInfo VALUES (2,'B','brown')
INSERT INTO #ItemInfo VALUES (3,'A','flexible')
INSERT INTO #ItemInfo VALUES (3,'A','colorful')
INSERT INTO #ItemInfo VALUES (3,'A','unnatural')
INSERT INTO #ItemInfo VALUES (3,'C','waterproof')
You'll get warnings about FKs and temp tables, I just show them for clarity, or you can remove the "#" to make permanent tables...
when I run this query:
SELECT
i.ItemDescription
,t.ItemTypeDescription
,o.ItemValue
FROM #Items i
LEFT OUTER JOIN #ItemInfo o ON i.ItemPK=o.ItemPk
LEFT OUTER JOIN #ItemTypes t ON o.ItemType=t.ItemType
I get this output:
ItemDescription ItemTypeDescription ItemValue
------------------------- ------------------------- ----------
rock Type A hard
rock Type A natural
rock BBBBB cold
rock Color gray
wood BBBBB grain
wood BBBBB brown
plastic Type A flexible
plastic Type A colorful
plastic Type A unnatural
plastic Color waterproof
However, I need output like:
ItemDescription CombinedDescription
------------------------- -----------------------------------------------
rock Type A: hard, natural; BBBBB: cold; Color: gray
wood BBBBB: grain, brown
plastic Type A: flexible, colorful, unnatural; Color: waterproof
(3 row(s) affected)
thanks...