Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This would not be possible with a view, unless the view is dynamically recreated on the fly every time <code>FieldDef</code> changes because view schemas are locked-in at creation time. However, it may be possible with a stored procedure, which may or may not work depending on how you are using it.</p> <p><strong>Edit 1</strong></p> <p>Here is a sample query that works just for your current field names, and would have to be modified by dynamic SQL to work in general:</p> <p><strong>Edit 2</strong></p> <p>Modified to grab the newest values from the customer field table</p> <pre><code>with CustomerFieldNewest as ( select cf1.* from customerfield cf1 inner join ( select customerid, fielddefid, max(capturedate) as maxcapturedate from customerfield cf2 group by customerid, fielddefid ) cf2 on cf1.customerid = cf2.customerid and cf1.fielddefid = cf2.fielddefid and cf1.capturedate = cf2.maxcapturedate ) ,CustomerFieldPivot as ( select C.ID as ID ,max(case when F.Name = 'Mobile' then CF.ValueText end) as Mobile ,max(case when F.Name = 'DateOfBirth' then CF.ValueDate end) as DateOfBirth from Customer C left join CustomerFieldNewest CF on C.ID = CF.CustomerID left join FieldDef F on F.ID = CF.FieldDefID group by C.ID ) select C.* ,P.Mobile ,P.DateOfBirth from Customer C left join CustomerFieldPivot P on C.ID = P.ID </code></pre> <p><strong>Edit 3</strong></p> <p>Here is T-SQL code to generate the view on the fly based on the current set of fields in FieldDef (this assumes the view <code>CustomerView</code> already exists, so you will need to create it first as a blank definition or you will get an error). I'm not sure about the performance of all this, but it should work correctly.</p> <pre><code>declare @sql varchar(max) declare @fielddef varchar(max) declare @fieldlist varchar(max) select @fielddef = coalesce(@fielddef + ', ' + CHAR(13) + CHAR(10), '') + ' max(case when F.Name = ''' + F.Name + ''' then CF.' + case F.FieldType when 'T' then 'ValueText' when 'N' then 'ValueNumber' when 'D' then 'ValueDate' end + ' end) as [' + F.Name + ']' ,@fieldlist = coalesce(@fieldlist + ', ' + CHAR(13) + CHAR(10), '') + ' [' + F.Name + ']' from FieldDef F set @sql = ' alter view [CustomerView] as with CustomerFieldNewest as ( select cf1.* from customerfield cf1 inner join ( select customerid, fielddefid, max(capturedate) as maxcapturedate from customerfield cf2 group by customerid, fielddefid ) cf2 on cf1.customerid = cf2.customerid and cf1.fielddefid = cf2.fielddefid and cf1.capturedate = cf2.maxcapturedate ) ,CustomerFieldPivot as ( select C.ID as ID, ' + @fielddef + ' from Customer C left join CustomerFieldNewest CF on C.ID = CF.CustomerID left join FieldDef F on F.ID = CF.FieldDefID group by C.ID ) select C.*, ' + @fieldlist + ' from Customer C left join CustomerFieldPivot P on C.ID = P.ID ' print @sql exec(@sql) select * from CustomerView </code></pre>
    singulars
    1. This table or related slice is empty.
    1. PODynamic field definitions - can this be done in T-SQL?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmellamokb
    1. USmellamokb
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODynamic field definitions - can this be done in T-SQL?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COOk, thanks. The problem is that the table may contain up to 20 million customer records. So I don't want to create a SP to trawl through this each time. Is there a way to code the SP to avoid this?
      singulars
      1. PO
      1. USSimonGoldstone
    2. COWOW! That looks great. I'll test that out and come back to you. Recreating the query each time a new field def is created is not a problem. One other thing... does the max() function give me the latest mobile phone number or just the biggest alphabetically? I need the latest one based on CaptureDate
      singulars
      1. PO
      1. USSimonGoldstone
    3. CO@Simon: I modified to filter out only the newest customer field values based on CaptureDate.
      singulars
      1. PO
      1. USmellamokb
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload