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

OPTIMAL approach to KeyPair table flatten shredder

Expand Messages
  • Charles Carroll
    ID TestName TestValue StartDate EndDate =========================================== 1 Test1 100 2 Test2 Blah 3
    Message 1 of 1 , Feb 24, 2011
      ID TestName TestValue StartDate EndDate
      1 Test1 100
      2 Test2 Blah
      3 Test3 200
      4 Test1 300
      5 UserName Ted Smith
      6 DeviceName Hp 900x
      7 DeviceName Rico 107ex

      Need it made into

      ID Test1 Test2 Test3 UserName DeviceName StartDate EndDate
      1 100 null null null null
      2 null Blah null null null
      3 null null 200 null null
      4 300 null null null null
      5 null null null Ted Smith null
      6 null null null null Hp 900x
      7 null null null null Rico 107ex

      My first instinct is to dynamically build a create table i.e.

      REATE TABLE [dbo].[ReportPivot_1](
      [ReportPivot1_ID] [bigint] IDENTITY(1,1) NOT NULL,
      [Test1] int NULL,
      [Test2] int NULL,
      [Test3] int NULL,
      [UserName] varchar(31) NULL,
      [DeviceName] varchar(31) NULL,
      [StartDate] [datetime2](7) NOT NULL,
      [EndDate] [datetime2](7) NOT NULL

      But what is most efficient way to shuttle in data I don't want to use
      a CURSOR and billions of INSERT statements though I obviously could
      because not only would perf suck it would not be very set oriented but
      if I could construct a set based / subquery INSERT great where I just
      did in this example n queries in this example 5 i.e.

      My assumption is I will be distinct'ing the [TestName] column and for
      the sakes of this group lets just assume using that to build 1 create
      table and 1 or more INSERTS/SELECT INTO (I assume I need as many as I
      have distinct values) .,,,,

      What would the correct elegant INSERT / SELECT INTO whatever look like?

      (The real application goes through some additional steps because the
      Users of app have tables about ignoring KeyPairs, synonyms i.e.
      'User', 'Username' could resolve to same keypair even if rows are
      labelled differently and the KeyPairs are associated with the correct
      data types i.e. dates, ints, nvarchars, currency, etc. to power the
      create table data type assignments and casts on the inserts but those
      steps are not interesting to solving the main issue.)
    Your message has been successfully submitted and would be delivered to recipients shortly.