Matrix output

Question

HI,

I have data something like this:

                     

ID Type Code       value
1 term term   1
2 term  term  2
3 Channel CHN Retail
4 Channel CHN Retail90
5 Drug DRG Brand
6 Drug DRG Generic

Output:

i need ALL combinations based ON market

So, a market can have many yr combinations IN a matrix 

OUTPUT ID
3,5,1
3,5,2
4,6,1
4,6,2

Please advise

Answer 1

What is the logic behind getting 3,5,1 or 3,5,2??? didn't get it.. can you explain a little more.

Answer 2

You're not new - no one should have to tell you that it is best to post DDL and sample data to help understand what "something like this" and "matrix" mean.  While I'm at it, you have "something like this" but apparently not EXACTLY that - can you see that your description doesn't make much sense?  In addition, you use the term "market" yet there is no such thing to be found in your data or matrix. 

Based on your matrix, it seems you place special significance on the column "Type" - which is generally concerning - and there also seems to be some relationship between rows since ID 3 should be associated to ID 5 ONLY in your matrix while ID 4 should be associated to ID 6 only.  If you desire a generic solution that does not rely on hardcoded IDs you will need to define that relationship.

Answer 3

;WITH cteChannel AS (
SELECT ID
  FROM @T
 WHERE Code = 'CHN'
), cteDrug AS (
SELECT ID
  FROM @T
 WHERE Code = 'DRG'
), cteTerm AS (
SELECT ID
  FROM @T
 WHERE Code = 'term'
)
SELECT *
  FROM cteChannel, cteDrug, cteTerm

J.