title: How to apply 3 values for 1 row to 3 rows with each value?
body: <p>I have a number of ROWS that have 1 to 6 values. For example:</p>
  
  <pre><code>Param1: A|B|C|D
  Param2: B|Y
  Param3: A
  </code></pre>
  
  <p>I need to transform it like this:</p>
  
  <pre><code>Param1: A
  Param1: B
  Param1: C
  Param1: D
  Param2: B
  Param2: Y
  Param3: A
  </code></pre>
  
  <p>Well, I guess a pivot-unpivot might work, but there are a lot of conditions and fields I need to get. Also, I have a View that divides all values and counts them. In the top example it will return dataset like this:</p>
  
  <pre><code>A 2
  B 2
  C 1
  D 1
  Y 1
  </code></pre>
  
  <p>Here is my own example, which is working alright on a few records and works very badly with more than 100000 rows.</p>
  
  <p>Initial story is about this. I have some objects(obj), each has its params(prm), which have its values (val). So, as you see, each object is like a tree, which I need to expand.
  Here is a simulation:</p>
  
  <pre><code>DECLARE @x TABLE
      (
        prm INT ,
        iin VARCHAR(20) ,
        oout VARCHAR(20)
      ) 
  INSERT  INTO @x
  VALUES  ( 1, 'A/B/C', 'A' )
  INSERT  INTO @x
  VALUES  ( 1, 'A/B/C', 'B' )
  INSERT  INTO @x
  VALUES  ( 1, 'A/B/C', 'C' )
  INSERT  INTO @x
  VALUES  ( 3, 'D', 'D' )
  INSERT  INTO @x
  VALUES  ( 2, 'R/G', 'R' )
  INSERT  INTO @x
  VALUES  ( 2, 'R/G', 'G' )
  
  DECLARE @y TABLE
      (
        obj INT ,
        prm INT ,
        val VARCHAR(20)
      ) 
  INSERT  INTO @y
  VALUES  ( 10, 1, 'A/B/C' )
  INSERT  INTO @y
  VALUES  ( 10, 2, 'R/G' )
  INSERT  INTO @y
  VALUES  ( 10, 3, 'D' )
  INSERT  INTO @y
  VALUES  ( 20, 2, 'R/G' )
  INSERT  INTO @y
  VALUES  ( 20, 3, 'D' )
  
  DECLARE @z TABLE
      (
        id INT ,
        obj INT ,
        prm INT ,
        val VARCHAR(20)
      ) 
  INSERT  INTO @z
  VALUES  ( 1, 10, 1, NULL ) 
  INSERT  INTO @z
  VALUES  ( 2, 10, 1, NULL ) 
  INSERT  INTO @z
  VALUES  ( 3, 10, 1, NULL ) 
  INSERT  INTO @z
  VALUES  ( 4, 10, 2, NULL ) 
  INSERT  INTO @z
  VALUES  ( 5, 10, 2, NULL ) 
  INSERT  INTO @z
  VALUES  ( 6, 10, 3, NULL ) 
  INSERT  INTO @z
  VALUES  ( 7, 20, 2, NULL ) 
  INSERT  INTO @z
  VALUES  ( 8, 20, 2, NULL ) 
  INSERT  INTO @z
  VALUES  ( 9, 20, 3, NULL )
  </code></pre>
  
  <p>And decision:</p>
  
  <pre><code>;
  WITH    a AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY prm ORDER BY prm ) n ,
                      *
             FROM     @x
           ),
      b AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY obj, prm ORDER BY obj, prm ) n ,
                      *
             FROM     @z
           )
  UPDATE  b
  SET     b.val = a.oout
  FROM    b
          INNER JOIN @y y ON y.obj = b.obj
                             AND y.prm = b.prm
          INNER JOIN a ON a.n = b.n
                          AND a.prm = b.prm
                          AND y.val = a.iin
  SELECT  *
  FROM    @z
  </code></pre>
  
  <p><strong>@y</strong> table - is a table with arguments like the first example, where Param1,Param2 is 1,2 ets on column <em>prm</em>, concerning some object in <em>obj</em><br>
  <strong>@z</strong> table - is simulation with <em>val</em> set to null, which represents, what params should be filled with values<br>
  <strong>@x</strong> table - is a simulation of dividing of values, that should be applied to <strong>@y</strong> table, replacing the null values of the <strong>@z</strong> table with actual ranked values.</p>
  
  <p>Is there a better way to do this?</p>