SQL to find all combinations of fields populated

• scottcabral

  Default port

  Points: 1495

  More actions

  May 25, 2016 at 12:19 pm

  #316733

  Hi,

  I have the folloiwing table:

  DECLARE @Table TABLE(

  CMBAmt decimal(18,2) NULL,

  PDAmt decimal(18,2) NULL,

  TEAmt decimal(18,2) NULL,

  MinCMBAmt decimal(18,2) NULL,

  MinPDAmt decimal(18,2) NULL,

  MinTEAmt decimal(18,2) NULL,

  MaxCMBAmt decimal(18,2) NULL,

  MaxPDAmt decimal(18,2) NULL,

  MaxTEAmt decimal(18,2) NULL

  );

  there are a few hundred thousand rows in this table with different combinations of amount fields filled in. I was wondering if there was any way to write a script to spit out all of the different combinations of what amount fields have values > 0 on all rows.

  So I would want the following:

  CmbAmt, PDAmt, TEAmt - combo 1

  MinCmbAmt, MaxCmbAmt - combo 2

  MaxPdAmt, MaxTEAmt - combo 3

  CmbAmt - combo 4

  etc...

  thanks

  Scott

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  May 25, 2016 at 12:24 pm

  #1881424

  Look up DISTINCT in Books Online.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• scottcabral

  Default port

  Points: 1495

  More actions

  May 25, 2016 at 12:29 pm

  #1881429

  wow, i way overlooked this one. thanks

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  May 25, 2016 at 12:40 pm

  #1881433

  Welcome. Sometimes our brains don't work like we wished they would. 😀

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service