Post reply

How to get row values as column values in a table

  • May 22, 2017 at 6:38 am

    #323711

    Hi

    I am having table 'tblcalculate'  with field names

    1)Hardness ---numeric,

    2)tower ---int

    I am having values

    Hardness  towers
    1.2     1

    2.2     2

    3.2     1

    5.2     3

    6.2     2

    Now I want to get as output

    ---------------------

    1    2   3  (towers) column head

    -------------------------

    1.2   2.2  5.2 (Hardness)
    3.2   6.2     (Hardness)

    Please help me to get output as above
    Thanks and Regards
    Ram

  • May 22, 2017 at 6:50 am

    #1943549

    Are you trying to Pivot your data?  https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot

    Rob

  • May 22, 2017 at 3:30 pm

    #1943668

    Try:

    DECLARE @tblcalculate table( Hardness numeric(5,1), Tower int);
    INSERT @tblcalculate
            ( Hardness, Tower)
        VALUES
            ( 1.2, 1 )
          , ( 2.2, 2 )
          , ( 3.2, 1 )
          , ( 5.2, 3 )
          , ( 6.2, 2 );

    SELECT p.Reading
         , [1]
         , [2]
         , [3]
    FROM (SELECT Hardness, Tower, Row_Number() OVER (PARTITION BY Tower ORDER BY Tower, Hardness) Reading FROM @tblcalculate) AS t
    PIVOT
       (
       Sum(t.Hardness)
       FOR
          t.Tower IN
             ( [1]
             , [2]
             , [3] )
       ) p
       ORDER BY p.Reading;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply