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

Query Returns Three Records, I need them as One, if Possible

Expand Messages
  • The Mills
    Hello All: I have the following Access 2003 Query... SELECT Agent_Store_Machine.Agent_Number, Agent_Store_Machine.Serial_Number, Terminal_Settings.SettingName,
    Message 1 of 10 , May 12 8:36 AM
      Hello All:



      I have the following Access 2003 Query...



      SELECT

      Agent_Store_Machine.Agent_Number,

      Agent_Store_Machine.Serial_Number,

      Terminal_Settings.SettingName,

      Terminal_Settings.SettingValue

      FROM

      Agent_Store_Machine

      INNER JOIN

      Terminal_Settings ON Agent_Store_Machine.Machine_Number =
      Terminal_Settings.SerialNumber

      WHERE

      Terminal_Settings.SettingCurrent = Yes

      AND ((Terminal_Settings.SettingName = 'PrimaryPhone') OR
      (Terminal_Settings.SettingName = 'UserID') OR (Terminal_Settings.SettingName
      = 'HostPassword'))



      The problem is: the database table Terminal_Settings stores the information
      I need in a Name / Value pair in the SettingName / SettingValue columns, so
      instead of returning one row per terminal, I end up getting three due to
      this, and because of the way the architecture is designed for our interface
      for reports, it was always figured that we would have one row at a time.



      Is there anyway I can rework this query to get columns PrimaryPhone, UserID,
      HostPassword to be column names and their values in the row, so I can have
      one row at a time? Instead, the data is looking like this:




      Query4


      Agent_Number

      Serial_Number

      SettingName

      SettingValue


      90002

      207-549-315

      HostPassword

      Pass1


      90002

      207-549-315

      PrimaryPhone

      18881234567


      90002

      207-549-315

      UserID

      User1



      I just need one row, with 5 columns, the SettingName being the column names
      and the values being the row.



      I hope this makes sense. I am not sure what to do.



      Thanks

      Andy





      [Non-text portions of this message have been removed]
    • Paul Livengood
      Andy; You need to do a Pivot Query. Example (Please verify table and column names before running): TRANSFORM Max(Terminal_Settings.SettingValue) AS
      Message 2 of 10 , May 13 9:53 AM
        Andy;

        You need to do a Pivot Query.

        Example (Please verify table and column names before running):
        TRANSFORM Max(Terminal_Settings.SettingValue) AS MaxOfSettingValue

        SELECT
        [Agent_Store_ Machine].Agent_number,
        [Agent_Store_ Machine].Serial_Number

        FROM [Agent_Store_ Machine]
        INNER JOIN Terminal_Settings
        ON [Agent_Store_ Machine].Serial_Number = Terminal_Settings.Serial_Number

        WHERE
        Terminal_Settings.SettingCurrent = Yes
        AND ((Terminal_Settings.SettingName = 'PrimaryPhone')
        OR (Terminal_Settings.SettingName = 'UserID')
        OR (Terminal_Settings.SettingName = 'HostPassword'))

        GROUP BY
        [Agent_Store_ Machine].Agent_number,
        [Agent_Store_ Machine].Serial_Number

        PIVOT Terminal_Settings.SettingName;



        Hope this helps
        Paul


        --- In SQLQueriesNoCode@yahoogroups.com, "The Mills" <kkamills@...> wrote:
        >
        > Hello All:
        >
        >
        >
        > I have the following Access 2003 Query...
        >
        >
        >
        > SELECT
        >
        > Agent_Store_Machine.Agent_Number,
        >
        > Agent_Store_Machine.Serial_Number,
        >
        > Terminal_Settings.SettingName,
        >
        > Terminal_Settings.SettingValue
        >
        > FROM
        >
        > Agent_Store_Machine
        >
        > INNER JOIN
        >
        > Terminal_Settings ON Agent_Store_Machine.Machine_Number =
        > Terminal_Settings.SerialNumber
        >
        > WHERE
        >
        > Terminal_Settings.SettingCurrent = Yes
        >
        > AND ((Terminal_Settings.SettingName = 'PrimaryPhone') OR
        > (Terminal_Settings.SettingName = 'UserID') OR (Terminal_Settings.SettingName
        > = 'HostPassword'))
        >
        >
        >
        > The problem is: the database table Terminal_Settings stores the information
        > I need in a Name / Value pair in the SettingName / SettingValue columns, so
        > instead of returning one row per terminal, I end up getting three due to
        > this, and because of the way the architecture is designed for our interface
        > for reports, it was always figured that we would have one row at a time.
        >
        >
        >
        > Is there anyway I can rework this query to get columns PrimaryPhone, UserID,
        > HostPassword to be column names and their values in the row, so I can have
        > one row at a time? Instead, the data is looking like this:
        >
        >
        >
        >
        > Query4
        >
        >
        > Agent_Number
        >
        > Serial_Number
        >
        > SettingName
        >
        > SettingValue
        >
        >
        > 90002
        >
        > 207-549-315
        >
        > HostPassword
        >
        > Pass1
        >
        >
        > 90002
        >
        > 207-549-315
        >
        > PrimaryPhone
        >
        > 18881234567
        >
        >
        > 90002
        >
        > 207-549-315
        >
        > UserID
        >
        > User1
        >
        >
        >
        > I just need one row, with 5 columns, the SettingName being the column names
        > and the values being the row.
        >
        >
        >
        > I hope this makes sense. I am not sure what to do.
        >
        >
        >
        > Thanks
        >
        > Andy
        >
        >
        >
        >
        >
        > [Non-text portions of this message have been removed]
        >
      • John Warner
        You database is not in third normal form, that is miss designed. You need to sit down with the powers at work and discuss this before the situation gets even
        Message 3 of 10 , May 13 10:45 AM
          You database is not in third normal form, that is miss designed. You need
          to sit down with the powers at work and discuss this before the situation
          gets even worse which it will as the dataset grows. Seriously any work
          around you come up with now will fail or time out as the number of rows
          increases with time.

          John Warner

          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of The Mills
          > Sent: Tuesday, May 12, 2009 11:37 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: [SQLQueriesNoCode] Query Returns Three Records, I need them as
          > One, if Possible
          >
          > Hello All:
          >
          >
          >
          > I have the following Access 2003 Query...
          >
          >
          >
          > SELECT
          >
          > Agent_Store_Machine.Agent_Number,
          >
          > Agent_Store_Machine.Serial_Number,
          >
          > Terminal_Settings.SettingName,
          >
          > Terminal_Settings.SettingValue
          >
          > FROM
          >
          > Agent_Store_Machine
          >
          > INNER JOIN
          >
          > Terminal_Settings ON Agent_Store_Machine.Machine_Number =
          > Terminal_Settings.SerialNumber
          >
          > WHERE
          >
          > Terminal_Settings.SettingCurrent = Yes
          >
          > AND ((Terminal_Settings.SettingName = 'PrimaryPhone') OR
          > (Terminal_Settings.SettingName = 'UserID') OR
          > (Terminal_Settings.SettingName
          > = 'HostPassword'))
          >
          >
          >
          > The problem is: the database table Terminal_Settings stores the
          information
          > I need in a Name / Value pair in the SettingName / SettingValue columns,
          so
          > instead of returning one row per terminal, I end up getting three due to
          > this, and because of the way the architecture is designed for our
          interface
          > for reports, it was always figured that we would have one row at a time.
          >
          >
          >
          > Is there anyway I can rework this query to get columns PrimaryPhone,
          UserID,
          > HostPassword to be column names and their values in the row, so I can
          have
          > one row at a time? Instead, the data is looking like this:
          >
          >
          >
          >
          > Query4
          >
          >
          > Agent_Number
          >
          > Serial_Number
          >
          > SettingName
          >
          > SettingValue
          >
          >
          > 90002
          >
          > 207-549-315
          >
          > HostPassword
          >
          > Pass1
          >
          >
          > 90002
          >
          > 207-549-315
          >
          > PrimaryPhone
          >
          > 18881234567
          >
          >
          > 90002
          >
          > 207-549-315
          >
          > UserID
          >
          > User1
          >
          >
          >
          > I just need one row, with 5 columns, the SettingName being the column
          names
          > and the values being the row.
          >
          >
          >
          > I hope this makes sense. I am not sure what to do.
          >
          >
          >
          > Thanks
          >
          > Andy
          >
          >
          >
          >
          >
          > [Non-text portions of this message have been removed]
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
        • Paul Livengood
          Excuse me if this is a double post. I replied earlier but it did not seem to get posted. I would look into a Pivot query. Example (Please verify table and
          Message 4 of 10 , May 13 12:06 PM
            Excuse me if this is a double post. I replied earlier but it did not seem to get posted.

            I would look into a Pivot query.

            Example (Please verify table and column names):


            TRANSFORM Max(Terminal_Settings.SettingValue) AS MaxOfSettingValue

            SELECT
            [Agent_Store_ Machine].Agent_number,
            [Agent_Store_ Machine].Serial_Number

            FROM
            [Agent_Store_ Machine]
            INNER JOIN
            Terminal_Settings
            ON
            [Agent_Store_ Machine].Serial_Number = Terminal_Settings.Serial_Number

            GROUP BY [Agent_Store_ Machine].Agent_number, [Agent_Store_ Machine].Serial_Number
            PIVOT Terminal_Settings.SettingName;


            Hope this helps
            Paul

            --- In SQLQueriesNoCode@yahoogroups.com, "The Mills" <kkamills@...> wrote:
            >
            > Hello All:
            >
            >
            >
            > I have the following Access 2003 Query...
            >
            >
            >
            > SELECT
            >
            > Agent_Store_Machine.Agent_Number,
            >
            > Agent_Store_Machine.Serial_Number,
            >
            > Terminal_Settings.SettingName,
            >
            > Terminal_Settings.SettingValue
            >
            > FROM
            >
            > Agent_Store_Machine
            >
            > INNER JOIN
            >
            > Terminal_Settings ON Agent_Store_Machine.Machine_Number =
            > Terminal_Settings.SerialNumber
            >
            > WHERE
            >
            > Terminal_Settings.SettingCurrent = Yes
            >
            > AND ((Terminal_Settings.SettingName = 'PrimaryPhone') OR
            > (Terminal_Settings.SettingName = 'UserID') OR (Terminal_Settings.SettingName
            > = 'HostPassword'))
            >
            >
            >
            > The problem is: the database table Terminal_Settings stores the information
            > I need in a Name / Value pair in the SettingName / SettingValue columns, so
            > instead of returning one row per terminal, I end up getting three due to
            > this, and because of the way the architecture is designed for our interface
            > for reports, it was always figured that we would have one row at a time.
            >
            >
            >
            > Is there anyway I can rework this query to get columns PrimaryPhone, UserID,
            > HostPassword to be column names and their values in the row, so I can have
            > one row at a time? Instead, the data is looking like this:
            >
            >
            >
            >
            > Query4
            >
            >
            > Agent_Number
            >
            > Serial_Number
            >
            > SettingName
            >
            > SettingValue
            >
            >
            > 90002
            >
            > 207-549-315
            >
            > HostPassword
            >
            > Pass1
            >
            >
            > 90002
            >
            > 207-549-315
            >
            > PrimaryPhone
            >
            > 18881234567
            >
            >
            > 90002
            >
            > 207-549-315
            >
            > UserID
            >
            > User1
            >
            >
            >
            > I just need one row, with 5 columns, the SettingName being the column names
            > and the values being the row.
            >
            >
            >
            > I hope this makes sense. I am not sure what to do.
            >
            >
            >
            > Thanks
            >
            > Andy
            >
            >
            >
            >
            >
            > [Non-text portions of this message have been removed]
            >
          • peternilsson42
            ... In SQL92, use a conditional max... select asm.agent_number, asm.serial_number, max(case when ts.settingname = PrimaryPhone then ts.settingvalue end),
            Message 5 of 10 , May 14 10:30 PM
              "The Mills" <kkamills@...> wrote:
              >
              > Hello All:
              >
              > I have the following Access 2003 Query...
              >
              > SELECT
              > Agent_Store_Machine.Agent_Number,
              > Agent_Store_Machine.Serial_Number,
              > Terminal_Settings.SettingName,
              > Terminal_Settings.SettingValue
              > FROM
              > Agent_Store_Machine
              > INNER JOIN
              > Terminal_Settings ON Agent_Store_Machine.Machine_Number =
              > Terminal_Settings.SerialNumber
              > WHERE
              > Terminal_Settings.SettingCurrent = Yes
              > AND ((Terminal_Settings.SettingName = 'PrimaryPhone') OR
              > (Terminal_Settings.SettingName = 'UserID') OR (Terminal_Settings.SettingName
              > = 'HostPassword'))
              >
              > The problem is: the database table Terminal_Settings stores
              > the information I need in a Name / Value pair in the
              > SettingName / SettingValue columns, so instead of returning
              > one row per terminal, I end up getting three due to
              > this, and because of the way the architecture is designed for
              > our interface for reports, it was always figured that we
              > would have one row at a time.

              In SQL92, use a conditional max...

              select asm.agent_number,
              asm.serial_number,
              max(case
              when ts.settingname = 'PrimaryPhone'
              then ts.settingvalue
              end),
              max(case
              when ts.settingname = 'UserID'
              then ts.settingvalue
              end),
              max(case
              when ts.settingname = 'HostPassword'
              then ts.settingvalue
              end)
              from agent_store_machine asm
              left outer join terminal_settings ts
              on asm.machine_number = ts.serialnumber
              group by
              asm.agent_number,
              asm.serial_number

              --
              Peter
            • John Warner
              What is the downside of Varchar(max) in text fields vs varchar(500) or some other number when the data to be stored here really is variable but in this case I
              Message 6 of 10 , May 20 12:40 PM
                What is the downside of Varchar(max) in text fields vs varchar(500) or some
                other number when the data to be stored here really is variable but in this
                case I know it will never get to 2 Billion characters or even remotely
                close?

                Adding a new table to a database and this has sort of come up moving an
                Access memo field to sql server. I think the memo field will hold 64K of
                characters so for that matter what is the harm of varchar(65535) v
                varchar(max). Much of the data is greater than 255 Access's text/char
                limit.

                Also this field will not be queried in a WHERE/JOIN type clause only in
                SELECTs

                Thanks.


                John Warner
              • Noman Aftab
                http://blog.sqlauthority.com/2007/06/01/sql-server-2005-constraint-on-varcharmax-field-to-limit-it-certain-length/ ... From: John Warner
                Message 7 of 10 , May 20 11:18 PM
                  http://blog.sqlauthority.com/2007/06/01/sql-server-2005-constraint-on-varcharmax-field-to-limit-it-certain-length/


                  --- On Thu, 21/5/09, John Warner <john@...> wrote:


                  From: John Warner <john@...>
                  Subject: [SQLQueriesNoCode] Varchar(max) ?? SQL Server 2005 +
                  To: SQLQueriesNoCode@yahoogroups.com
                  Date: Thursday, 21 May, 2009, 12:40 AM








                  What is the downside of Varchar(max) in text fields vs varchar(500) or some
                  other number when the data to be stored here really is variable but in this
                  case I know it will never get to 2 Billion characters or even remotely
                  close?

                  Adding a new table to a database and this has sort of come up moving an
                  Access memo field to sql server. I think the memo field will hold 64K of
                  characters so for that matter what is the harm of varchar(65535) v
                  varchar(max) . Much of the data is greater than 255 Access's text/char
                  limit.

                  Also this field will not be queried in a WHERE/JOIN type clause only in
                  SELECTs

                  Thanks.

                  John Warner



















                  [Non-text portions of this message have been removed]
                • Paul Livengood
                  John; I believe the main disadvantage would be speed. SQL 2005 has a maximum page size of 8k. This is why in SQL 2000 you could only have VARCHAR(8000), or
                  Message 8 of 10 , May 21 5:01 AM
                    John;

                    I believe the main disadvantage would be speed. SQL 2005 has a maximum page size of 8k. This is why in SQL 2000 you could only have VARCHAR(8000), or NVARCHAR(4000). To get around this limit SQL 2005 uses overflow pages. An overflow page is a second page with the extra data on it. For 2,000,000 characters that is a lot of pages. The overflow pages make it so one record is on multiple pages, not just one. This doubles (or triples, or quadruples, etc) the amount of reads needed to query a row. So, to keep your data to one page, and thereby help keep reads to a minimum, the total size of your table (not just one row) should still be below 8k. If you need to go over that I would recommend creating a second table to store the PK and VARCHAR(MAX) fields in.



                    As for using VARCHAR(65535); that is not allowed. You can use up to 8000 or MAX. To limit above 8000 you need to create a VARCHAR(MAX) field and then add a check constraint to make sure the data length is below your applications max value.



                    ALTER TABLE [TableName
                    ADD CONSTRAINT [constraintName]
                    CHECK (DATALENGTH([FieldName]} <= 65535)





                    HTH

                    Paul



                    --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
                    >
                    > What is the downside of Varchar(max) in text fields vs varchar(500) or some
                    > other number when the data to be stored here really is variable but in this
                    > case I know it will never get to 2 Billion characters or even remotely
                    > close?
                    >
                    > Adding a new table to a database and this has sort of come up moving an
                    > Access memo field to sql server. I think the memo field will hold 64K of
                    > characters so for that matter what is the harm of varchar(65535) v
                    > varchar(max). Much of the data is greater than 255 Access's text/char
                    > limit.
                    >
                    > Also this field will not be queried in a WHERE/JOIN type clause only in
                    > SELECTs
                    >
                    > Thanks.
                    >
                    >
                    > John Warner
                    >
                  • John Warner
                    Yes, I knew about the fact I would have to apply a constraint to actually limit things. I m not sure my question is clear though. Assume I m not actually
                    Message 9 of 10 , May 21 5:36 AM
                      Yes, I knew about the fact I would have to apply a constraint to actually
                      limit things. I'm not sure my question is clear though. Assume I'm not
                      actually coming remotely close to 2 Billion but might on occasion exceed
                      8K. Is there a penalty specific to varchar(max) vs a 'regular' varchar or
                      perhaps with the constraint beyond the paging. Is more data in the file
                      consumed for the extra storage capacity or is this concern unfounded on my
                      part?

                      Clear as mud ??


                      John Warner

                      -----Original Message-----
                      From: SQLQueriesNoCode@yahoogroups.com
                      [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
                      Sent: Thursday, May 21, 2009 8:01 AM
                      To: SQLQueriesNoCode@yahoogroups.com
                      Subject: [SQLQueriesNoCode] Re: Varchar(max) ?? SQL Server 2005 +

                      John;

                      I believe the main disadvantage would be speed. SQL 2005 has a maximum
                      page size of 8k. This is why in SQL 2000 you could only have
                      VARCHAR(8000), or NVARCHAR(4000). To get around this limit SQL 2005 uses
                      overflow pages. An overflow page is a second page with the extra data on
                      it. For 2,000,000 characters that is a lot of pages. The overflow pages
                      make it so one record is on multiple pages, not just one. This doubles (or
                      triples, or quadruples, etc) the amount of reads needed to query a row.
                      So, to keep your data to one page, and thereby help keep reads to a
                      minimum, the total size of your table (not just one row) should still be
                      below 8k. If you need to go over that I would recommend creating a second
                      table to store the PK and VARCHAR(MAX) fields in.



                      As for using VARCHAR(65535); that is not allowed. You can use up to 8000
                      or MAX. To limit above 8000 you need to create a VARCHAR(MAX) field and
                      then add a check constraint to make sure the data length is below your
                      applications max value.



                      ALTER TABLE [TableName
                      ADD CONSTRAINT [constraintName]
                      CHECK (DATALENGTH([FieldName]} <= 65535)





                      HTH

                      Paul



                      --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
                      >
                      > What is the downside of Varchar(max) in text fields vs varchar(500) or
                      > some other number when the data to be stored here really is variable
                      > but in this case I know it will never get to 2 Billion characters or
                      > even remotely close?
                      >
                      > Adding a new table to a database and this has sort of come up moving
                      > an Access memo field to sql server. I think the memo field will hold
                      > 64K of characters so for that matter what is the harm of
                      > varchar(65535) v varchar(max). Much of the data is greater than 255
                      > Access's text/char limit.
                      >
                      > Also this field will not be queried in a WHERE/JOIN type clause only
                      > in SELECTs
                      >
                      > Thanks.
                      >
                      >
                      > John Warner
                      >




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

                      Yahoo! Groups Links
                    • Paul Livengood
                      I would not worry about it, if you need the storage that is what MAX is for.  I would go ahead and use it. ________________________________ From: John Warner
                      Message 10 of 10 , May 21 7:51 AM
                        I would not worry about it, if you need the storage that is what MAX is for.  I would go ahead and use it.




                        ________________________________
                        From: John Warner <john@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Thursday, May 21, 2009 6:36:45 AM
                        Subject: RE: [SQLQueriesNoCode] Re: Varchar(max) ?? SQL Server 2005 +





                        Yes, I knew about the fact I would have to apply a constraint to actually
                        limit things. I'm not sure my question is clear though. Assume I'm not
                        actually coming remotely close to 2 Billion but might on occasion exceed
                        8K. Is there a penalty specific to varchar(max) vs a 'regular' varchar or
                        perhaps with the constraint beyond the paging. Is more data in the file
                        consumed for the extra storage capacity or is this concern unfounded on my
                        part?

                        Clear as mud ??

                        John Warner

                        -----Original Message-----
                        From: SQLQueriesNoCode@ yahoogroups. com
                        [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Paul Livengood
                        Sent: Thursday, May 21, 2009 8:01 AM
                        To: SQLQueriesNoCode@ yahoogroups. com
                        Subject: [SQLQueriesNoCode] Re: Varchar(max) ?? SQL Server 2005 +

                        John;

                        I believe the main disadvantage would be speed. SQL 2005 has a maximum
                        page size of 8k. This is why in SQL 2000 you could only have
                        VARCHAR(8000) , or NVARCHAR(4000) . To get around this limit SQL 2005 uses
                        overflow pages. An overflow page is a second page with the extra data on
                        it. For 2,000,000 characters that is a lot of pages. The overflow pages
                        make it so one record is on multiple pages, not just one. This doubles (or
                        triples, or quadruples, etc) the amount of reads needed to query a row.
                        So, to keep your data to one page, and thereby help keep reads to a
                        minimum, the total size of your table (not just one row) should still be
                        below 8k. If you need to go over that I would recommend creating a second
                        table to store the PK and VARCHAR(MAX) fields in.

                        As for using VARCHAR(65535) ; that is not allowed. You can use up to 8000
                        or MAX. To limit above 8000 you need to create a VARCHAR(MAX) field and
                        then add a check constraint to make sure the data length is below your
                        applications max value.

                        ALTER TABLE [TableName
                        ADD CONSTRAINT [constraintName]
                        CHECK (DATALENGTH( [FieldName] } <= 65535)

                        HTH

                        Paul

                        --- In SQLQueriesNoCode@ yahoogroups. com, "John Warner" <john@...> wrote:
                        >
                        > What is the downside of Varchar(max) in text fields vs varchar(500) or
                        > some other number when the data to be stored here really is variable
                        > but in this case I know it will never get to 2 Billion characters or
                        > even remotely close?
                        >
                        > Adding a new table to a database and this has sort of come up moving
                        > an Access memo field to sql server. I think the memo field will hold
                        > 64K of characters so for that matter what is the harm of
                        > varchar(65535) v varchar(max) . Much of the data is greater than 255
                        > Access's text/char limit.
                        >
                        > Also this field will not be queried in a WHERE/JOIN type clause only
                        > in SELECTs
                        >
                        > Thanks.
                        >
                        >
                        > John Warner
                        >

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

                        Yahoo! Groups Links




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