title: Get columns from joined tables as rows instead of adjacent columns
body: <p>I have Three tables A_PRODUCT, A_MODEL, and A_AREA. These tables can be joined using PRODUCT_ID. I want to get an output such that a row from A_PRODUCT table is followed by rows from A_MODEL table followed by rows from A_AREA table for a product. Following is the SQL to create sample data:</p>
  
  <pre><code>CREATE TABLE A_PRODUCT
  (
  PRODUCT_ID NVARCHAR(30),
  PODUCT_NAME NVARCHAR(30),
  );
  
  INSERT INTO A_PRODUCT (PRODUCT_ID, PODUCT_NAME)
  VALUES ('P-1', 'LONG-SLEEVE LOGO JERSEY');
  
  INSERT INTO A_PRODUCT (PRODUCT_ID, PODUCT_NAME)
  VALUES ('P-2', 'GLASSES');
  
  
  CREATE TABLE A_MODEL
  (
  MODEL_ID NVARCHAR(30),
  MODEL_NAME NVARCHAR(30),
  PRODUCT_ID NVARCHAR(30)
  );
  
  INSERT INTO A_MODEL (MODEL_ID, MODEL_NAME, PRODUCT_ID)
  VALUES ('M-1', 'OLD JERSEY', 'P-1');
  
  INSERT INTO A_MODEL (MODEL_ID, MODEL_NAME, PRODUCT_ID)
  VALUES ('M-2', 'RAY-BAN', 'P-2');
  
  INSERT INTO A_MODEL (MODEL_ID, MODEL_NAME, PRODUCT_ID)
  VALUES ('M-3', 'ARMANI', 'P-2');
  
  CREATE TABLE A_AREA
  (
  AREA_ID NVARCHAR(30),
  AREA_NAME NVARCHAR(30),
  PRODUCT_ID NVARCHAR(30)
  );
  
  INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
  VALUES ('A-1', 'SOUTH', 'P-1');
  
  INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
  VALUES ('A-2', 'NORTH', 'P-1');
  
  INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
  VALUES ('A-3', 'EAST', 'P-1');
  
  INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
  VALUES ('A-4', 'WEST', 'P-1');
  
  INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
  VALUES ('A-5', 'SOUTH-WEST', 'P-2');
  </code></pre>
  
  <p>My expected output is:</p>
  
  <pre><code>|PRODUCT|P-1|LONG-SLEEVE LOGO JERSEY|
  |MODEL|M-1|OLD JERSEY|
  |AREA|A-1|SOUTH|
  |AREA|A-2|NORTH|
  |AREA|A-3|EAST|
  |AREA|A-4|WEST|
  |PRODUCT|P-2|GLASSES|
  |MODEL|M-2|RAY-BAN|
  |MODEL|M-3|ARMANI|
  |AREA|A-5|SOUTH-WEST|
  </code></pre>
  
  <p>I have tried the following query but it is not giving me the desired output:</p>
  
  <pre><code>;WITH CTE AS 
  (
  SELECT CONCAT('|PRODUCT|', P.PRODUCT_ID,'|',P.PODUCT_NAME,'|') AS Col1,
          CONCAT('|MODEL|', M.MODEL_ID,'|',M.MODEL_NAME,'|') AS COl2,
          CONCAT('|AREA|', A.AREA_ID,'|',A.AREA_NAME,'|') AS COl3      
      FROM A_PRODUCT P
      JOIN A_MODEL M
      ON P.PRODUCT_ID = M.PRODUCT_ID
      JOIN A_AREA A
      ON P.PRODUCT_ID = A.PRODUCT_ID
   )
   SELECT Result
   FROM CTE
   CROSS APPLY (VALUES (Col1,'Col1'),(Col2,'Col2'),(Col3,'Col3')
                )DT (Result,Names);
  </code></pre>