Loading ...
Sorry, an error occurred while loading the content.

query multiple fields results in one column.

Expand Messages
  • Ted S
    I have a view where there are three fields that i have to search for 3 specific values. Let s say x,y, and z. The three fields are called MiscAdjust1Desc,
    Message 1 of 16 , Apr 6, 2011
    • 0 Attachment
      I have a view where there are three fields that i have to search for 3 specific values. Let's say x,y, and z. The three fields are called MiscAdjust1Desc, MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of the three fields.

      In the results, I need to have all the x listed in one column, all the y in one column, and all the z in one column.

      Here is my code:

      SELECT DISTINCT
      TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #], Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
      Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV, Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],
      Apacs_Reporting.dbo.tblClaim.InsSettlement - Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
      MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment Date], Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
      Apacs_Reporting.dbo.tblClaim.MiscAdjust1
      FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
      Apacs_Reporting.dbo.tblMasterProduct ON
      Apacs_Reporting.dbo.tblCarrierProducts.ProductCode = Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
      Apacs_Reporting.dbo.tblClaim INNER JOIN
      Apacs_Reporting.dbo.tblClaimPayment ON
      Apacs_Reporting.dbo.tblClaim.ClaimNumber = Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
      Apacs_Reporting.dbo.tblMasterPolicy ON Apacs_Reporting.dbo.tblClaim.PolicyID = Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
      Apacs_Reporting.dbo.tblValidProducts ON
      Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID = Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
      Apacs_Reporting.dbo.tblCarrierProducts.ProductID = Apacs_Reporting.dbo.tblValidProducts.ProductID
      WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL)) AND
      (Apacs_Reporting.dbo.tblClaim.InsSettlement = Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
      (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND (Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
      CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31 00:00:00', 102)) and
      (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition Adjustments')
      GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber, Apacs_Reporting.dbo.tblClaim.VehiclePACV, Apacs_Reporting.dbo.tblClaim.VehicleACV,
      Apacs_Reporting.dbo.tblClaim.InsSettlement - Apacs_Reporting.dbo.tblClaim.VehiclePACV, Apacs_Reporting.dbo.tblClaim.InsSettlement,
      Apacs_Reporting.dbo.tblClaim.MiscAdjust1, Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
      , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
      ORDER BY [Claim #]
    • Paul Livengood
      Not sure I understand what you want.  You only want results where x,y, and z are in MiscAdjust1Desc, MiscAdjust3Desc AND MiscAdjust3Desc, but anyone of the
      Message 2 of 16 , Apr 6, 2011
      • 0 Attachment
        Not sure I understand what you want.  You only want results where x,y, and z are
        in MiscAdjust1Desc, MiscAdjust3Desc AND MiscAdjust3Desc, but anyone of the
        values can be in anyone of the columns.
        In the result set you want to include x,y,z....Is that correct?
        If so, try this...

        SELECTDISTINCTTOP(100 )PERCENTApacs_Reporting.dbo.tblClaim.ClaimNumber AS[Claim
        #],Apacs_Reporting.dbo.tblClaim.VehiclePACV AS[Primary
        Settlement],Apacs_Reporting.dbo.tblClaim.VehicleACV
        ASACV,Apacs_Reporting.dbo.tblClaim.InsSettlement AS[Settlement Amount
        Used],Apacs_Reporting.dbo.tblClaim.InsSettlement-Apacs_Reporting.dbo.tblClaim.VehiclePACV
        AS[Payment
        Difference],MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate)AS[Payment
        Date],Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,'x','y','z'FROMApacs_Reporting.dbo.tblCarrierProductsINNERJOINApacs_Reporting.dbo.tblMasterProduct
        ONApacs_Reporting.dbo.tblCarrierProducts.ProductCode
        =Apacs_Reporting.dbo.tblMasterProduct.ProductCodeINNERJOINApacs_Reporting.dbo.tblClaimINNERJOINApacs_Reporting.dbo.tblClaimPayment
        ONApacs_Reporting.dbo.tblClaim.ClaimNumber
        =Apacs_Reporting.dbo.tblClaimPayment.ClaimNumberINNERJOINApacs_Reporting.dbo.tblMasterPolicy
        ONApacs_Reporting.dbo.tblClaim.PolicyID
        =Apacs_Reporting.dbo.tblMasterPolicy.PolicyIDINNERJOINApacs_Reporting.dbo.tblValidProducts
        ONApacs_Reporting.dbo.tblMasterPolicy.ValidProductID
        =Apacs_Reporting.dbo.tblValidProducts.ValidProductID
        ONApacs_Reporting.dbo.tblCarrierProducts.ProductID
        =Apacs_Reporting.dbo.tblValidProducts.ProductIDWHERE(NOT(Apacs_Reporting.dbo.tblClaim.VehiclePACV
        ISNULL))AND(Apacs_Reporting.dbo.tblClaim.InsSettlement
        =Apacs_Reporting.dbo.tblClaim.VehiclePACV
        )AND(Apacs_Reporting.dbo.tblMasterProduct.ProductCode
        LIKE'fp%')AND(Apacs_Reporting.dbo.tblClaimPayment.CheckDate
        BETWEENCONVERT(DATETIME,'2010-01-01
        00:00:00',102)ANDCONVERT(DATETIME,'2010-12-31
        00:00:00',102))AND(Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
        ='x'ORApacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
        ='x'ORApacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
        ='x')AND(Apacs_Reporting.dbo.tblClaim.MiscAdjust2Desc
        ='y'ORApacs_Reporting.dbo.tblClaim.MiscAdjust2Desc
        ='y'ORApacs_Reporting.dbo.tblClaim.MiscAdjust2Desc
        ='y')AND(Apacs_Reporting.dbo.tblClaim.MiscAdjust3Desc
        ='z'ORApacs_Reporting.dbo.tblClaim.MiscAdjust3Desc
        ='z'ORApacs_Reporting.dbo.tblClaim.MiscAdjust3Desc
        ='z')GROUPBYApacs_Reporting.dbo.tblClaim.ClaimNumber,Apacs_Reporting.dbo.tblClaim.VehiclePACV,Apacs_Reporting.dbo.tblClaim.VehicleACV,Apacs_Reporting.dbo.tblClaim.InsSettlement-Apacs_Reporting.dbo.tblClaim.VehiclePACV,Apacs_Reporting.dbo.tblClaim.InsSettlement,Apacs_Reporting.dbo.tblClaim.MiscAdjust1,Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,Apacs_Reporting.dbo.tblClaim.MiscAdjust1DescORDERBY[Claim
        #]




        ________________________________
        From: Ted S <miamited33156@...>
        To: SQLQueriesNoCode@yahoogroups.com
        Sent: Wed, April 6, 2011 8:36:13 AM
        Subject: [SQLQueriesNoCode] query multiple fields results in one column.

         
        I have a view where there are three fields that i have to search for 3 specific
        values. Let's say x,y, and z. The three fields are called MiscAdjust1Desc,
        MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of the three
        fields.


        In the results, I need to have all the x listed in one column, all the y in one
        column, and all the z in one column.

        Here is my code:

        SELECT DISTINCT
        TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #],
        Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],

        Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
        Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],

        Apacs_Reporting.dbo.tblClaim.InsSettlement -
        Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],

        MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment Date],
        Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,

        Apacs_Reporting.dbo.tblClaim.MiscAdjust1
        FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
        Apacs_Reporting.dbo.tblMasterProduct ON
        Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
        Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
        Apacs_Reporting.dbo.tblClaim INNER JOIN
        Apacs_Reporting.dbo.tblClaimPayment ON
        Apacs_Reporting.dbo.tblClaim.ClaimNumber =
        Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
        Apacs_Reporting.dbo.tblMasterPolicy ON Apacs_Reporting.dbo.tblClaim.PolicyID =
        Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
        Apacs_Reporting.dbo.tblValidProducts ON
        Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
        Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON

        Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
        Apacs_Reporting.dbo.tblValidProducts.ProductID
        WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL)) AND
        (Apacs_Reporting.dbo.tblClaim.InsSettlement =
        Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND

        (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND
        (Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN

        CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31
        00:00:00', 102)) and

        (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition Adjustments')
        GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
        Apacs_Reporting.dbo.tblClaim.VehiclePACV,
        Apacs_Reporting.dbo.tblClaim.VehicleACV,

        Apacs_Reporting.dbo.tblClaim.InsSettlement -
        Apacs_Reporting.dbo.tblClaim.VehiclePACV,
        Apacs_Reporting.dbo.tblClaim.InsSettlement,

        Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
        Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
        , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
        ORDER BY [Claim #]




        [Non-text portions of this message have been removed]
      • John Warner
        Likely too late but this would be so easy if your database was normalized. Any time you have fields named blah1, blah2, blah3 you need to proceed to third
        Message 3 of 16 , Apr 6, 2011
        • 0 Attachment
          Likely too late but this would be so easy if your database was normalized.
          Any time you have fields named blah1, blah2, blah3 you need to proceed to
          third normal form. Querying problems like this are avoided that way.

          John Warner


          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ted S
          > Sent: Wednesday, April 06, 2011 10:36 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: [SQLQueriesNoCode] query multiple fields results in one column.
          >
          > I have a view where there are three fields that i have to search for 3
          specific
          > values. Let's say x,y, and z. The three fields are called
          MiscAdjust1Desc,
          > MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of the
          > three fields.
          >
          > In the results, I need to have all the x listed in one column, all the y
          in one
          > column, and all the z in one column.
          >
          > Here is my code:
          >
          > SELECT DISTINCT
          > TOP (100) PERCENT
          > Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #],
          > Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
          > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
          > Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],
          > Apacs_Reporting.dbo.tblClaim.InsSettlement -
          > Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
          > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate)
          AS
          > [Payment Date], Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
          > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
          > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
          > Apacs_Reporting.dbo.tblMasterProduct ON
          > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode
          =
          > Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
          > Apacs_Reporting.dbo.tblClaim INNER JOIN
          > Apacs_Reporting.dbo.tblClaimPayment ON
          > Apacs_Reporting.dbo.tblClaim.ClaimNumber =
          > Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
          > Apacs_Reporting.dbo.tblMasterPolicy ON
          > Apacs_Reporting.dbo.tblClaim.PolicyID =
          > Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
          > Apacs_Reporting.dbo.tblValidProducts ON
          > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID
          =
          > Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
          > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
          > Apacs_Reporting.dbo.tblValidProducts.ProductID
          > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL))
          > AND
          > (Apacs_Reporting.dbo.tblClaim.InsSettlement =
          > Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
          > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode
          LIKE
          > 'fp%') AND (Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
          > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND
          > CONVERT(DATETIME, '2010-12-31 00:00:00', 102)) and
          > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition Adjustments')
          > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
          > Apacs_Reporting.dbo.tblClaim.VehiclePACV,
          > Apacs_Reporting.dbo.tblClaim.VehicleACV,
          > Apacs_Reporting.dbo.tblClaim.InsSettlement -
          > Apacs_Reporting.dbo.tblClaim.VehiclePACV,
          > Apacs_Reporting.dbo.tblClaim.InsSettlement,
          > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
          > Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
          > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
          > ORDER BY [Claim #]
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
        • John Warner
          Don t you just hate it when you come in somewhere (I ll bet this is what has happened to Ted) and there is years worth of data in a database that was not
          Message 4 of 16 , Apr 6, 2011
          • 0 Attachment
            Don't you just hate it when you come in somewhere (I'll bet this is what has happened to Ted) and there is years worth of data in a database that was not properly normalized and now you come along and have to fetch data from the mess.

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
            > Sent: Wednesday, April 06, 2011 10:50 AM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: Re: [SQLQueriesNoCode] query multiple fields results in one column.
            >
            > Not sure I understand what you want. You only want results where x,y, and z
            > are
            > in MiscAdjust1Desc, MiscAdjust3Desc AND MiscAdjust3Desc, but anyone of
            > the
            > values can be in anyone of the columns.
            > In the result set you want to include x,y,z....Is that correct?
            > If so, try this...
            >
            > SELECTDISTINCTTOP(100
            > )PERCENTApacs_Reporting.dbo.tblClaim.ClaimNumber AS[Claim
            > #],Apacs_Reporting.dbo.tblClaim.VehiclePACV AS[Primary
            > Settlement],Apacs_Reporting.dbo.tblClaim.VehicleACV
            > ASACV,Apacs_Reporting.dbo.tblClaim.InsSettlement AS[Settlement Amount
            > Used],Apacs_Reporting.dbo.tblClaim.InsSettlement-
            > Apacs_Reporting.dbo.tblClaim.VehiclePACV
            > AS[Payment
            > Difference],MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate)AS[Payme
            > nt
            > Date],Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,'x','y','z'FROMApacs_Re
            > porting.dbo.tblCarrierProductsINNERJOINApacs_Reporting.dbo.tblMasterProd
            > uct
            > ONApacs_Reporting.dbo.tblCarrierProducts.ProductCode
            > =Apacs_Reporting.dbo.tblMasterProduct.ProductCodeINNERJOINApacs_Rep
            > orting.dbo.tblClaimINNERJOINApacs_Reporting.dbo.tblClaimPayment
            > ONApacs_Reporting.dbo.tblClaim.ClaimNumber
            > =Apacs_Reporting.dbo.tblClaimPayment.ClaimNumberINNERJOINApacs_Rep
            > orting.dbo.tblMasterPolicy
            > ONApacs_Reporting.dbo.tblClaim.PolicyID
            > =Apacs_Reporting.dbo.tblMasterPolicy.PolicyIDINNERJOINApacs_Reporting.
            > dbo.tblValidProducts
            > ONApacs_Reporting.dbo.tblMasterPolicy.ValidProductID
            > =Apacs_Reporting.dbo.tblValidProducts.ValidProductID
            > ONApacs_Reporting.dbo.tblCarrierProducts.ProductID
            > =Apacs_Reporting.dbo.tblValidProducts.ProductIDWHERE(NOT(Apacs_Repor
            > ting.dbo.tblClaim.VehiclePACV
            > ISNULL))AND(Apacs_Reporting.dbo.tblClaim.InsSettlement
            > =Apacs_Reporting.dbo.tblClaim.VehiclePACV
            > )AND(Apacs_Reporting.dbo.tblMasterProduct.ProductCode
            > LIKE'fp%')AND(Apacs_Reporting.dbo.tblClaimPayment.CheckDate
            > BETWEENCONVERT(DATETIME,'2010-01-01
            > 00:00:00',102)ANDCONVERT(DATETIME,'2010-12-31
            > 00:00:00',102))AND(Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
            > ='x'ORApacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
            > ='x'ORApacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
            > ='x')AND(Apacs_Reporting.dbo.tblClaim.MiscAdjust2Desc
            > ='y'ORApacs_Reporting.dbo.tblClaim.MiscAdjust2Desc
            > ='y'ORApacs_Reporting.dbo.tblClaim.MiscAdjust2Desc
            > ='y')AND(Apacs_Reporting.dbo.tblClaim.MiscAdjust3Desc
            > ='z'ORApacs_Reporting.dbo.tblClaim.MiscAdjust3Desc
            > ='z'ORApacs_Reporting.dbo.tblClaim.MiscAdjust3Desc
            > ='z')GROUPBYApacs_Reporting.dbo.tblClaim.ClaimNumber,Apacs_Reporting.
            > dbo.tblClaim.VehiclePACV,Apacs_Reporting.dbo.tblClaim.VehicleACV,Apacs_
            > Reporting.dbo.tblClaim.InsSettlement-
            > Apacs_Reporting.dbo.tblClaim.VehiclePACV,Apacs_Reporting.dbo.tblClaim.In
            > sSettlement,Apacs_Reporting.dbo.tblClaim.MiscAdjust1,Apacs_Reporting.dbo.
            > tblClaim.MiscAdjust1Desc,Apacs_Reporting.dbo.tblClaim.MiscAdjust1DescOR
            > DERBY[Claim
            > #]
            >
            >
            >
            >
            > ________________________________
            > From: Ted S <miamited33156@...>
            > To: SQLQueriesNoCode@yahoogroups.com
            > Sent: Wed, April 6, 2011 8:36:13 AM
            > Subject: [SQLQueriesNoCode] query multiple fields results in one column.
            >
            >
            > I have a view where there are three fields that i have to search for 3 specific
            > values. Let's say x,y, and z. The three fields are called MiscAdjust1Desc,
            > MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of the
            > three
            > fields.
            >
            >
            > In the results, I need to have all the x listed in one column, all the y in one
            > column, and all the z in one column.
            >
            > Here is my code:
            >
            > SELECT DISTINCT
            > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim
            > #],
            > Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
            >
            > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
            > Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],
            >
            > Apacs_Reporting.dbo.tblClaim.InsSettlement -
            > Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
            >
            > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment Date],
            > Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
            >
            > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
            > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
            > Apacs_Reporting.dbo.tblMasterProduct ON
            > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
            > Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
            > Apacs_Reporting.dbo.tblClaim INNER JOIN
            > Apacs_Reporting.dbo.tblClaimPayment ON
            > Apacs_Reporting.dbo.tblClaim.ClaimNumber =
            > Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
            > Apacs_Reporting.dbo.tblMasterPolicy ON
            > Apacs_Reporting.dbo.tblClaim.PolicyID =
            > Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
            > Apacs_Reporting.dbo.tblValidProducts ON
            > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
            > Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
            >
            > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
            > Apacs_Reporting.dbo.tblValidProducts.ProductID
            > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL)) AND
            > (Apacs_Reporting.dbo.tblClaim.InsSettlement =
            > Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
            >
            > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND
            > (Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
            >
            > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND
            > CONVERT(DATETIME, '2010-12-31
            > 00:00:00', 102)) and
            >
            > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition Adjustments')
            > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
            > Apacs_Reporting.dbo.tblClaim.VehiclePACV,
            > Apacs_Reporting.dbo.tblClaim.VehicleACV,
            >
            > Apacs_Reporting.dbo.tblClaim.InsSettlement -
            > Apacs_Reporting.dbo.tblClaim.VehiclePACV,
            > Apacs_Reporting.dbo.tblClaim.InsSettlement,
            >
            > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
            > Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
            > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
            > ORDER BY [Claim #]
            >
            >
            >
            >
            > [Non-text portions of this message have been removed]
            >
            >
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
          • Jon Boss
            I was just wondering what everyone’s thoughts were on storing a fixed schema as XML data type. Why would I want to store inbound data that is based on a
            Message 5 of 16 , Apr 6, 2011
            • 0 Attachment
              I was just wondering what everyone’s thoughts were on storing a fixed schema as XML data type.

              Why would I want to store inbound data that is based on a fixed schema in a XML data type?

              I have some thoughts as to the pro/con of this but want others opinions.

              Thanks,
              Jon

              The BCI Email Firewall made the following annotations
              ---------------------------------------------------------------------
              *Confidentiality Notice:

              This E-Mail is intended only for the use of the individual
              or entity to which it is addressed and may contain
              information that is privileged, confidential and exempt
              from disclosure under applicable law. If you have received
              this communication in error, please do not distribute, and
              delete the original message.

              Thank you for your compliance.

              You may contact us at:
              Blue Cross of Idaho
              3000 E. Pine Ave.
              Meridian, Idaho 83642
              1.208.345.4550

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



              [Non-text portions of this message have been removed]
            • Paul Livengood
              what else would you store it as?  Are you saying you want to store XML data as a text? ________________________________ From: Jon Boss To:
              Message 6 of 16 , Apr 6, 2011
              • 0 Attachment
                what else would you store it as?  Are you saying you want to store XML data as a
                text?




                ________________________________
                From: Jon Boss <jboss@...>
                To: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
                Sent: Wed, April 6, 2011 8:59:55 AM
                Subject: [SQLQueriesNoCode] Thougths on using XML data type

                 
                I was just wondering what everyone’s thoughts were on storing a fixed schema as
                XML data type.


                Why would I want to store inbound data that is based on a fixed schema in a XML
                data type?


                I have some thoughts as to the pro/con of this but want others opinions.

                Thanks,
                Jon

                The BCI Email Firewall made the following annotations
                ----------------------------------------------------------
                *Confidentiality Notice:

                This E-Mail is intended only for the use of the individual
                or entity to which it is addressed and may contain
                information that is privileged, confidential and exempt
                from disclosure under applicable law. If you have received
                this communication in error, please do not distribute, and
                delete the original message.

                Thank you for your compliance.

                You may contact us at:
                Blue Cross of Idaho
                3000 E. Pine Ave.
                Meridian, Idaho 83642
                1.208.345.4550

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


                [Non-text portions of this message have been removed]




                [Non-text portions of this message have been removed]
              • Ted S
                I tried that after cleaning up the ? and missing spaces that the board inserted due to quoting. That only returned one row when there are a lot more than that.
                Message 7 of 16 , Apr 6, 2011
                • 0 Attachment
                  I tried that after cleaning up the ? and missing spaces that the board inserted due to quoting. That only returned one row when there are a lot more than that. It also added three columns with no column name and x,y,z as the content of the fields.

                  --- In SQLQueriesNoCode@yahoogroups.com, Paul Livengood <p_livengood@...> wrote:
                  >
                  > Not sure I understand what you want.  You only want results where x,y, and z are
                  > in MiscAdjust1Desc, MiscAdjust3Desc AND MiscAdjust3Desc, but anyone of the
                  > values can be in anyone of the columns.
                  > In the result set you want to include x,y,z....Is that correct?
                  > If so, try this...
                  >
                  > SELECTDISTINCTTOP(100 )PERCENTApacs_Reporting.dbo.tblClaim.ClaimNumber AS[Claim
                  > #],Apacs_Reporting.dbo.tblClaim.VehiclePACV AS[Primary
                  > Settlement],Apacs_Reporting.dbo.tblClaim.VehicleACV
                  > ASACV,Apacs_Reporting.dbo.tblClaim.InsSettlement AS[Settlement Amount
                  > Used],Apacs_Reporting.dbo.tblClaim.InsSettlement-Apacs_Reporting.dbo.tblClaim.VehiclePACV
                  > AS[Payment
                  > Difference],MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate)AS[Payment
                  > Date],Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,'x','y','z'FROMApacs_Reporting.dbo.tblCarrierProductsINNERJOINApacs_Reporting.dbo.tblMasterProduct
                  > ONApacs_Reporting.dbo.tblCarrierProducts.ProductCode
                  > =Apacs_Reporting.dbo.tblMasterProduct.ProductCodeINNERJOINApacs_Reporting.dbo.tblClaimINNERJOINApacs_Reporting.dbo.tblClaimPayment
                  > ONApacs_Reporting.dbo.tblClaim.ClaimNumber
                  > =Apacs_Reporting.dbo.tblClaimPayment.ClaimNumberINNERJOINApacs_Reporting.dbo.tblMasterPolicy
                  > ONApacs_Reporting.dbo.tblClaim.PolicyID
                  > =Apacs_Reporting.dbo.tblMasterPolicy.PolicyIDINNERJOINApacs_Reporting.dbo.tblValidProducts
                  > ONApacs_Reporting.dbo.tblMasterPolicy.ValidProductID
                  > =Apacs_Reporting.dbo.tblValidProducts.ValidProductID
                  > ONApacs_Reporting.dbo.tblCarrierProducts.ProductID
                  > =Apacs_Reporting.dbo.tblValidProducts.ProductIDWHERE(NOT(Apacs_Reporting.dbo.tblClaim.VehiclePACV
                  > ISNULL))AND(Apacs_Reporting.dbo.tblClaim.InsSettlement
                  > =Apacs_Reporting.dbo.tblClaim.VehiclePACV
                  > )AND(Apacs_Reporting.dbo.tblMasterProduct.ProductCode
                  > LIKE'fp%')AND(Apacs_Reporting.dbo.tblClaimPayment.CheckDate
                  > BETWEENCONVERT(DATETIME,'2010-01-01
                  > 00:00:00',102)ANDCONVERT(DATETIME,'2010-12-31
                  > 00:00:00',102))AND(Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                  > ='x'ORApacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                  > ='x'ORApacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                  > ='x')AND(Apacs_Reporting.dbo.tblClaim.MiscAdjust2Desc
                  > ='y'ORApacs_Reporting.dbo.tblClaim.MiscAdjust2Desc
                  > ='y'ORApacs_Reporting.dbo.tblClaim.MiscAdjust2Desc
                  > ='y')AND(Apacs_Reporting.dbo.tblClaim.MiscAdjust3Desc
                  > ='z'ORApacs_Reporting.dbo.tblClaim.MiscAdjust3Desc
                  > ='z'ORApacs_Reporting.dbo.tblClaim.MiscAdjust3Desc
                  > ='z')GROUPBYApacs_Reporting.dbo.tblClaim.ClaimNumber,Apacs_Reporting.dbo.tblClaim.VehiclePACV,Apacs_Reporting.dbo.tblClaim.VehicleACV,Apacs_Reporting.dbo.tblClaim.InsSettlement-Apacs_Reporting.dbo.tblClaim.VehiclePACV,Apacs_Reporting.dbo.tblClaim.InsSettlement,Apacs_Reporting.dbo.tblClaim.MiscAdjust1,Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,Apacs_Reporting.dbo.tblClaim.MiscAdjust1DescORDERBY[Claim
                  > #]
                  >
                  >
                  >
                  >
                  > ________________________________
                  > From: Ted S <miamited33156@...>
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Sent: Wed, April 6, 2011 8:36:13 AM
                  > Subject: [SQLQueriesNoCode] query multiple fields results in one column.
                  >
                  >  
                  > I have a view where there are three fields that i have to search for 3 specific
                  > values. Let's say x,y, and z. The three fields are called MiscAdjust1Desc,
                  > MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of the three
                  > fields.
                  >
                  >
                  > In the results, I need to have all the x listed in one column, all the y in one
                  > column, and all the z in one column.
                  >
                  > Here is my code:
                  >
                  > SELECT DISTINCT
                  > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #],
                  > Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
                  >
                  > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
                  > Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],
                  >
                  > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                  > Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
                  >
                  > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment Date],
                  > Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                  >
                  > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
                  > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
                  > Apacs_Reporting.dbo.tblMasterProduct ON
                  > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
                  > Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
                  > Apacs_Reporting.dbo.tblClaim INNER JOIN
                  > Apacs_Reporting.dbo.tblClaimPayment ON
                  > Apacs_Reporting.dbo.tblClaim.ClaimNumber =
                  > Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
                  > Apacs_Reporting.dbo.tblMasterPolicy ON Apacs_Reporting.dbo.tblClaim.PolicyID =
                  > Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
                  > Apacs_Reporting.dbo.tblValidProducts ON
                  > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
                  > Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
                  >
                  > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
                  > Apacs_Reporting.dbo.tblValidProducts.ProductID
                  > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL)) AND
                  > (Apacs_Reporting.dbo.tblClaim.InsSettlement =
                  > Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
                  >
                  > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND
                  > (Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
                  >
                  > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31
                  > 00:00:00', 102)) and
                  >
                  > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition Adjustments')
                  > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
                  > Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                  > Apacs_Reporting.dbo.tblClaim.VehicleACV,
                  >
                  > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                  > Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                  > Apacs_Reporting.dbo.tblClaim.InsSettlement,
                  >
                  > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
                  > Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                  > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                  > ORDER BY [Claim #]
                  >
                  >
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >
                • Ted S
                  These are three fields in a form that is filled out. Each one must be chosen from a drop down list. There is no requirement for any order that they be selected
                  Message 8 of 16 , Apr 6, 2011
                  • 0 Attachment
                    These are three fields in a form that is filled out. Each one must be chosen from a drop down list. There is no requirement for any order that they be selected in. Thus in one claim, the user might select x for the first and another selection that I don't care about for this report in the second. The next time x might be selected in the secont or third field.

                    I need to be able to get all the claims listed with all the x selections and the associated amount regardless of it being selected in the first field, second field or third field returned in one column, all of the Y in one column and all the z in one column so that we can work with the data.

                    My query will return the claims that have any of the three in any of the fields and I don't really care to go through and clean up over 700 rows.

                    Here is a short example:
                    MiscAdjust1Desc MiscAdjust1 MiscAdjust2Desc MiscAdjust2
                    Prior Damage 272.24 Condition Adjustments 42.00
                    0.00 Prior Damage 35.00

                    I would want the "Prior Damage" (x) from each column to return in one column, etc.



                    --- In SQLQueriesNoCode@yahoogroups.com, "Ted S" <miamited33156@...> wrote:
                    >
                    > I have a view where there are three fields that i have to search for 3 specific values. Let's say x,y, and z. The three fields are called MiscAdjust1Desc, MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of the three fields.
                    >
                    > In the results, I need to have all the x listed in one column, all the y in one column, and all the z in one column.
                    >
                    > Here is my code:
                    >
                    > SELECT DISTINCT
                    > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #], Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
                    > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV, Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],
                    > Apacs_Reporting.dbo.tblClaim.InsSettlement - Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
                    > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment Date], Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                    > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
                    > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
                    > Apacs_Reporting.dbo.tblMasterProduct ON
                    > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode = Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
                    > Apacs_Reporting.dbo.tblClaim INNER JOIN
                    > Apacs_Reporting.dbo.tblClaimPayment ON
                    > Apacs_Reporting.dbo.tblClaim.ClaimNumber = Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
                    > Apacs_Reporting.dbo.tblMasterPolicy ON Apacs_Reporting.dbo.tblClaim.PolicyID = Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
                    > Apacs_Reporting.dbo.tblValidProducts ON
                    > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID = Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
                    > Apacs_Reporting.dbo.tblCarrierProducts.ProductID = Apacs_Reporting.dbo.tblValidProducts.ProductID
                    > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL)) AND
                    > (Apacs_Reporting.dbo.tblClaim.InsSettlement = Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
                    > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND (Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
                    > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31 00:00:00', 102)) and
                    > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition Adjustments')
                    > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber, Apacs_Reporting.dbo.tblClaim.VehiclePACV, Apacs_Reporting.dbo.tblClaim.VehicleACV,
                    > Apacs_Reporting.dbo.tblClaim.InsSettlement - Apacs_Reporting.dbo.tblClaim.VehiclePACV, Apacs_Reporting.dbo.tblClaim.InsSettlement,
                    > Apacs_Reporting.dbo.tblClaim.MiscAdjust1, Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                    > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                    > ORDER BY [Claim #]
                    >
                  • Paul Livengood
                    I am not sure what you mean by all of the Y in one column and all the z in one column .  Can you give a few rows of sample data and what you want the results
                    Message 9 of 16 , Apr 6, 2011
                    • 0 Attachment
                      I am not sure what you mean by "all of the Y in one column and all the z in one
                      column".  Can you give a few rows of sample data and what you want the results
                      to be? I am sure it is possible, but I dont understand your requirements.



                      ________________________________
                      From: Ted S <miamited33156@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Wed, April 6, 2011 11:47:13 AM
                      Subject: [SQLQueriesNoCode] Re: query multiple fields results in one column.

                       
                      These are three fields in a form that is filled out. Each one must be chosen
                      from a drop down list. There is no requirement for any order that they be
                      selected in. Thus in one claim, the user might select x for the first and
                      another selection that I don't care about for this report in the second. The
                      next time x might be selected in the secont or third field.


                      I need to be able to get all the claims listed with all the x selections and the
                      associated amount regardless of it being selected in the first field, second
                      field or third field returned in one column, all of the Y in one column and all
                      the z in one column so that we can work with the data.

                      My query will return the claims that have any of the three in any of the fields
                      and I don't really care to go through and clean up over 700 rows.

                      Here is a short example:
                      MiscAdjust1Desc MiscAdjust1 MiscAdjust2Desc MiscAdjust2
                      Prior Damage 272.24 Condition Adjustments 42.00
                      0.00 Prior Damage 35.00

                      I would want the "Prior Damage" (x) from each column to return in one column,
                      etc.

                      --- In SQLQueriesNoCode@yahoogroups.com, "Ted S" <miamited33156@...> wrote:
                      >
                      > I have a view where there are three fields that i have to search for 3 specific
                      >values. Let's say x,y, and z. The three fields are called MiscAdjust1Desc,
                      >MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of the three
                      >fields.
                      >
                      >
                      > In the results, I need to have all the x listed in one column, all the y in one
                      >column, and all the z in one column.
                      >
                      > Here is my code:
                      >
                      > SELECT DISTINCT
                      > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #],
                      >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
                      >
                      > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
                      >Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],
                      >
                      > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                      >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
                      >
                      > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment Date],
                      >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                      >
                      > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
                      > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
                      > Apacs_Reporting.dbo.tblMasterProduct ON
                      > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
                      >Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
                      > Apacs_Reporting.dbo.tblClaim INNER JOIN
                      > Apacs_Reporting.dbo.tblClaimPayment ON
                      > Apacs_Reporting.dbo.tblClaim.ClaimNumber =
                      >Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
                      > Apacs_Reporting.dbo.tblMasterPolicy ON Apacs_Reporting.dbo.tblClaim.PolicyID =
                      >Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
                      > Apacs_Reporting.dbo.tblValidProducts ON
                      > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
                      >Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
                      >
                      > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
                      >Apacs_Reporting.dbo.tblValidProducts.ProductID
                      > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL)) AND
                      > (Apacs_Reporting.dbo.tblClaim.InsSettlement =
                      >Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
                      >
                      > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND
                      >(Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
                      >
                      > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31
                      >00:00:00', 102)) and
                      >
                      > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition Adjustments')
                      > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
                      >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                      >Apacs_Reporting.dbo.tblClaim.VehicleACV,
                      >
                      > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                      >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                      >Apacs_Reporting.dbo.tblClaim.InsSettlement,
                      >
                      > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
                      >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                      > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                      > ORDER BY [Claim #]
                      >




                      [Non-text portions of this message have been removed]
                    • Ted S
                      I want the results to list all of the results with x in one column regardless of which of the three field it was in, the same for y and z. After the query is
                      Message 10 of 16 , Apr 7, 2011
                      • 0 Attachment
                        I want the results to list all of the results with x in one column regardless of which of the three field it was in, the same for y and z. After the query is run, I copy and paste the results into excel. I need all of x in one column, y in one, and z in one so that I can add them up as well as other operations such as charts, etc.

                        Example
                        Claim 1 x y
                        Claim 2 x z
                        Claim 3 x y z
                        Claim 4 y



                        --- In SQLQueriesNoCode@yahoogroups.com, Paul Livengood <p_livengood@...> wrote:
                        >
                        > I am not sure what you mean by "all of the Y in one column and all the z in one
                        > column".  Can you give a few rows of sample data and what you want the results
                        > to be? I am sure it is possible, but I dont understand your requirements.
                        >
                        >
                        >
                        > ________________________________
                        > From: Ted S <miamited33156@...>
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Sent: Wed, April 6, 2011 11:47:13 AM
                        > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one column.
                        >
                        >  
                        > These are three fields in a form that is filled out. Each one must be chosen
                        > from a drop down list. There is no requirement for any order that they be
                        > selected in. Thus in one claim, the user might select x for the first and
                        > another selection that I don't care about for this report in the second. The
                        > next time x might be selected in the secont or third field.
                        >
                        >
                        > I need to be able to get all the claims listed with all the x selections and the
                        > associated amount regardless of it being selected in the first field, second
                        > field or third field returned in one column, all of the Y in one column and all
                        > the z in one column so that we can work with the data.
                        >
                        > My query will return the claims that have any of the three in any of the fields
                        > and I don't really care to go through and clean up over 700 rows.
                        >
                        > Here is a short example:
                        > MiscAdjust1Desc MiscAdjust1 MiscAdjust2Desc MiscAdjust2
                        > Prior Damage 272.24 Condition Adjustments 42.00
                        > 0.00 Prior Damage 35.00
                        >
                        > I would want the "Prior Damage" (x) from each column to return in one column,
                        > etc.
                        >
                        > --- In SQLQueriesNoCode@yahoogroups.com, "Ted S" <miamited33156@> wrote:
                        > >
                        > > I have a view where there are three fields that i have to search for 3 specific
                        > >values. Let's say x,y, and z. The three fields are called MiscAdjust1Desc,
                        > >MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of the three
                        > >fields.
                        > >
                        > >
                        > > In the results, I need to have all the x listed in one column, all the y in one
                        > >column, and all the z in one column.
                        > >
                        > > Here is my code:
                        > >
                        > > SELECT DISTINCT
                        > > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #],
                        > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
                        > >
                        > > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
                        > >Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],
                        > >
                        > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                        > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
                        > >
                        > > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment Date],
                        > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                        > >
                        > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
                        > > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
                        > > Apacs_Reporting.dbo.tblMasterProduct ON
                        > > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
                        > >Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
                        > > Apacs_Reporting.dbo.tblClaim INNER JOIN
                        > > Apacs_Reporting.dbo.tblClaimPayment ON
                        > > Apacs_Reporting.dbo.tblClaim.ClaimNumber =
                        > >Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
                        > > Apacs_Reporting.dbo.tblMasterPolicy ON Apacs_Reporting.dbo.tblClaim.PolicyID =
                        > >Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
                        > > Apacs_Reporting.dbo.tblValidProducts ON
                        > > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
                        > >Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
                        > >
                        > > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
                        > >Apacs_Reporting.dbo.tblValidProducts.ProductID
                        > > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL)) AND
                        > > (Apacs_Reporting.dbo.tblClaim.InsSettlement =
                        > >Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
                        > >
                        > > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND
                        > >(Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
                        > >
                        > > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31
                        > >00:00:00', 102)) and
                        > >
                        > > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition Adjustments')
                        > > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
                        > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                        > >Apacs_Reporting.dbo.tblClaim.VehicleACV,
                        > >
                        > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                        > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                        > >Apacs_Reporting.dbo.tblClaim.InsSettlement,
                        > >
                        > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
                        > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                        > > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                        > > ORDER BY [Claim #]
                        > >
                        >
                        >
                        >
                        >
                        > [Non-text portions of this message have been removed]
                        >
                      • John Warner
                        I could with some thought get there using a cursor but that would be the wrong way to do this, hope Paul has a far far better idea than mine. Assuming I
                        Message 11 of 16 , Apr 7, 2011
                        • 0 Attachment
                          I could with some thought get there using a cursor but that would be the
                          wrong way to do this, hope Paul has a far far better idea than mine.
                          Assuming I understand correctly within the data y could appear in any
                          column and needs to be 'moved' for the report query to column 2, same with
                          z to 3 and x to one?

                          sometime today find whoever wrote the front end program and designed this
                          database schema and kick him in the knee and break his favorite coffee cup


                          Still could you show us a couple of rows of sample data to be sure we
                          understand what the data looks like?

                          John Warner


                          > -----Original Message-----
                          > From: SQLQueriesNoCode@yahoogroups.com
                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ted S
                          > Sent: Thursday, April 07, 2011 8:04 AM
                          > To: SQLQueriesNoCode@yahoogroups.com
                          > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one
                          column.
                          >
                          > I want the results to list all of the results with x in one column
                          regardless of
                          > which of the three field it was in, the same for y and z. After the
                          query is run, I
                          > copy and paste the results into excel. I need all of x in one column, y
                          in one,
                          > and z in one so that I can add them up as well as other operations such
                          as
                          > charts, etc.
                          >
                          > Example
                          > Claim 1 x y
                          > Claim 2 x z
                          > Claim 3 x y z
                          > Claim 4 y
                          >
                          >
                          >
                          > --- In SQLQueriesNoCode@yahoogroups.com, Paul Livengood
                          > <p_livengood@...> wrote:
                          > >
                          > > I am not sure what you mean by "all of the Y in one column and all the
                          z in
                          > one
                          > > column".  Can you give a few rows of sample data and what you want
                          the
                          > results
                          > > to be? I am sure it is possible, but I dont understand your
                          requirements.
                          > >
                          > >
                          > >
                          > > ________________________________
                          > > From: Ted S <miamited33156@...>
                          > > To: SQLQueriesNoCode@yahoogroups.com
                          > > Sent: Wed, April 6, 2011 11:47:13 AM
                          > > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one
                          > column.
                          > >
                          > > Â
                          > > These are three fields in a form that is filled out. Each one must be
                          chosen
                          > > from a drop down list. There is no requirement for any order that they
                          be
                          > > selected in. Thus in one claim, the user might select x for the first
                          and
                          > > another selection that I don't care about for this report in the
                          second. The
                          > > next time x might be selected in the secont or third field.
                          > >
                          > >
                          > > I need to be able to get all the claims listed with all the x
                          selections and the
                          > > associated amount regardless of it being selected in the first field,
                          second
                          > > field or third field returned in one column, all of the Y in one
                          column and all
                          > > the z in one column so that we can work with the data.
                          > >
                          > > My query will return the claims that have any of the three in any of
                          the fields
                          > > and I don't really care to go through and clean up over 700 rows.
                          > >
                          > > Here is a short example:
                          > > MiscAdjust1Desc MiscAdjust1 MiscAdjust2Desc MiscAdjust2
                          > > Prior Damage 272.24 Condition Adjustments 42.00
                          > > 0.00 Prior Damage 35.00
                          > >
                          > > I would want the "Prior Damage" (x) from each column to return in one
                          > column,
                          > > etc.
                          > >
                          > > --- In SQLQueriesNoCode@yahoogroups.com, "Ted S" <miamited33156@>
                          > wrote:
                          > > >
                          > > > I have a view where there are three fields that i have to search for
                          3
                          > specific
                          > > >values. Let's say x,y, and z. The three fields are called
                          MiscAdjust1Desc,
                          > > >MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of
                          the
                          > three
                          > > >fields.
                          > > >
                          > > >
                          > > > In the results, I need to have all the x listed in one column, all
                          the y in one
                          > > >column, and all the z in one column.
                          > > >
                          > > > Here is my code:
                          > > >
                          > > > SELECT DISTINCT
                          > > > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS
                          > [Claim #],
                          > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
                          > > >
                          > > > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
                          > > >Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount
                          > Used],
                          > > >
                          > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                          > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
                          > > >
                          > > > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment
                          > Date],
                          > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                          > > >
                          > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
                          > > > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
                          > > > Apacs_Reporting.dbo.tblMasterProduct ON
                          > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
                          > > >Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
                          > > > Apacs_Reporting.dbo.tblClaim INNER JOIN
                          > > > Apacs_Reporting.dbo.tblClaimPayment ON
                          > > > Apacs_Reporting.dbo.tblClaim.ClaimNumber =
                          > > >Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
                          > > > Apacs_Reporting.dbo.tblMasterPolicy ON
                          > Apacs_Reporting.dbo.tblClaim.PolicyID =
                          > > >Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
                          > > > Apacs_Reporting.dbo.tblValidProducts ON
                          > > > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
                          > > >Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
                          > > >
                          > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
                          > > >Apacs_Reporting.dbo.tblValidProducts.ProductID
                          > > > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL))
                          > AND
                          > > > (Apacs_Reporting.dbo.tblClaim.InsSettlement =
                          > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
                          > > >
                          > > > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND
                          > > >(Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
                          > > >
                          > > > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND
                          > CONVERT(DATETIME, '2010-12-31
                          > > >00:00:00', 102)) and
                          > > >
                          > > > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition
                          > Adjustments')
                          > > > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
                          > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                          > > >Apacs_Reporting.dbo.tblClaim.VehicleACV,
                          > > >
                          > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                          > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                          > > >Apacs_Reporting.dbo.tblClaim.InsSettlement,
                          > > >
                          > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
                          > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                          > > > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                          > > > ORDER BY [Claim #]
                          > > >
                          > >
                          > >
                          > >
                          > >
                          > > [Non-text portions of this message have been removed]
                          > >
                          >
                          >
                          >
                          >
                          > ------------------------------------
                          >
                          > Yahoo! Groups Links
                          >
                          >
                          >
                        • Rk Prem Nath
                          ... From: John Warner Subject: RE: [SQLQueriesNoCode] Re: query multiple fields results in one column. To: SQLQueriesNoCode@yahoogroups.com
                          Message 12 of 16 , Apr 7, 2011
                          • 0 Attachment
                            --- On Thu, 4/7/11, John Warner <john@...> wrote:

                            From: John Warner <john@...>
                            Subject: RE: [SQLQueriesNoCode] Re: query multiple fields results in one column.
                            To: SQLQueriesNoCode@yahoogroups.com
                            Date: Thursday, April 7, 2011, 6:26 PM
















                             









                            I could with some thought get there using a cursor but that would be the

                            wrong way to do this, hope Paul has a far far better idea than mine.

                            Assuming I understand correctly within the data y could appear in any

                            column and needs to be 'moved' for the report query to column 2, same with

                            z to 3 and x to one?



                            sometime today find whoever wrote the front end program and designed this

                            database schema and kick him in the knee and break his favorite coffee cup



                            Still could you show us a couple of rows of sample data to be sure we

                            understand what the data looks like?



                            John Warner



                            > -----Original Message-----

                            > From: SQLQueriesNoCode@yahoogroups.com

                            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ted S

                            > Sent: Thursday, April 07, 2011 8:04 AM

                            > To: SQLQueriesNoCode@yahoogroups.com

                            > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one

                            column.

                            >

                            > I want the results to list all of the results with x in one column

                            regardless of

                            > which of the three field it was in, the same for y and z. After the

                            query is run, I

                            > copy and paste the results into excel. I need all of x in one column, y

                            in one,

                            > and z in one so that I can add them up as well as other operations such

                            as

                            > charts, etc.

                            >

                            > Example

                            > Claim 1 x y

                            > Claim 2 x z

                            > Claim 3 x y z

                            > Claim 4 y

                            >

                            >

                            >

                            > --- In SQLQueriesNoCode@yahoogroups.com, Paul Livengood

                            > <p_livengood@...> wrote:

                            > >

                            > > I am not sure what you mean by "all of the Y in one column and all the

                            z in

                            > one

                            > > column".  Can you give a few rows of sample data and what you want

                            the

                            > results

                            > > to be? I am sure it is possible, but I dont understand your

                            requirements.

                            > >

                            > >

                            > >

                            > > ________________________________

                            > > From: Ted S <miamited33156@...>

                            > > To: SQLQueriesNoCode@yahoogroups.com

                            > > Sent: Wed, April 6, 2011 11:47:13 AM

                            > > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one

                            > column.

                            > >

                            > > Â

                            > > These are three fields in a form that is filled out. Each one must be

                            chosen

                            > > from a drop down list. There is no requirement for any order that they

                            be

                            > > selected in. Thus in one claim, the user might select x for the first

                            and

                            > > another selection that I don't care about for this report in the

                            second. The

                            > > next time x might be selected in the secont or third field.

                            > >

                            > >

                            > > I need to be able to get all the claims listed with all the x

                            selections and the

                            > > associated amount regardless of it being selected in the first field,

                            second

                            > > field or third field returned in one column, all of the Y in one

                            column and all

                            > > the z in one column so that we can work with the data.

                            > >

                            > > My query will return the claims that have any of the three in any of

                            the fields

                            > > and I don't really care to go through and clean up over 700 rows.

                            > >

                            > > Here is a short example:

                            > > MiscAdjust1Desc MiscAdjust1 MiscAdjust2Desc MiscAdjust2

                            > > Prior Damage 272.24 Condition Adjustments 42.00

                            > > 0.00 Prior Damage 35.00

                            > >

                            > > I would want the "Prior Damage" (x) from each column to return in one

                            > column,

                            > > etc.

                            > >

                            > > --- In SQLQueriesNoCode@yahoogroups.com, "Ted S" <miamited33156@>

                            > wrote:

                            > > >

                            > > > I have a view where there are three fields that i have to search for

                            3

                            > specific

                            > > >values. Let's say x,y, and z. The three fields are called

                            MiscAdjust1Desc,

                            > > >MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of

                            the

                            > three

                            > > >fields.

                            > > >

                            > > >

                            > > > In the results, I need to have all the x listed in one column, all

                            the y in one

                            > > >column, and all the z in one column.

                            > > >

                            > > > Here is my code:

                            > > >

                            > > > SELECT DISTINCT

                            > > > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS

                            > [Claim #],

                            > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],

                            > > >

                            > > > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,

                            > > >Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount

                            > Used],

                            > > >

                            > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -

                            > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],

                            > > >

                            > > > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment

                            > Date],

                            > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,

                            > > >

                            > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1

                            > > > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN

                            > > > Apacs_Reporting.dbo.tblMasterProduct ON

                            > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =

                            > > >Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN

                            > > > Apacs_Reporting.dbo.tblClaim INNER JOIN

                            > > > Apacs_Reporting.dbo.tblClaimPayment ON

                            > > > Apacs_Reporting.dbo.tblClaim.ClaimNumber =

                            > > >Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN

                            > > > Apacs_Reporting.dbo.tblMasterPolicy ON

                            > Apacs_Reporting.dbo.tblClaim.PolicyID =

                            > > >Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN

                            > > > Apacs_Reporting.dbo.tblValidProducts ON

                            > > > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =

                            > > >Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON

                            > > >

                            > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =

                            > > >Apacs_Reporting.dbo.tblValidProducts.ProductID

                            > > > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL))

                            > AND

                            > > > (Apacs_Reporting.dbo.tblClaim.InsSettlement =

                            > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND

                            > > >

                            > > > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND

                            > > >(Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN

                            > > >

                            > > > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND

                            > CONVERT(DATETIME, '2010-12-31

                            > > >00:00:00', 102)) and

                            > > >

                            > > > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition

                            > Adjustments')

                            > > > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,

                            > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,

                            > > >Apacs_Reporting.dbo.tblClaim.VehicleACV,

                            > > >

                            > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -

                            > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,

                            > > >Apacs_Reporting.dbo.tblClaim.InsSettlement,

                            > > >

                            > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,

                            > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc

                            > > > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc

                            > > > ORDER BY [Claim #]

                            > > >

                            > >

                            > >

                            > >

                            > >

                            > > [Non-text portions of this message have been removed]

                            > >

                            >

                            >

                            >

                            >

                            > ------------------------------------

                            >

                            > Yahoo! Groups Links

                            >

                            >

                            >






























                            [Non-text portions of this message have been removed]
                          • Rk Prem Nath
                            ... From: John Warner Subject: RE: [SQLQueriesNoCode] Re: query multiple fields results in one column. To: SQLQueriesNoCode@yahoogroups.com
                            Message 13 of 16 , Apr 7, 2011
                            • 0 Attachment
                              --- On Thu, 4/7/11, John Warner <john@...> wrote:

                              From: John Warner <john@...>
                              Subject: RE: [SQLQueriesNoCode] Re: query multiple fields results in one column.
                              To: SQLQueriesNoCode@yahoogroups.com
                              Date: Thursday, April 7, 2011, 6:26 PM
















                               









                              I could with some thought get there using a cursor but that would be the

                              wrong way to do this, hope Paul has a far far better idea than mine.

                              Assuming I understand correctly within the data y could appear in any

                              column and needs to be 'moved' for the report query to column 2, same with

                              z to 3 and x to one?



                              sometime today find whoever wrote the front end program and designed this

                              database schema and kick him in the knee and break his favorite coffee cup



                              Still could you show us a couple of rows of sample data to be sure we

                              understand what the data looks like?



                              John Warner



                              > -----Original Message-----

                              > From: SQLQueriesNoCode@yahoogroups.com

                              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ted S

                              > Sent: Thursday, April 07, 2011 8:04 AM

                              > To: SQLQueriesNoCode@yahoogroups.com

                              > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one

                              column.

                              >

                              > I want the results to list all of the results with x in one column

                              regardless of

                              > which of the three field it was in, the same for y and z. After the

                              query is run, I

                              > copy and paste the results into excel. I need all of x in one column, y

                              in one,

                              > and z in one so that I can add them up as well as other operations such

                              as

                              > charts, etc.

                              >

                              > Example

                              > Claim 1 x y

                              > Claim 2 x z

                              > Claim 3 x y z

                              > Claim 4 y

                              >

                              >

                              >

                              > --- In SQLQueriesNoCode@yahoogroups.com, Paul Livengood

                              > <p_livengood@...> wrote:

                              > >

                              > > I am not sure what you mean by "all of the Y in one column and all the

                              z in

                              > one

                              > > column".  Can you give a few rows of sample data and what you want

                              the

                              > results

                              > > to be? I am sure it is possible, but I dont understand your

                              requirements.

                              > >

                              > >

                              > >

                              > > ________________________________

                              > > From: Ted S <miamited33156@...>

                              > > To: SQLQueriesNoCode@yahoogroups.com

                              > > Sent: Wed, April 6, 2011 11:47:13 AM

                              > > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one

                              > column.

                              > >

                              > > Â

                              > > These are three fields in a form that is filled out. Each one must be

                              chosen

                              > > from a drop down list. There is no requirement for any order that they

                              be

                              > > selected in. Thus in one claim, the user might select x for the first

                              and

                              > > another selection that I don't care about for this report in the

                              second. The

                              > > next time x might be selected in the secont or third field.

                              > >

                              > >

                              > > I need to be able to get all the claims listed with all the x

                              selections and the

                              > > associated amount regardless of it being selected in the first field,

                              second

                              > > field or third field returned in one column, all of the Y in one

                              column and all

                              > > the z in one column so that we can work with the data.

                              > >

                              > > My query will return the claims that have any of the three in any of

                              the fields

                              > > and I don't really care to go through and clean up over 700 rows.

                              > >

                              > > Here is a short example:

                              > > MiscAdjust1Desc MiscAdjust1 MiscAdjust2Desc MiscAdjust2

                              > > Prior Damage 272.24 Condition Adjustments 42.00

                              > > 0.00 Prior Damage 35.00

                              > >

                              > > I would want the "Prior Damage" (x) from each column to return in one

                              > column,

                              > > etc.

                              > >

                              > > --- In SQLQueriesNoCode@yahoogroups.com, "Ted S" <miamited33156@>

                              > wrote:

                              > > >

                              > > > I have a view where there are three fields that i have to search for

                              3

                              > specific

                              > > >values. Let's say x,y, and z. The three fields are called

                              MiscAdjust1Desc,

                              > > >MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of

                              the

                              > three

                              > > >fields.

                              > > >

                              > > >

                              > > > In the results, I need to have all the x listed in one column, all

                              the y in one

                              > > >column, and all the z in one column.

                              > > >

                              > > > Here is my code:

                              > > >

                              > > > SELECT DISTINCT

                              > > > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS

                              > [Claim #],

                              > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],

                              > > >

                              > > > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,

                              > > >Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount

                              > Used],

                              > > >

                              > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -

                              > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],

                              > > >

                              > > > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment

                              > Date],

                              > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,

                              > > >

                              > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1

                              > > > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN

                              > > > Apacs_Reporting.dbo.tblMasterProduct ON

                              > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =

                              > > >Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN

                              > > > Apacs_Reporting.dbo.tblClaim INNER JOIN

                              > > > Apacs_Reporting.dbo.tblClaimPayment ON

                              > > > Apacs_Reporting.dbo.tblClaim.ClaimNumber =

                              > > >Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN

                              > > > Apacs_Reporting.dbo.tblMasterPolicy ON

                              > Apacs_Reporting.dbo.tblClaim.PolicyID =

                              > > >Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN

                              > > > Apacs_Reporting.dbo.tblValidProducts ON

                              > > > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =

                              > > >Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON

                              > > >

                              > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =

                              > > >Apacs_Reporting.dbo.tblValidProducts.ProductID

                              > > > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL))

                              > AND

                              > > > (Apacs_Reporting.dbo.tblClaim.InsSettlement =

                              > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND

                              > > >

                              > > > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND

                              > > >(Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN

                              > > >

                              > > > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND

                              > CONVERT(DATETIME, '2010-12-31

                              > > >00:00:00', 102)) and

                              > > >

                              > > > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition

                              > Adjustments')

                              > > > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,

                              > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,

                              > > >Apacs_Reporting.dbo.tblClaim.VehicleACV,

                              > > >

                              > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -

                              > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,

                              > > >Apacs_Reporting.dbo.tblClaim.InsSettlement,

                              > > >

                              > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,

                              > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc

                              > > > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc

                              > > > ORDER BY [Claim #]

                              > > >

                              > >

                              > >

                              > >

                              > >

                              > > [Non-text portions of this message have been removed]

                              > >

                              >

                              >

                              >

                              >

                              > ------------------------------------

                              >

                              > Yahoo! Groups Links

                              >

                              >

                              >






























                              [Non-text portions of this message have been removed]
                            • Ted S
                              John, That is exactly what I am looking for. I wish I could go visit the people that set this up, but they are in another city.
                              Message 14 of 16 , Apr 7, 2011
                              • 0 Attachment
                                John,
                                That is exactly what I am looking for.

                                I wish I could go visit the people that set this up, but they are in another city.

                                --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
                                >
                                > I could with some thought get there using a cursor but that would be the
                                > wrong way to do this, hope Paul has a far far better idea than mine.
                                > Assuming I understand correctly within the data y could appear in any
                                > column and needs to be 'moved' for the report query to column 2, same with
                                > z to 3 and x to one?
                                >
                                > sometime today find whoever wrote the front end program and designed this
                                > database schema and kick him in the knee and break his favorite coffee cup
                                >
                                >
                                > Still could you show us a couple of rows of sample data to be sure we
                                > understand what the data looks like?
                                >
                                > John Warner
                                >
                                >
                                > > -----Original Message-----
                                > > From: SQLQueriesNoCode@yahoogroups.com
                                > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ted S
                                > > Sent: Thursday, April 07, 2011 8:04 AM
                                > > To: SQLQueriesNoCode@yahoogroups.com
                                > > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one
                                > column.
                                > >
                                > > I want the results to list all of the results with x in one column
                                > regardless of
                                > > which of the three field it was in, the same for y and z. After the
                                > query is run, I
                                > > copy and paste the results into excel. I need all of x in one column, y
                                > in one,
                                > > and z in one so that I can add them up as well as other operations such
                                > as
                                > > charts, etc.
                                > >
                                > > Example
                                > > Claim 1 x y
                                > > Claim 2 x z
                                > > Claim 3 x y z
                                > > Claim 4 y
                                > >
                                > >
                                > >
                                > > --- In SQLQueriesNoCode@yahoogroups.com, Paul Livengood
                                > > <p_livengood@> wrote:
                                > > >
                                > > > I am not sure what you mean by "all of the Y in one column and all the
                                > z in
                                > > one
                                > > > column".  Can you give a few rows of sample data and what you want
                                > the
                                > > results
                                > > > to be? I am sure it is possible, but I dont understand your
                                > requirements.
                                > > >
                                > > >
                                > > >
                                > > > ________________________________
                                > > > From: Ted S <miamited33156@>
                                > > > To: SQLQueriesNoCode@yahoogroups.com
                                > > > Sent: Wed, April 6, 2011 11:47:13 AM
                                > > > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one
                                > > column.
                                > > >
                                > > > Â
                                > > > These are three fields in a form that is filled out. Each one must be
                                > chosen
                                > > > from a drop down list. There is no requirement for any order that they
                                > be
                                > > > selected in. Thus in one claim, the user might select x for the first
                                > and
                                > > > another selection that I don't care about for this report in the
                                > second. The
                                > > > next time x might be selected in the secont or third field.
                                > > >
                                > > >
                                > > > I need to be able to get all the claims listed with all the x
                                > selections and the
                                > > > associated amount regardless of it being selected in the first field,
                                > second
                                > > > field or third field returned in one column, all of the Y in one
                                > column and all
                                > > > the z in one column so that we can work with the data.
                                > > >
                                > > > My query will return the claims that have any of the three in any of
                                > the fields
                                > > > and I don't really care to go through and clean up over 700 rows.
                                > > >
                                > > > Here is a short example:
                                > > > MiscAdjust1Desc MiscAdjust1 MiscAdjust2Desc MiscAdjust2
                                > > > Prior Damage 272.24 Condition Adjustments 42.00
                                > > > 0.00 Prior Damage 35.00
                                > > >
                                > > > I would want the "Prior Damage" (x) from each column to return in one
                                > > column,
                                > > > etc.
                                > > >
                                > > > --- In SQLQueriesNoCode@yahoogroups.com, "Ted S" <miamited33156@>
                                > > wrote:
                                > > > >
                                > > > > I have a view where there are three fields that i have to search for
                                > 3
                                > > specific
                                > > > >values. Let's say x,y, and z. The three fields are called
                                > MiscAdjust1Desc,
                                > > > >MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of
                                > the
                                > > three
                                > > > >fields.
                                > > > >
                                > > > >
                                > > > > In the results, I need to have all the x listed in one column, all
                                > the y in one
                                > > > >column, and all the z in one column.
                                > > > >
                                > > > > Here is my code:
                                > > > >
                                > > > > SELECT DISTINCT
                                > > > > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS
                                > > [Claim #],
                                > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
                                > > > >
                                > > > > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
                                > > > >Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount
                                > > Used],
                                > > > >
                                > > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                                > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
                                > > > >
                                > > > > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment
                                > > Date],
                                > > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                                > > > >
                                > > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
                                > > > > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
                                > > > > Apacs_Reporting.dbo.tblMasterProduct ON
                                > > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
                                > > > >Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
                                > > > > Apacs_Reporting.dbo.tblClaim INNER JOIN
                                > > > > Apacs_Reporting.dbo.tblClaimPayment ON
                                > > > > Apacs_Reporting.dbo.tblClaim.ClaimNumber =
                                > > > >Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
                                > > > > Apacs_Reporting.dbo.tblMasterPolicy ON
                                > > Apacs_Reporting.dbo.tblClaim.PolicyID =
                                > > > >Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
                                > > > > Apacs_Reporting.dbo.tblValidProducts ON
                                > > > > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
                                > > > >Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
                                > > > >
                                > > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
                                > > > >Apacs_Reporting.dbo.tblValidProducts.ProductID
                                > > > > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL))
                                > > AND
                                > > > > (Apacs_Reporting.dbo.tblClaim.InsSettlement =
                                > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
                                > > > >
                                > > > > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND
                                > > > >(Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
                                > > > >
                                > > > > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND
                                > > CONVERT(DATETIME, '2010-12-31
                                > > > >00:00:00', 102)) and
                                > > > >
                                > > > > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition
                                > > Adjustments')
                                > > > > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
                                > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                                > > > >Apacs_Reporting.dbo.tblClaim.VehicleACV,
                                > > > >
                                > > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                                > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                                > > > >Apacs_Reporting.dbo.tblClaim.InsSettlement,
                                > > > >
                                > > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
                                > > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                                > > > > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                                > > > > ORDER BY [Claim #]
                                > > > >
                                > > >
                                > > >
                                > > >
                                > > >
                                > > > [Non-text portions of this message have been removed]
                                > > >
                                > >
                                > >
                                > >
                                > >
                                > > ------------------------------------
                                > >
                                > > Yahoo! Groups Links
                                > >
                                > >
                                > >
                                >
                              • John Warner
                                I know insurance data can be massive, generally speaking how many rows are you expecting back? Again using a cursor here could easily clobber your server for
                                Message 15 of 16 , Apr 7, 2011
                                • 0 Attachment
                                  I know insurance data can be massive, generally speaking how many rows are
                                  you expecting back? Again using a cursor here could easily clobber your
                                  server for awhile if we are looking at a LOT of records. Also any chance
                                  you could clean the personal stuff out of a few rows of state so we can
                                  actually see this?

                                  John Warner


                                  > -----Original Message-----
                                  > From: SQLQueriesNoCode@yahoogroups.com
                                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ted S
                                  > Sent: Thursday, April 07, 2011 10:26 AM
                                  > To: SQLQueriesNoCode@yahoogroups.com
                                  > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one
                                  column.
                                  >
                                  > John,
                                  > That is exactly what I am looking for.
                                  >
                                  > I wish I could go visit the people that set this up, but they are in
                                  another city.
                                  >
                                  > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
                                  > wrote:
                                  > >
                                  > > I could with some thought get there using a cursor but that would be
                                  the
                                  > > wrong way to do this, hope Paul has a far far better idea than mine.
                                  > > Assuming I understand correctly within the data y could appear in any
                                  > > column and needs to be 'moved' for the report query to column 2, same
                                  with
                                  > > z to 3 and x to one?
                                  > >
                                  > > sometime today find whoever wrote the front end program and designed
                                  this
                                  > > database schema and kick him in the knee and break his favorite coffee
                                  cup
                                  > >
                                  > >
                                  > > Still could you show us a couple of rows of sample data to be sure we
                                  > > understand what the data looks like?
                                  > >
                                  > > John Warner
                                  > >
                                  > >
                                  > > > -----Original Message-----
                                  > > > From: SQLQueriesNoCode@yahoogroups.com
                                  > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ted S
                                  > > > Sent: Thursday, April 07, 2011 8:04 AM
                                  > > > To: SQLQueriesNoCode@yahoogroups.com
                                  > > > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one
                                  > > column.
                                  > > >
                                  > > > I want the results to list all of the results with x in one column
                                  > > regardless of
                                  > > > which of the three field it was in, the same for y and z. After the
                                  > > query is run, I
                                  > > > copy and paste the results into excel. I need all of x in one
                                  column, y
                                  > > in one,
                                  > > > and z in one so that I can add them up as well as other operations
                                  such
                                  > > as
                                  > > > charts, etc.
                                  > > >
                                  > > > Example
                                  > > > Claim 1 x y
                                  > > > Claim 2 x z
                                  > > > Claim 3 x y z
                                  > > > Claim 4 y
                                  > > >
                                  > > >
                                  > > >
                                  > > > --- In SQLQueriesNoCode@yahoogroups.com, Paul Livengood
                                  > > > <p_livengood@> wrote:
                                  > > > >
                                  > > > > I am not sure what you mean by "all of the Y in one column and all
                                  the
                                  > > z in
                                  > > > one
                                  > > > > column".  Can you give a few rows of sample data and what you
                                  want
                                  > > the
                                  > > > results
                                  > > > > to be? I am sure it is possible, but I dont understand your
                                  > > requirements.
                                  > > > >
                                  > > > >
                                  > > > >
                                  > > > > ________________________________
                                  > > > > From: Ted S <miamited33156@>
                                  > > > > To: SQLQueriesNoCode@yahoogroups.com
                                  > > > > Sent: Wed, April 6, 2011 11:47:13 AM
                                  > > > > Subject: [SQLQueriesNoCode] Re: query multiple fields results in
                                  one
                                  > > > column.
                                  > > > >
                                  > > > > Â
                                  > > > > These are three fields in a form that is filled out. Each one must
                                  be
                                  > > chosen
                                  > > > > from a drop down list. There is no requirement for any order that
                                  they
                                  > > be
                                  > > > > selected in. Thus in one claim, the user might select x for the
                                  first
                                  > > and
                                  > > > > another selection that I don't care about for this report in the
                                  > > second. The
                                  > > > > next time x might be selected in the secont or third field.
                                  > > > >
                                  > > > >
                                  > > > > I need to be able to get all the claims listed with all the x
                                  > > selections and the
                                  > > > > associated amount regardless of it being selected in the first
                                  field,
                                  > > second
                                  > > > > field or third field returned in one column, all of the Y in one
                                  > > column and all
                                  > > > > the z in one column so that we can work with the data.
                                  > > > >
                                  > > > > My query will return the claims that have any of the three in any
                                  of
                                  > > the fields
                                  > > > > and I don't really care to go through and clean up over 700 rows.
                                  > > > >
                                  > > > > Here is a short example:
                                  > > > > MiscAdjust1Desc MiscAdjust1 MiscAdjust2Desc MiscAdjust2
                                  > > > > Prior Damage 272.24 Condition Adjustments 42.00
                                  > > > > 0.00 Prior Damage 35.00
                                  > > > >
                                  > > > > I would want the "Prior Damage" (x) from each column to return in
                                  one
                                  > > > column,
                                  > > > > etc.
                                  > > > >
                                  > > > > --- In SQLQueriesNoCode@yahoogroups.com, "Ted S"
                                  > <miamited33156@>
                                  > > > wrote:
                                  > > > > >
                                  > > > > > I have a view where there are three fields that i have to search
                                  for
                                  > > 3
                                  > > > specific
                                  > > > > >values. Let's say x,y, and z. The three fields are called
                                  > > MiscAdjust1Desc,
                                  > > > > >MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any
                                  of
                                  > > the
                                  > > > three
                                  > > > > >fields.
                                  > > > > >
                                  > > > > >
                                  > > > > > In the results, I need to have all the x listed in one column,
                                  all
                                  > > the y in one
                                  > > > > >column, and all the z in one column.
                                  > > > > >
                                  > > > > > Here is my code:
                                  > > > > >
                                  > > > > > SELECT DISTINCT
                                  > > > > > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber
                                  > AS
                                  > > > [Claim #],
                                  > > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
                                  > > > > >
                                  > > > > > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
                                  > > > > >Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount
                                  > > > Used],
                                  > > > > >
                                  > > > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                                  > > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
                                  > > > > >
                                  > > > > > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment
                                  > > > Date],
                                  > > > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                                  > > > > >
                                  > > > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
                                  > > > > > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
                                  > > > > > Apacs_Reporting.dbo.tblMasterProduct ON
                                  > > > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
                                  > > > > >Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
                                  > > > > > Apacs_Reporting.dbo.tblClaim INNER JOIN
                                  > > > > > Apacs_Reporting.dbo.tblClaimPayment ON
                                  > > > > > Apacs_Reporting.dbo.tblClaim.ClaimNumber =
                                  > > > > >Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
                                  > > > > > Apacs_Reporting.dbo.tblMasterPolicy ON
                                  > > > Apacs_Reporting.dbo.tblClaim.PolicyID =
                                  > > > > >Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
                                  > > > > > Apacs_Reporting.dbo.tblValidProducts ON
                                  > > > > > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
                                  > > > > >Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
                                  > > > > >
                                  > > > > > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
                                  > > > > >Apacs_Reporting.dbo.tblValidProducts.ProductID
                                  > > > > > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS
                                  > NULL))
                                  > > > AND
                                  > > > > > (Apacs_Reporting.dbo.tblClaim.InsSettlement =
                                  > > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
                                  > > > > >
                                  > > > > > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%')
                                  AND
                                  > > > > >(Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
                                  > > > > >
                                  > > > > > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND
                                  > > > CONVERT(DATETIME, '2010-12-31
                                  > > > > >00:00:00', 102)) and
                                  > > > > >
                                  > > > > > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition
                                  > > > Adjustments')
                                  > > > > > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
                                  > > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                                  > > > > >Apacs_Reporting.dbo.tblClaim.VehicleACV,
                                  > > > > >
                                  > > > > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                                  > > > > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                                  > > > > >Apacs_Reporting.dbo.tblClaim.InsSettlement,
                                  > > > > >
                                  > > > > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
                                  > > > > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                                  > > > > > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                                  > > > > > ORDER BY [Claim #]
                                  > > > > >
                                  > > > >
                                  > > > >
                                  > > > >
                                  > > > >
                                  > > > > [Non-text portions of this message have been removed]
                                  > > > >
                                  > > >
                                  > > >
                                  > > >
                                  > > >
                                  > > > ------------------------------------
                                  > > >
                                  > > > Yahoo! Groups Links
                                  > > >
                                  > > >
                                  > > >
                                  > >
                                  >
                                  >
                                  >
                                  >
                                  > ------------------------------------
                                  >
                                  > Yahoo! Groups Links
                                  >
                                  >
                                  >
                                • Paul Livengood
                                  okay, try this SELECT DISTINCT TOP ( 100 ) PERCENT         Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #],        
                                  Message 16 of 16 , Apr 7, 2011
                                  • 0 Attachment
                                    okay, try this

                                    SELECT DISTINCT TOP ( 100 ) PERCENT
                                            Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #],
                                            Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
                                            Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
                                            Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],
                                            Apacs_Reporting.dbo.tblClaim.InsSettlement
                                            - Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
                                            MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment Date],
                                            Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                                            CASE WHEN Apacs_Reporting.dbo.tblClaim.MiscAdjust1 = 'x'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust2 = 'x'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust3 = 'x'
                                        THEN 'x' ELSE '' END,
                                            CASE WHEN Apacs_Reporting.dbo.tblClaim.MiscAdjust1 = 'y'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust2 = 'y'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust3 = 'y'
                                        THEN 'y' ELSE '' END,
                                            CASE WHEN Apacs_Reporting.dbo.tblClaim.MiscAdjust1 = 'z'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust2 = 'z'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust3 = 'z'
                                        THEN 'z' ELSE '' END
                                    FROM    Apacs_Reporting.dbo.tblCarrierProducts
                                            INNER JOIN Apacs_Reporting.dbo.tblMasterProduct ON
                                    Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
                                    Apacs_Reporting.dbo.tblMasterProduct.ProductCode
                                            INNER JOIN Apacs_Reporting.dbo.tblClaim
                                            INNER JOIN Apacs_Reporting.dbo.tblClaimPayment ON
                                    Apacs_Reporting.dbo.tblClaim.ClaimNumber =
                                    Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber
                                            INNER JOIN Apacs_Reporting.dbo.tblMasterPolicy ON
                                    Apacs_Reporting.dbo.tblClaim.PolicyID =
                                    Apacs_Reporting.dbo.tblMasterPolicy.PolicyID
                                            INNER JOIN Apacs_Reporting.dbo.tblValidProducts ON
                                    Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
                                    Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
                                    Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
                                    Apacs_Reporting.dbo.tblValidProducts.ProductID
                                    WHERE   ( NOT ( Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL )
                                            )
                                            AND ( Apacs_Reporting.dbo.tblClaim.InsSettlement =
                                    Apacs_Reporting.dbo.tblClaim.VehiclePACV )
                                            AND ( Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%' )
                                            AND ( Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
                                    CONVERT(DATETIME, '2010-01-01 00:00:00', 102)
                                                                                                AND    
                                    CONVERT(DATETIME, '2010-12-31 00:00:00', 102) )
                                            AND ( (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'x'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust2Desc = 'x'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust3Desc = 'x')
                                       OR (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'y'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust2Desc = 'y'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust3Desc = 'y')
                                       OR (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'z'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust2Desc = 'z'
                                        OR Apacs_Reporting.dbo.tblClaim.MiscAdjust3Desc = 'z'))

                                    GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
                                            Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                                            Apacs_Reporting.dbo.tblClaim.VehicleACV,
                                            Apacs_Reporting.dbo.tblClaim.InsSettlement
                                            - Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                                            Apacs_Reporting.dbo.tblClaim.InsSettlement,
                                            Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
                                            Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                                            Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                                    ORDER BY [Claim #]





                                    ________________________________
                                    From: Ted S <miamited33156@...>
                                    To: SQLQueriesNoCode@yahoogroups.com
                                    Sent: Thu, April 7, 2011 6:04:21 AM
                                    Subject: [SQLQueriesNoCode] Re: query multiple fields results in one column.

                                     
                                    I want the results to list all of the results with x in one column regardless of
                                    which of the three field it was in, the same for y and z. After the query is
                                    run, I copy and paste the results into excel. I need all of x in one column, y
                                    in one, and z in one so that I can add them up as well as other operations such
                                    as charts, etc.

                                    Example
                                    Claim 1 x y
                                    Claim 2 x z
                                    Claim 3 x y z
                                    Claim 4 y

                                    --- In SQLQueriesNoCode@yahoogroups.com, Paul Livengood <p_livengood@...> wrote:
                                    >
                                    > I am not sure what you mean by "all of the Y in one column and all the z in one
                                    >
                                    > column".  Can you give a few rows of sample data and what you want the results
                                    >
                                    > to be? I am sure it is possible, but I dont understand your requirements.
                                    >
                                    >
                                    >
                                    > ________________________________
                                    > From: Ted S <miamited33156@...>
                                    > To: SQLQueriesNoCode@yahoogroups.com
                                    > Sent: Wed, April 6, 2011 11:47:13 AM
                                    > Subject: [SQLQueriesNoCode] Re: query multiple fields results in one column.
                                    >
                                    >  
                                    > These are three fields in a form that is filled out. Each one must be chosen
                                    > from a drop down list. There is no requirement for any order that they be
                                    > selected in. Thus in one claim, the user might select x for the first and
                                    > another selection that I don't care about for this report in the second. The
                                    > next time x might be selected in the secont or third field.
                                    >
                                    >
                                    > I need to be able to get all the claims listed with all the x selections and
                                    >the
                                    >
                                    > associated amount regardless of it being selected in the first field, second
                                    > field or third field returned in one column, all of the Y in one column and all
                                    >
                                    > the z in one column so that we can work with the data.
                                    >
                                    > My query will return the claims that have any of the three in any of the fields
                                    >
                                    > and I don't really care to go through and clean up over 700 rows.
                                    >
                                    > Here is a short example:
                                    > MiscAdjust1Desc MiscAdjust1 MiscAdjust2Desc MiscAdjust2
                                    > Prior Damage 272.24 Condition Adjustments 42.00
                                    > 0.00 Prior Damage 35.00
                                    >
                                    > I would want the "Prior Damage" (x) from each column to return in one column,
                                    > etc.
                                    >
                                    > --- In SQLQueriesNoCode@yahoogroups.com, "Ted S" <miamited33156@> wrote:
                                    > >
                                    > > I have a view where there are three fields that i have to search for 3
                                    >specific
                                    >
                                    > >values. Let's say x,y, and z. The three fields are called MiscAdjust1Desc,
                                    > >MiscAdjust2Desc, and MiscAdjust3Desc. The values could be in any of the three

                                    > >fields.
                                    > >
                                    > >
                                    > > In the results, I need to have all the x listed in one column, all the y in
                                    >one
                                    >
                                    > >column, and all the z in one column.
                                    > >
                                    > > Here is my code:
                                    > >
                                    > > SELECT DISTINCT
                                    > > TOP (100) PERCENT Apacs_Reporting.dbo.tblClaim.ClaimNumber AS [Claim #],
                                    > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Primary Settlement],
                                    > >
                                    > > Apacs_Reporting.dbo.tblClaim.VehicleACV AS ACV,
                                    > >Apacs_Reporting.dbo.tblClaim.InsSettlement AS [Settlement Amount Used],
                                    > >
                                    > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                                    > >Apacs_Reporting.dbo.tblClaim.VehiclePACV AS [Payment Difference],
                                    > >
                                    > > MIN(Apacs_Reporting.dbo.tblClaimPayment.CheckDate) AS [Payment Date],
                                    > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc,
                                    > >
                                    > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1
                                    > > FROM Apacs_Reporting.dbo.tblCarrierProducts INNER JOIN
                                    > > Apacs_Reporting.dbo.tblMasterProduct ON
                                    > > Apacs_Reporting.dbo.tblCarrierProducts.ProductCode =
                                    > >Apacs_Reporting.dbo.tblMasterProduct.ProductCode INNER JOIN
                                    > > Apacs_Reporting.dbo.tblClaim INNER JOIN
                                    > > Apacs_Reporting.dbo.tblClaimPayment ON
                                    > > Apacs_Reporting.dbo.tblClaim.ClaimNumber =
                                    > >Apacs_Reporting.dbo.tblClaimPayment.ClaimNumber INNER JOIN
                                    > > Apacs_Reporting.dbo.tblMasterPolicy ON Apacs_Reporting.dbo.tblClaim.PolicyID
                                    >=
                                    >
                                    > >Apacs_Reporting.dbo.tblMasterPolicy.PolicyID INNER JOIN
                                    > > Apacs_Reporting.dbo.tblValidProducts ON
                                    > > Apacs_Reporting.dbo.tblMasterPolicy.ValidProductID =
                                    > >Apacs_Reporting.dbo.tblValidProducts.ValidProductID ON
                                    > >
                                    > > Apacs_Reporting.dbo.tblCarrierProducts.ProductID =
                                    > >Apacs_Reporting.dbo.tblValidProducts.ProductID
                                    > > WHERE (NOT (Apacs_Reporting.dbo.tblClaim.VehiclePACV IS NULL)) AND
                                    > > (Apacs_Reporting.dbo.tblClaim.InsSettlement =
                                    > >Apacs_Reporting.dbo.tblClaim.VehiclePACV) AND
                                    > >
                                    > > (Apacs_Reporting.dbo.tblMasterProduct.ProductCode LIKE 'fp%') AND
                                    > >(Apacs_Reporting.dbo.tblClaimPayment.CheckDate BETWEEN
                                    > >
                                    > > CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND CONVERT(DATETIME,
                                    >'2010-12-31
                                    >
                                    > >00:00:00', 102)) and
                                    > >
                                    > > (Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc = 'Condition Adjustments')
                                    > > GROUP BY Apacs_Reporting.dbo.tblClaim.ClaimNumber,
                                    > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                                    > >Apacs_Reporting.dbo.tblClaim.VehicleACV,
                                    > >
                                    > > Apacs_Reporting.dbo.tblClaim.InsSettlement -
                                    > >Apacs_Reporting.dbo.tblClaim.VehiclePACV,
                                    > >Apacs_Reporting.dbo.tblClaim.InsSettlement,
                                    > >
                                    > > Apacs_Reporting.dbo.tblClaim.MiscAdjust1,
                                    > >Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                                    > > , Apacs_Reporting.dbo.tblClaim.MiscAdjust1Desc
                                    > > ORDER BY [Claim #]
                                    > >
                                    >
                                    >
                                    >
                                    >
                                    > [Non-text portions of this message have been removed]
                                    >




                                    [Non-text portions of this message have been removed]
                                  Your message has been successfully submitted and would be delivered to recipients shortly.