Query Output Results

• Barcelona10

  SSCertifiable

  Points: 5373

  More actions

  September 4, 2024 at 12:19 am

  #4453772

  Hi guys.

  I need help  with query results output.

  I have the following table:

  create table MyTable(HH int,F int,T int,Val int)

  insert into MyTable (HH,F,T,Val)

  values(2,6,7,11),

  (2,6,10,1),

  (3,7,6,12),

  (3,6,7,17),

  (4,6,7,15)

  I need to show those F/T values on the same HH. Something like this

  HH    F     T     VAL     F     T    VAL    F    T    VAL

  2        6      7       11       6     10      1

  3       6      7       17       7      6       12     6     10     1

  4       6      7       15

  Thank You in advance

• DesNorton

  SSC-Insane

  Points: 24540

  More actions

  September 4, 2024 at 6:09 am

  #4453822

  A CROSS TAB query will work here.  You just need to know how many sets there are

  WITH cteGroup AS (
    SELECT *
         , rn = ROW_NUMBER() OVER (PARTITION BY mt.HH ORDER BY mt.F, mt.T, mt.Val)
    FROM MyTable AS mt
  )
  SELECT g.HH
       /**********************************************/
       , F   = MAX(CASE WHEN g.rn = 1 THEN g.F   END)
       , T   = MAX(CASE WHEN g.rn = 1 THEN g.T   END)
       , Val = MAX(CASE WHEN g.rn = 1 THEN g.Val END)
       /**********************************************/
       , F   = MAX(CASE WHEN g.rn = 2 THEN g.F   END)
       , T   = MAX(CASE WHEN g.rn = 2 THEN g.T   END)
       , Val = MAX(CASE WHEN g.rn = 2 THEN g.Val END)
       /**********************************************/
       --, F   = MAX(CASE WHEN g.rn = 3 THEN g.F   END)
       --, T   = MAX(CASE WHEN g.rn = 3 THEN g.T   END)
       --, Val = MAX(CASE WHEN g.rn = 3 THEN g.Val END)
  FROM cteGroup AS g
  GROUP BY g.HH
  ORDER BY g.HH;
  

  ---

  How to post data/code on a forum to get the best help.

• Barcelona10

  SSCertifiable

  Points: 5373

  More actions

  September 4, 2024 at 12:39 pm

  #4453959

  DesNorton

  Works for me.

  Thank You so much!

  Regards!