title: Table Rows to columns in sql server
body: <p>I have a header and a detail table. I wants my column names to be repeated for each row.</p>
  
  <p>My header table have data like this </p>
  
  <pre><code>---------------------------------
  ID      Product         Status
  ---------------------------------
  1       P1              S1
  ---------------------------------
  </code></pre>
  
  <p>And my detail have data like this.</p>
  
  <pre><code>---------------------------------
  ID      Spec1   Spec2   Spec3
  ---------------------------------
  1       A       B       C
  1       D       E       F
  1       G       H       I
  ---------------------------------
  </code></pre>
  
  <p>I wants my output like this i.e. table rows to columns</p>
  
  <pre><code> ----------------------------------------------------------------------------
   Product   Status   S11   S12  S13  S21  S22  S23  S31  S32  S33
   ----------------------------------------------------------------------------
   P1         S1      A     D    G    B    E    H    C    F    I
   ----------------------------------------------------------------------------
  </code></pre>
  
  <p>I tried like this..</p>
  
  <pre><code>    SELECT  Hdr.Status,
              Hdr.Product,
              Dtl.Spec1,
              Dtl.Spec2,
              Dtl.Spec3,
              ROW_NUMBER() OVER(ORDER BY Hdr.Root_uuid)   AS  SlNo
      INTO    #Temp
      FROM    TBL_HOME_HEADER_TEST    Hdr
      JOIN    TBL_HOME_Detail_TEST    Dtl
        ON    (Hdr.ID =   Dtl.ID)
  
  
       DECLARE    @RowNo      Int     =   1,
                  @RowCount   Int     =   (SELECT COUNT(*) FROM #Temp),
                  @Field1     Varchar(100),
                  @Field2     Varchar(100),
                  @Field3     Varchar(100),
                  @ProductName    Varchar(100),
                  @StatusName     Varchar(100),
                  @Query      NVarchar(max) = ''  
  
        SELECT    DISTINCT @StatusName = Status,@ProductName = Product
        FROM  #Temp
  
        SET       @Query  =   'SELECT '''+@StatusName+'''AS Status , '''+ 
               @ProductName+''' AS ProductName '  
  
        WHILE @RowCount   &lt;&gt;  0
        BEGIN
  
             SELECT   @Field1 =   Spec1,
                      @Field2 =   Spec2,
                      @Field3 =   Spec3
             FROM #Temp
             WHERE    SlNo    =   @RowNo
  
            SET       @Query  =   @Query + ','''+@Field1+'''AS 
                Filed'+CONVERT(Varchar(10),@RowNo)+'#Name , '''+ @Field2+''' AS 
                Field'+CONVERT(Varchar(10),@RowNo)+'#Name ,'''+@Field3+''' AS 
                Field'+CONVERT(Varchar(10),@RowNo)+'#Name'
  
         SET  @RowNo      =   @RowNo +1
         SET  @RowCount   =   @RowCount   -   1
  
   END
  
   EXECUTE (@Query)
  </code></pre>
  
  <p>It is working, but I want to know, is there any other solution for this?</p>