0

I am trying to create a view so that I can create groups of related products from my product table.
I have already built a simple query to get the items and the output look like the following: 

Product | Product2
1234    | 23456
1234    | 4567
1234    | 7895

My goal is to have a view with the following output:

Product | Related1 | Related2 | Related3
1234    | 23456    | 4567     | 7895
23456   | 1234     | 4567     | 7895

so on and so forth.

I'm stuck at the moment trying to understand how to do that.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
MattC
  • 119
  • 2
  • 13
  • What have you tried so far? How many related products could a product have? Is 3 the maximum, or could it be 5, 10, 100, ∞? – Thom A Aug 27 '18 at 16:54
  • Could you explain your desired output ? – Yogesh Sharma Aug 27 '18 at 16:55
  • Also, I *assume* that `Product2` is a `varchar`? That's the only way that `23456` would be before `4567` (Aas `'23456' < '4567`), as we'll need to use a `ORDER BY` to achieve exactly what you want. – Thom A Aug 27 '18 at 16:56
  • Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Thom A Aug 27 '18 at 17:02
  • Please clarify how you get the last row in the result `23456 | 1234 | 4567 | 7895`. Why 23456 is related to 4567 or to 7895, and why you have a rows for Product 23456 but not for 4567 or to 7895 ?!? – Ronen Ariely Aug 27 '18 at 18:11
  • Since your relation seems to be symmetric and transitive, the number of "related" products depends on the data. There is no fix upper bound for the number of related products. That sound like you will need... no, I'm not going to say it. Say that you use a reporting tool and define an "order" of the related products, then I will give you more details. Sorry, but I can't stand this thing that I'm not gonna name and that everybody needs to be able to re-invent reporting. :-) – Wolfgang Kais Aug 28 '18 at 21:07
  • I was able to get what I needed by finding this thread: https://dba.stackexchange.com/questions/132482/two-rows-into-two-columns – MattC Aug 30 '18 at 12:27

0 Answers0