sql server - a pivot table kinda group by -

sql server - a pivot table kinda group by -

i have 3 tables, table1: containing client details, table2: containing uniq codes, table3: containing 1 row every single item purchased. number of items purchased 1 n.

table1: info imported every few minutes business goes along id, account, emailaddress, transactionid

table2: id, uniqcode

table3: info imported every few minutes business goes along id, account, transactionid, item

i bring together on incrementing id's give me likes this: select * table1 bring together table3 on table1.transactionid = table3.transactionid bring together table2 on on table3.id = table2.id

this results in:

emailaddress1, item1, uniqcode1 emailaddress1, item2, uniqcode2 emailaddress1, item3, uniqcode3 emailaddress2, item1, uniqcode4 emailaddress2, item2, uniqcode5

i end result emailaddress1, item1, uniqcode1, item2, uniqcode2, item3, uniqcode3 emailaddress2, item1, uniqcode4, item2, uniqcode5, null, null

why hassle? because our bulkmailer can cope 'flat' tables , i'd send 1 email containing number of uniq codes (they'll 1 every item purchased) entitled instead of number of emails containing single code. codes provided 3rd party. needs done in mssql. view used allow bulkmailer think it's actualy looking @ single table.

how can this???

sql-server