Query Help!

• MexiCano

  SSC Enthusiast

  Points: 179

  More actions

  July 12, 2006 at 3:35 pm

  #173462

  Hello all

  i have this table (tblPosition):

  CveContacto   CvePaga        CveFactura    CveSistema            CvePricipal

  NULL              NULL             300008634            HH                   2152308001

  NULL              300006900      NULL                   HH                   2152308001

  2                       NULL             NULL                   HH                   2152308001

  NULL              300007777      NULL                   HH                   2152308020

  38                    NULL              NULL                   HH                   2152308020

  NULL              NULL             300001212            HH                   2152308020

  i need a query that give the next result:

  CveContacto   CvePaga        CveFactura      CveSistema            CvePricipal

  2                      300006900      300008634            HH                   2152308001

  38                     300007777      300001212           HH                   2152308020

   

  where CvePrincipal work how pivot

   

  i Hope that can help me,  thanx

   

  sorry my english is very bad

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  July 12, 2006 at 3:57 pm

  #648841

  This will work given there is only one CveContacto, CvePaga, and CveFactura value for each grouping of CvePricipal, CveSistema.

  SELECT SUM(ISNULL(CveContacto,0)),

      SUM(ISNULL(CvePaga,0)),

      SUM(ISNULL(CveFactura, 0)),

      CveSistema,

      CvePricipal

  FROM tblPosition

  GROUP BY CvePricipal, CveSistema

   

  Here is the sample data that I used:

  create table tblPosition ( CveContacto int,

      CvePaga bigint,

      CveFactura bigint,

      CveSistema char(2),

      CvePricipal bigint

  )

  INSERT INTO tblPosition

  select NULL,NULL,300008634,'HH',2152308001 union all

  select NULL,300006900,NULL,'HH',2152308001 union all

  select 2,NULL,NULL,'HH',2152308001 union all

  select NULL,300007777,NULL,'HH',2152308020 union all

  select 38,NULL,NULL,'HH',2152308020 union all

  select NULL,NULL,300001212,'HH',2152308020

   

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• MexiCano

  SSC Enthusiast

  Points: 179

  More actions

  July 12, 2006 at 4:16 pm

  #648845

  Thanx very much John

  for help me very fast

  your solution working very good

  Saludos