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

RE: [SQLQueriesNoCode] Pivot Help Enitity Attribute Value style scenario

Expand Messages
  • Arnie Rowland
    This is one of those moments when we see that the elegant (at least for developers) Name-Value pair data scheme (EAV) can cause significant work to
    Message 1 of 17 , Feb 24, 2011
    • 0 Attachment
      This is one of those moments when we see that the 'elegant' (at least
      for developers) Name-Value pair data scheme (EAV) can cause significant
      work to reconstitute meaning from data.

      However, you were very close to the solution you desired. Make the
      following alteration to the query:

      SELECT *
      FROM [TestData]
      PIVOT ( max(KeyValue)
      FOR [KeyName]
      IN ( [Test1],
      [Test2],
      [Test3],
      [UserName],
      [DeviceName])
      ) pvt
      ORDER BY [Id]

      An interesting quirk to max()/min() is that they will work just fine to
      return varchar values -AND as long as you have singleton values, you
      will not have a problem. If you tried to aggregate in some fashion, all
      bets are off...

      As you alluded to in your comments, this will be a 'bear' to implement
      in a dynamic fashion. Again, EAV data structures are easy to design,
      easy to load, and a major headache to turn raw data into actionable
      information.

      <Soapbox>
      There is a reason why we have roads. In some previous planning process,
      the best routes available to get from most locations to most other
      locations were determined. Over time, however, society decides to add
      additional roads, maybe removing unused roads. Imagine trying to keep
      order if everyone could go from any point to any other point as they
      wish. Chaos would ensue, and some of the chosen routes could be through
      your yard. There is a reason we design databases to solve known
      problems, and revise them when additional problems, or desired solutions
      become evident. An EAV database invites chaos -which may not be bad, as
      long as you have the development resources to make information out of
      data.
      </ Soapbox>

      Regards,

      Arnie Rowland, MVP (SQL Server)

      "You cannot do a kindness too soon because you never know how soon it
      will be too late."
      -Ralph Waldo Emerson




      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com
      [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
      Sent: Thursday, February 24, 2011 2:59 PM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: Re: [SQLQueriesNoCode] Pivot Help Enitity Attribute Value style
      scenario

      So you meant like the one for months at:
      http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial
      -with-examples.aspx
      i.e.

      SELECT *
      FROM (
      SELECT
      YEAR(OrderDate) [Year],
      CASE MONTH(OrderDate)
      WHEN 1 THEN 'January'
      WHEN 2 THEN 'February'
      WHEN 3 THEN 'March'
      WHEN 4 THEN 'April'
      WHEN 5 THEN 'May'
      WHEN 6 THEN 'June'
      WHEN 7 THEN 'July'
      WHEN 8 THEN 'August'
      WHEN 8 THEN 'September'
      WHEN 8 THEN 'October'
      WHEN 8 THEN 'November'
      WHEN 8 THEN 'December'
      END as [Month],
      SubTotal
      FROM Sales.SalesOrderHeader
      ) TableDate
      PIVOT (
      SUM(SubTotal)
      FOR [Month] IN (
      [January],[February],[March],[April],
      [May],[June],[July],[August],
      [September],[October],[November],[December]
      )
      ) PivotTable

      What amazes me is this very simple scenario conceptually has not been
      solved elegantly in god knows how many versions of SQL Server.... Plus
      it seems to me I will be building that SQL and executing it dynamically
      not only do my KeyPairs vary they even vary for the report application.

      Some people will want some of the keypairs others will not, so it ends
      up being a very dynamic situation. I may have to go with dynamic CREATE
      TABLES since I will want to cast the values once not leave them in
      nvarchar(n) form in the case of numeric/date/etc. other strongly typed
      data.

      On Thu, Feb 24, 2011 at 5:30 PM, John Warner <john@...> wrote:
      >
      >
      >
      > You may need to do the pivot yourself what I mean is build a CASE WHEN

      > structure. Sort of the pivot table we made in the SQL Server 2000 and
      > lower days.


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

      Yahoo! Groups Links







      [Non-text portions of this message have been removed]
    • Charles Carroll
      This is Logging data for some very complex situations and very dynamic logging data. Someone may scan 500 invoices and OCR it and produce 500 rows like
      Message 2 of 17 , Feb 24, 2011
      • 0 Attachment
        This is Logging data for some very complex situations and very dynamic
        logging data.

        Someone may scan 500 invoices and OCR it and produce 500 rows like
        InvoiceDate mm/dd/yy
        InvoiceAmount $

        in an ideal scenario, and want to report on that data. But the reality is
        each document has a ton of metadata it carries around (filesize, when it
        started scanning, when it finished, whether it was rejected) that comes into
        a log as XML data.... And that metadata needs reporting too.

        In 2 months they may change their capture program to capture
        InvoiceDate mm/dd/yy
        InvoiceAmount $
        VendorName __________
        VendorType __________

        and now it has 4 fields they want to report on.

        And that is just one of hundreds of things the Doc Processing app is doing
        that day it may be moving resumes in and out of CMS systems, storing
        documents and graphics, etc. all of which will have custom fields.

        This data may be transported into a CMS or Database system but this
        reporting engine needs to monitor many document processing servers/programs
        and make sense of the XML it passes around.

        The good news is my program will efficiently give them CODD Relational
        Tables from all this logging XML data being streamed by RPC to me and they
        will be reporting and slicing and dicing off nice neat tables. But how I get
        it to them is some work on my part.

        But everyone using the Reporting system wants some 20-30% of the logging
        data and all their custom data and its different for everyone using it so my
        code looks at what they need from logs sucks it out of many XML data streams
        that travel back and forth.

        On Thu, Feb 24, 2011 at 6:37 PM, Arnie Rowland <arnie@...> wrote:

        >
        >
        > This is one of those moments when we see that the 'elegant' (at least
        > for developers) Name-Value pair data scheme (EAV) can cause significant
        > work to reconstitute meaning from data.
        >
        > However, you were very close to the solution you desired. Make the
        > following alteration to the query:
        >
        > SELECT *
        > FROM [TestData]
        > PIVOT ( max(KeyValue)
        >
        > FOR [KeyName]
        > IN ( [Test1],
        > [Test2],
        > [Test3],
        > [UserName],
        > [DeviceName])
        > ) pvt
        > ORDER BY [Id]
        >
        > An interesting quirk to max()/min() is that they will work just fine to
        > return varchar values -AND as long as you have singleton values, you
        > will not have a problem. If you tried to aggregate in some fashion, all
        > bets are off...
        >
        > As you alluded to in your comments, this will be a 'bear' to implement
        > in a dynamic fashion. Again, EAV data structures are easy to design,
        > easy to load, and a major headache to turn raw data into actionable
        > information.
        >
        > <Soapbox>
        > There is a reason why we have roads. In some previous planning process,
        > the best routes available to get from most locations to most other
        > locations were determined. Over time, however, society decides to add
        > additional roads, maybe removing unused roads. Imagine trying to keep
        > order if everyone could go from any point to any other point as they
        > wish. Chaos would ensue, and some of the chosen routes could be through
        > your yard. There is a reason we design databases to solve known
        > problems, and revise them when additional problems, or desired solutions
        > become evident. An EAV database invites chaos -which may not be bad, as
        > long as you have the development resources to make information out of
        > data.
        > </ Soapbox>
        >
        > Regards,
        >
        > Arnie Rowland, MVP (SQL Server)
        >
        > "You cannot do a kindness too soon because you never know how soon it
        > will be too late."
        > -Ralph Waldo Emerson
        >
        >
        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
        > Sent: Thursday, February 24, 2011 2:59 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: Re: [SQLQueriesNoCode] Pivot Help Enitity Attribute Value style
        > scenario
        >
        > So you meant like the one for months at:
        > http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial
        > -with-examples.aspx
        > i.e.
        >
        > SELECT *
        > FROM (
        > SELECT
        > YEAR(OrderDate) [Year],
        > CASE MONTH(OrderDate)
        > WHEN 1 THEN 'January'
        > WHEN 2 THEN 'February'
        > WHEN 3 THEN 'March'
        > WHEN 4 THEN 'April'
        > WHEN 5 THEN 'May'
        > WHEN 6 THEN 'June'
        > WHEN 7 THEN 'July'
        > WHEN 8 THEN 'August'
        > WHEN 8 THEN 'September'
        > WHEN 8 THEN 'October'
        > WHEN 8 THEN 'November'
        > WHEN 8 THEN 'December'
        > END as [Month],
        > SubTotal
        > FROM Sales.SalesOrderHeader
        > ) TableDate
        > PIVOT (
        > SUM(SubTotal)
        > FOR [Month] IN (
        > [January],[February],[March],[April],
        > [May],[June],[July],[August],
        > [September],[October],[November],[December]
        > )
        > ) PivotTable
        >
        > What amazes me is this very simple scenario conceptually has not been
        > solved elegantly in god knows how many versions of SQL Server.... Plus
        > it seems to me I will be building that SQL and executing it dynamically
        > not only do my KeyPairs vary they even vary for the report application.
        >
        > Some people will want some of the keypairs others will not, so it ends
        > up being a very dynamic situation. I may have to go with dynamic CREATE
        > TABLES since I will want to cast the values once not leave them in
        > nvarchar(n) form in the case of numeric/date/etc. other strongly typed
        > data.
        >
        > On Thu, Feb 24, 2011 at 5:30 PM, John Warner <john@...> wrote:
        > >
        > >
        > >
        > > You may need to do the pivot yourself what I mean is build a CASE WHEN
        >
        > > structure. Sort of the pivot table we made in the SQL Server 2000 and
        > > lower days.
        >
        > ------------------------------------
        >
        > Yahoo! Groups Links
        >
        >
        > [Non-text portions of this message have been removed]
        >
        >
        >


        [Non-text portions of this message have been removed]
      • Charles Carroll
        Thank you Arnie!!!!!!!! Thannnnnnnnnnnnnnnnnnnnnnnnnnks SELECT * FROM [TestPivot] PIVOT ( max(TestValue) FOR [TestName] IN ([Test 1],[Test 2],[Test
        Message 3 of 17 , Feb 24, 2011
        • 0 Attachment
          Thank you Arnie!!!!!!!! Thannnnnnnnnnnnnnnnnnnnnnnnnnks

          SELECT *
          FROM [TestPivot]
          PIVOT (
          max(TestValue)
          FOR [TestName] IN ([Test 1],[Test 2],[Test
          3],[UserName],[Device Name], [File Size],[Number of Pages Scanned],[Number
          of Documents Scanned])) pvt

          ORDER BY [TestPivot_ID]

          works marvelously now I can work on building it dynamically.

          On Thu, Feb 24, 2011 at 6:37 PM, Arnie Rowland <arnie@...> wrote:

          >
          >
          > This is one of those moments when we see that the 'elegant' (at least
          > for developers) Name-Value pair data scheme (EAV) can cause significant
          > work to reconstitute meaning from data.
          >
          > However, you were very close to the solution you desired. Make the
          > following alteration to the query:
          >
          > SELECT *
          > FROM [TestData]
          > PIVOT ( max(KeyValue)
          >
          > FOR [KeyName]
          > IN ( [Test1],
          > [Test2],
          > [Test3],
          > [UserName],
          > [DeviceName])
          > ) pvt
          > ORDER BY [Id]
          >


          [Non-text portions of this message have been removed]
        • Charles Carroll
          Yeah of course I read a ton including great things like: http://pratchev.blogspot.com/2010/07/refactoring-entity-attribute-value.html But this is not really
          Message 4 of 17 , Feb 24, 2011
          • 0 Attachment
            Yeah of course I read a ton including great things like:
            http://pratchev.blogspot.com/2010/07/refactoring-entity-attribute-value.html

            But this is not really coming from a Database it comes from a server
            and is collected via RPC and I get all this XML metadata and data
            about things that are coming and going from devices to CMS and
            Databases and there are some people who need to know for all of this
            traffic what it MEANS....

            When I collect it from the XML I collect it first as just XML the
            parsing happens based on what the user needs they may need just 2% of
            the data in the XML but I parse it all even the 98% I do not need
            hence all the KeyPairs that are not drawn into the PIVOT. I am
            considering some way to go from XML to the relational structure
            without passing through a PIVOT but the approach of capturing all
            keyPairs and letting them PIVOT what they need out gets them working
            and running reports and I will then work on maybe getting from the XML
            to Relational without even the PIVOT middle step.

            On Thu, Feb 24, 2011 at 7:03 PM, Charles Carroll <911@...> wrote:
            > Thank you Arnie!!!!!!!! Thannnnnnnnnnnnnnnnnnnnnnnnnnks
            >
            >     SELECT *
            >             FROM [TestPivot]
            >                     PIVOT (
            >                     max(TestValue)
            >                     FOR [TestName] IN ([Test 1],[Test 2],[Test
            > 3],[UserName],[Device Name], [File Size],[Number of Pages Scanned],[Number
            > of Documents Scanned])) pvt
            >
            >     ORDER BY [TestPivot_ID]
            >
            > works marvelously now I can work on building it dynamically.
            >
            > On Thu, Feb 24, 2011 at 6:37 PM, Arnie Rowland <arnie@...> wrote:
            >>
            >>
            >>
            >> This is one of those moments when we see that the 'elegant' (at least
            >> for developers) Name-Value pair data scheme (EAV) can cause significant
            >> work to reconstitute meaning from data.
            >>
            >> However, you were very close to the solution you desired. Make the
            >> following alteration to the query:
            >>
            >> SELECT *
            >> FROM [TestData]
            >> PIVOT ( max(KeyValue)
            >>
            >> FOR [KeyName]
            >> IN ( [Test1],
            >> [Test2],
            >> [Test3],
            >> [UserName],
            >> [DeviceName])
            >> ) pvt
            >> ORDER BY [Id]
            >
            >
          • Charles Carroll
            Is there some way to force a data type in the resultant PIVOT output i.e. INT, Currency and cast the data to it? Obviously in the below example
            Message 5 of 17 , Feb 24, 2011
            • 0 Attachment
              Is there some way to force a data type in the resultant PIVOT output
              i.e. INT, Currency and cast the data to it?

              Obviously in the below example [UserName],[Device Name] would be
              nvarchar(..) other data would be INTs or appropriate numeric types.

              On Thu, Feb 24, 2011 at 7:03 PM, Charles Carroll <911@...> wrote:
              > Thank you Arnie!!!!!!!! Thannnnnnnnnnnnnnnnnnnnnnnnnnks
              >
              >     SELECT *
              >             FROM [TestPivot]
              >                     PIVOT (
              >                     max(TestValue)
              >                     FOR [TestName] IN ([Test 1],[Test 2],[Test
              > 3],[UserName],[Device Name], [File Size],[Number of Pages Scanned],[Number
              > of Documents Scanned])) pvt
              >
              >     ORDER BY [TestPivot_ID]
              >
              > works marvelously now I can work on building it dynamically.
              >
              > On Thu, Feb 24, 2011 at 6:37 PM, Arnie Rowland <arnie@...> wrote:
              >>
              >>
              >>
              >> This is one of those moments when we see that the 'elegant' (at least
              >> for developers) Name-Value pair data scheme (EAV) can cause significant
              >> work to reconstitute meaning from data.
              >>
              >> However, you were very close to the solution you desired. Make the
              >> following alteration to the query:
              >>
              >> SELECT *
              >> FROM [TestData]
              >> PIVOT ( max(KeyValue)
              >>
              >> FOR [KeyName]
              >> IN ( [Test1],
              >> [Test2],
              >> [Test3],
              >> [UserName],
              >> [DeviceName])
              >> ) pvt
              >> ORDER BY [Id]
              >
              >
            • Arnie Rowland
              Glad to help Charles. And I understand that often we are stuck supporting ill designed logging applications... I like Plamen s article
              Message 6 of 17 , Feb 24, 2011
              • 0 Attachment
                Glad to help Charles. And I understand that often we are stuck
                supporting ill designed logging applications...

                I like Plamen's article
                <http://pratchev.blogspot.com/2010/07/refactoring-entity-attribute-value
                .html> .

                And if you didn't see my short note on the issue of EAV design, here it
                is:
                http://sqlblog.com/blogs/arnie_rowland/archive/2008/10/03/through-the-lo
                oking-glass-elegant-or-not.aspx


                Regards,

                Arnie Rowland, MVP (SQL Server)

                "You cannot do a kindness too soon because you never know how soon it
                will be too late."
                -Ralph Waldo Emerson




                -----Original Message-----
                From: SQLQueriesNoCode@yahoogroups.com
                [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
                Sent: Thursday, February 24, 2011 4:06 PM
                To: SQLQueriesNoCode@yahoogroups.com
                Subject: Re: [SQLQueriesNoCode] Pivot Help Enitity Attribute Value style
                scenario

                Thank you Arnie!!!!!!!! Thannnnnnnnnnnnnnnnnnnnnnnnnnks

                SELECT *
                FROM [TestPivot]
                PIVOT (
                max(TestValue)
                FOR [TestName] IN ([Test 1],[Test 2],[Test
                3],[UserName],[Device Name], [File Size],[Number of Pages
                Scanned],[Number of Documents Scanned])) pvt

                ORDER BY [TestPivot_ID]

                works marvelously now I can work on building it dynamically.

                On Thu, Feb 24, 2011 at 6:37 PM, Arnie Rowland <arnie@...> wrote:

                >
                >
                > This is one of those moments when we see that the 'elegant' (at least
                > for developers) Name-Value pair data scheme (EAV) can cause
                > significant work to reconstitute meaning from data.
                >
                > However, you were very close to the solution you desired. Make the
                > following alteration to the query:
                >
                > SELECT *
                > FROM [TestData]
                > PIVOT ( max(KeyValue)
                >
                > FOR [KeyName]
                > IN ( [Test1],
                > [Test2],
                > [Test3],
                > [UserName],
                > [DeviceName])
                > ) pvt
                > ORDER BY [Id]
                >


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



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

                Yahoo! Groups Links







                [Non-text portions of this message have been removed]
              • Arnie Rowland
                Instead of SELECT * , itemize the column list and cast each one as appropriate. SELECT cast([Test 1] AS int) Test 1 cast([Test 2] AS int) Test 2 ,
                Message 7 of 17 , Feb 24, 2011
                • 0 Attachment
                  Instead of 'SELECT *', itemize the column list and cast each one as
                  appropriate.

                  SELECT
                  cast([Test 1] AS int) 'Test 1'
                  cast([Test 2] AS int) 'Test 2',
                  cast([Test 3] AS int) 'Test 3',
                  [UserName],
                  [Device Name],
                  cast([File Size] AS bigint) 'File Size',
                  cast([Number of Pages Scanned] AS int) 'Pages',
                  cast([Number of Documents Scanned] AS int) 'Documents'
                  FROM [TestPivot]
                  PIVOT ( max(TestValue )
                  FOR [TestName] IN ( [Test 1],
                  [Test 2],
                  [Test 3],
                  [UserName],
                  [Device Name],
                  [File Size],
                  [Number of Pages Scanned],
                  [Number of Documents Scanned]
                  )
                  ) pvt
                  ORDER BY [TestPivot_ID]

                  Regards,

                  Arnie Rowland, MVP (SQL Server)

                  "You cannot do a kindness too soon because you never know how soon it
                  will be too late."
                  -Ralph Waldo Emerson




                  -----Original Message-----
                  From: SQLQueriesNoCode@yahoogroups.com
                  [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
                  Sent: Thursday, February 24, 2011 4:15 PM
                  To: SQLQueriesNoCode@yahoogroups.com
                  Subject: Re: [SQLQueriesNoCode] Pivot Help Enitity Attribute Value style
                  scenario

                  Is there some way to force a data type in the resultant PIVOT output
                  i.e. INT, Currency and cast the data to it?

                  Obviously in the below example [UserName],[Device Name] would be
                  nvarchar(..) other data would be INTs or appropriate numeric types.

                  On Thu, Feb 24, 2011 at 7:03 PM, Charles Carroll <911@...>
                  wrote:
                  > Thank you Arnie!!!!!!!! Thannnnnnnnnnnnnnnnnnnnnnnnnnks
                  >
                  > SELECT *
                  > FROM [TestPivot]
                  > PIVOT (
                  > max(TestValue)
                  > FOR [TestName] IN ([Test 1],[Test 2],[Test
                  > 3],[UserName],[Device Name], [File Size],[Number of Pages
                  > Scanned],[Number of Documents Scanned])) pvt
                  >
                  > ORDER BY [TestPivot_ID]
                  >
                  > works marvelously now I can work on building it dynamically.
                  >
                  > On Thu, Feb 24, 2011 at 6:37 PM, Arnie Rowland <arnie@...> wrote:
                  >>
                  >>
                  >>
                  >> This is one of those moments when we see that the 'elegant' (at least

                  >> for developers) Name-Value pair data scheme (EAV) can cause
                  >> significant work to reconstitute meaning from data.
                  >>
                  >> However, you were very close to the solution you desired. Make the
                  >> following alteration to the query:
                  >>
                  >> SELECT *
                  >> FROM [TestData]
                  >> PIVOT ( max(KeyValue)
                  >>
                  >> FOR [KeyName]
                  >> IN ( [Test1],
                  >> [Test2],
                  >> [Test3],
                  >> [UserName],
                  >> [DeviceName])
                  >> ) pvt
                  >> ORDER BY [Id]
                  >
                  >


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

                  Yahoo! Groups Links







                  [Non-text portions of this message have been removed]
                • Arnie Rowland
                  Of course, using CAST() as I indicated will work for conforming data. Non-conforming data will cause the entire query to fail. Regards, Arnie Rowland, MVP (SQL
                  Message 8 of 17 , Feb 24, 2011
                  • 0 Attachment
                    Of course, using CAST() as I indicated will work for conforming data. Non-conforming data will cause the entire query to fail.

                    Regards,

                    Arnie Rowland, MVP (SQL Server)

                    "You cannot do a kindness too soon because you never know how soon it will be too late."
                    -Ralph Waldo Emerson




                    -----Original Message-----
                    From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
                    Sent: Thursday, February 24, 2011 4:15 PM
                    To: SQLQueriesNoCode@yahoogroups.com
                    Subject: Re: [SQLQueriesNoCode] Pivot Help Enitity Attribute Value style scenario

                    Is there some way to force a data type in the resultant PIVOT output i.e. INT, Currency and cast the data to it?

                    Obviously in the below example [UserName],[Device Name] would be
                    nvarchar(..) other data would be INTs or appropriate numeric types.

                    On Thu, Feb 24, 2011 at 7:03 PM, Charles Carroll <911@...> wrote:
                    > Thank you Arnie!!!!!!!! Thannnnnnnnnnnnnnnnnnnnnnnnnnks
                    >
                    >     SELECT *
                    >             FROM [TestPivot]
                    >                     PIVOT (
                    >                     max(TestValue)
                    >                     FOR [TestName] IN ([Test 1],[Test 2],[Test
                    > 3],[UserName],[Device Name], [File Size],[Number of Pages
                    > Scanned],[Number of Documents Scanned])) pvt
                    >
                    >     ORDER BY [TestPivot_ID]
                    >
                    > works marvelously now I can work on building it dynamically.
                    >
                    > On Thu, Feb 24, 2011 at 6:37 PM, Arnie Rowland <arnie@...> wrote:
                    >>
                    >>
                    >>
                    >> This is one of those moments when we see that the 'elegant' (at least
                    >> for developers) Name-Value pair data scheme (EAV) can cause
                    >> significant work to reconstitute meaning from data.
                    >>
                    >> However, you were very close to the solution you desired. Make the
                    >> following alteration to the query:
                    >>
                    >> SELECT *
                    >> FROM [TestData]
                    >> PIVOT ( max(KeyValue)
                    >>
                    >> FOR [KeyName]
                    >> IN ( [Test1],
                    >> [Test2],
                    >> [Test3],
                    >> [UserName],
                    >> [DeviceName])
                    >> ) pvt
                    >> ORDER BY [Id]
                    >
                    >


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

                    Yahoo! Groups Links
                  • John Warner
                    Thanks Charles and Arnie for a very interesting thread to read (and links). Charles, glad Arnie had a far (like this was a surprise) better solution to what I
                    Message 9 of 17 , Feb 25, 2011
                    • 0 Attachment
                      Thanks Charles and Arnie for a very interesting thread to read (and
                      links). Charles, glad Arnie had a far (like this was a surprise) better
                      solution to what I had in mind.

                      John Warner


                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                      > Sent: Thursday, February 24, 2011 9:30 PM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: RE: [SQLQueriesNoCode] Pivot Help Enitity Attribute Value style
                      > scenario
                      >
                      > Of course, using CAST() as I indicated will work for conforming data.
                      Non-
                      > conforming data will cause the entire query to fail.
                      >
                      > Regards,
                      >
                      > Arnie Rowland, MVP (SQL Server)
                      >
                      > "You cannot do a kindness too soon because you never know how soon it
                      will
                      > be too late."
                      > -Ralph Waldo Emerson
                      >
                      >
                      >
                      >
                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
                      > Sent: Thursday, February 24, 2011 4:15 PM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: Re: [SQLQueriesNoCode] Pivot Help Enitity Attribute Value style
                      > scenario
                      >
                      > Is there some way to force a data type in the resultant PIVOT output
                      i.e. INT,
                      > Currency and cast the data to it?
                      >
                      > Obviously in the below example [UserName],[Device Name] would be
                      > nvarchar(..) other data would be INTs or appropriate numeric types.
                      >
                      > On Thu, Feb 24, 2011 at 7:03 PM, Charles Carroll <911@...>
                      > wrote:
                      > > Thank you Arnie!!!!!!!! Thannnnnnnnnnnnnnnnnnnnnnnnnnks
                      > >
                      > >     SELECT *
                      > >             FROM [TestPivot]
                      > >                     PIVOT (
                      > >                     max(TestValue)
                      > >                     FOR [TestName] IN ([Test 1],[Test 2],[Test
                      > > 3],[UserName],[Device Name], [File Size],[Number of Pages
                      > > Scanned],[Number of Documents Scanned])) pvt
                      > >
                      > >     ORDER BY [TestPivot_ID]
                      > >
                      > > works marvelously now I can work on building it dynamically.
                      > >
                      > > On Thu, Feb 24, 2011 at 6:37 PM, Arnie Rowland <arnie@...> wrote:
                      > >>
                      > >>
                      > >>
                      > >> This is one of those moments when we see that the 'elegant' (at least
                      > >> for developers) Name-Value pair data scheme (EAV) can cause
                      > >> significant work to reconstitute meaning from data.
                      > >>
                      > >> However, you were very close to the solution you desired. Make the
                      > >> following alteration to the query:
                      > >>
                      > >> SELECT *
                      > >> FROM [TestData]
                      > >> PIVOT ( max(KeyValue)
                      > >>
                      > >> FOR [KeyName]
                      > >> IN ( [Test1],
                      > >> [Test2],
                      > >> [Test3],
                      > >> [UserName],
                      > >> [DeviceName])
                      > >> ) pvt
                      > >> ORDER BY [Id]
                      > >
                      > >
                      >
                      >
                      > ------------------------------------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      > ------------------------------------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                    • Noman Aftab
                      Hi, I have got database MDF/LDF files and I am trying to attach it on my database server. First I thought that its a SQL Server 2005 version and I tried to
                      Message 10 of 17 , Feb 26, 2011
                      • 0 Attachment
                        Hi,
                        I have got database MDF/LDF files and I am trying to attach it on my database
                        server.
                        First I thought that its a SQL Server 2005 version and I tried to attach but got
                        the following error:

                        The database 'Db_Name' cannot be opened because it is version 655. This server
                        supports version 612 and earlier. A downgrade path is not supported.
                        Could not open new database 'Db_Name'. CREATE DATABASE is aborted. (Microsoft
                        SQL Server, Error: 948)

                        Then I tried to attach on SQL 2008 Instance and got the following error:

                        The database 'Db_Name' cannot be opened because it is version 655. This server
                        supports version 643 and earlier. A downgrade path is not supported.
                        Could not open new database 'Db_Name'. CREATE DATABASE is aborted. (Microsoft
                        SQL Server, Error: 948)

                        2005 supports upto 612
                        2008 supports upto 643
                        what version do I need to support 655?

                        on SQL Server 2008, the query "SELECT @@VERSION" gives the following result:

                        Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1300.13 (Intel X86) Feb
                        8 2008 00:06:52 Copyright (c) 1988-2007 Microsoft Corporation Developer
                        Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

                        Kindly help me out! Best Wishes,
                        Noman Aftab


                        http://www.corpus.quran.com/wordbyword.jsp
                        http://www.jalandhari.qsh.eu



                        [Non-text portions of this message have been removed]
                      • John Warner
                        2008 R2 ??? John Warner ... database ... but got ... Developer
                        Message 11 of 17 , Feb 26, 2011
                        • 0 Attachment
                          2008 R2 ???

                          John Warner


                          > -----Original Message-----
                          > From: SQLQueriesNoCode@yahoogroups.com
                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                          > Sent: Saturday, February 26, 2011 2:36 PM
                          > To: SQLQueriesNoCode@yahoogroups.com
                          > Subject: [SQLQueriesNoCode] SQL Server database version issue
                          >
                          > Hi,
                          > I have got database MDF/LDF files and I am trying to attach it on my
                          database
                          > server.
                          > First I thought that its a SQL Server 2005 version and I tried to attach
                          but got
                          > the following error:
                          >
                          > The database 'Db_Name' cannot be opened because it is version 655. This
                          > server
                          > supports version 612 and earlier. A downgrade path is not supported.
                          > Could not open new database 'Db_Name'. CREATE DATABASE is aborted.
                          > (Microsoft
                          > SQL Server, Error: 948)
                          >
                          > Then I tried to attach on SQL 2008 Instance and got the following error:
                          >
                          > The database 'Db_Name' cannot be opened because it is version 655. This
                          > server
                          > supports version 643 and earlier. A downgrade path is not supported.
                          > Could not open new database 'Db_Name'. CREATE DATABASE is aborted.
                          > (Microsoft
                          > SQL Server, Error: 948)
                          >
                          > 2005 supports upto 612
                          > 2008 supports upto 643
                          > what version do I need to support 655?
                          >
                          > on SQL Server 2008, the query "SELECT @@VERSION" gives the following
                          > result:
                          >
                          > Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1300.13 (Intel X86)
                          > Feb
                          > 8 2008 00:06:52 Copyright (c) 1988-2007 Microsoft Corporation
                          Developer
                          > Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
                          >
                          > Kindly help me out! Best Wishes,
                          > Noman Aftab
                          >
                          >
                          > http://www.corpus.quran.com/wordbyword.jsp
                          > http://www.jalandhari.qsh.eu
                          >
                          >
                          >
                          > [Non-text portions of this message have been removed]
                          >
                          >
                          >
                          > ------------------------------------
                          >
                          > Yahoo! Groups Links
                          >
                          >
                          >
                        • Hendra Prakasa
                          If I m not mistaken its sql server 2008 R2 Sent from my BlackBerry® powered by Sinyal Kuat INDOSAT ... From: Noman Aftab Sender:
                          Message 12 of 17 , Feb 26, 2011
                          • 0 Attachment
                            If I'm not mistaken its sql server 2008 R2
                            Sent from my BlackBerry�
                            powered by Sinyal Kuat INDOSAT

                            -----Original Message-----
                            From: Noman Aftab <noman17pk@...>
                            Sender: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
                            Date: Sun, 27 Feb 2011 02:36:03
                            To: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode@yahoogroups.com>
                            Reply-To: "SQLQueriesNoCode@yahoogroups.com"
                            <SQLQueriesNoCode@yahoogroups.com>
                            Subject: [SQLQueriesNoCode] SQL Server database version issue



                            Hi,
                            I have got database MDF/LDF files and I am trying to attach it on my database
                            server.
                            First I thought that its a SQL Server 2005 version and I tried to attach but got
                            the following error:

                            The database 'Db_Name' cannot be opened because it is version 655. This server
                            supports version 612 and earlier. A downgrade path is not supported.
                            Could not open new database 'Db_Name'. CREATE DATABASE is aborted. (Microsoft
                            SQL Server, Error: 948)

                            Then I tried to attach on SQL 2008 Instance and got the following error:

                            The database 'Db_Name' cannot be opened because it is version 655. This server
                            supports version 643 and earlier. A downgrade path is not supported.
                            Could not open new database 'Db_Name'. CREATE DATABASE is aborted. (Microsoft
                            SQL Server, Error: 948)

                            2005 supports upto 612
                            2008 supports upto 643
                            what version do I need to support 655?

                            on SQL Server 2008, the query "SELECT @@VERSION" gives the following result:

                            Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1300.13 (Intel X86) Feb
                            8 2008 00:06:52 Copyright (c) 1988-2007 Microsoft Corporation Developer
                            Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

                            Kindly help me out! Best Wishes,
                            Noman Aftab

                            http://www.corpus.quran.com/wordbyword.jsp
                            http://www.jalandhari.qsh.eu

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






                            [Non-text portions of this message have been removed]
                          • Barry Sweeney
                            ... database ... Hi Noman, Are you able to restore from a backup file instead, or on the original server, drop the compatibility version back to 100 and then
                            Message 13 of 17 , Feb 27, 2011
                            • 0 Attachment
                              >
                              > Hi,
                              > I have got database MDF/LDF files and I am trying to attach it on my
                              database
                              > server.

                              Hi Noman,

                              Are you able to restore from a backup file instead, or on the original
                              server, drop the compatibility version back to 100 and then transfer mdf/ldf
                              or backup?

                              Barry
                            • Noman Aftab
                              The client system was reinstalled and I only got my hands on LDF/MDF files. Fortunately, got a backup of tables/views scripts, which helped me to re make the
                              Message 14 of 17 , Feb 27, 2011
                              • 0 Attachment
                                The client system was reinstalled and I only got my hands on LDF/MDF files.
                                Fortunately, got a backup of tables/views scripts, which helped me to re make
                                the database, but without data.
                                Thanks, anyways.
                                Best Wishes,
                                Noman Aftab


                                http://www.corpus.quran.com/wordbyword.jsp
                                http://www.jalandhari.qsh.eu





                                ________________________________
                                From: Barry Sweeney <barry@...>
                                To: SQLQueriesNoCode@yahoogroups.com
                                Sent: Sun, 27 February, 2011 6:10:29 PM
                                Subject: RE: [SQLQueriesNoCode] SQL Server database version issue


                                >
                                > Hi,
                                > I have got database MDF/LDF files and I am trying to attach it on my
                                database
                                > server.

                                Hi Noman,

                                Are you able to restore from a backup file instead, or on the original
                                server, drop the compatibility version back to 100 and then transfer mdf/ldf
                                or backup?

                                Barry






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