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. 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...
['https://Stackoverflow.com/questions/1017598', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/65223/']
The various options for aggregate string concatention in SQL Server were summed up by Anith Sen in this post: <http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/>

Usually the best option is the XML PATH trick.
you could write a scalar value function that would contain a subquery in which it would perform the concatenation, or you could try coalesce, [example here](http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string)