Query Assistance

Question

Hi,

I have table A that has columns OrderID (PK) & Name. I also have a second table B that has ImageID (PK), OrderID (FK), FileName

For each row in Table A there is multiple rows in Table B  (i.e. each order has multiple images)

When I go to query tables (joined on orderid) on an order that has say 4 images. I get 4 columns with all the data. I was hoping to get 1 columns with all the data then 3 columns with just TableB.FileName

Would appreciate any assistance on what I need to research to achieve this.

Thanks in advance.

Todd

Answer 1

IT sounds like you want to Pivot the data - however as there may be an unknown number of images per Order, you'll need to do this using dynamic sql.

Follow the steps in this article and you should be able to work out what you need: http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

---

Martin Cairney SQL Server MVP

Answer 2

Good day tango,

Instead of describe your table structure and data please post:

1) queries to CREATE the table(s)
2) Queries to INSERT sample data.
3) The desired result given the sample.
4) A short description of the business rules.
5) Which version of SQL Server you are using.

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]