Aggregate properties

Transact-SQL
1

Hi Guys!

I am using MS SQL. I have for instance three tables:
Items, ItemProp and Properties.
Items contains the individual items:
ITEM_ID,
ITEM_NAME
and other fields

ItemProp table:
ITEM_PROP_ID
ITEM_ID -> this is the primary key in Items table
PROP_ID

Properties table:
PROP_ID -> this is the foreign key in ItemProp table
PROP_NAME

Suppose that there are 2 items in items table:
1, First item
2, Second item

And there are 2 records for the first item in ItemProp and 3 items for second item:
ITEM_ID, PROP_ID
1, 1
1, 2
2, 2
2, 4
2, 5

Suppose the Properties contains these:
PROP_ID, PROP_NAME
1, A
2, B
3, C
4, D
5, E

My goal is something like this:
DEMO_ID, DEMO_NAME, PROP_NAME
1, First, A B
2, Second, B D E

So I would like to see onyla that amount of record as the amount of items (2),
and see the properties collected under one column.

Thanks in advance!

2

Look up GROUP BY and aggregate functions.

3

But as I know there is no aggregate function for text type, right?
Item, Prop
First, A
First, B
Second, B
Second, D
Second, E

Select item, prop from X group by item, prop "< + aggregate somehow>"

->
First, A B
Second, B D E

4

MAX and MIN work for text types

5

Tnx!

I used something like this at the and:

SELECT
[Item],
STUFF((
SELECT ', ' + [Prop] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (Item = Results.Item)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1, 1,'') AS NameValues
FROM #YourTable Results
GROUP BY Item