Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I will post a few examples from <strong><a href="https://stackoverflow.com/questions/1762305/sql-server-one-table-with-400-columns-or-40-tables-with-10-columns/1771094#1771094">this model</a></strong> -- because I already have them. Both models are very similar, so you should not have too much trouble adopting this technique.</p> <p>When it comes to headache, I find that the simplest way is to go step by step, and optimize later.</p> <p><strong>Step 1</strong>.<br> Create a view to flatten the model; (<strong><a href="https://stackoverflow.com/questions/1762305/sql-server-one-table-with-400-columns-or-40-tables-with-10-columns/1771094#1771094">see the model</a></strong>)</p> <pre><code>CREATE VIEW dbo.vProperties AS SELECT m.MachineID AS [Machine ID] ,s.SetupID AS [Setup ID] ,p.PropertyID AS [Property ID] ,t.PropertyTypeID AS [Property Type ID] ,m.Name AS [Machine Name] ,s.Name AS [Setup Name] ,t.Name AS [Property Type Name] ,t.IsTrait AS [Is Trait] ,x.Value AS [Measurement Value] ,x.Unit AS [Unit] ,y.Value AS [Trait] FROM dbo.Machine AS m JOIN dbo.Setup AS s ON s.MachineID = m.MachineID JOIN dbo.Property AS p ON p.SetupID = s.SetupID JOIN dbo.PropertyType AS t ON t.PropertyTypeID = p.PropertyTypeID LEFT JOIN dbo.Measurement AS x ON x.PropertyID = p.PropertyID LEFT JOIN dbo.Trait AS y ON y.PropertyID = p.PropertyID </code></pre> <p><strong>Step 2</strong>.</p> <p>Create a view to generate only <code>[Setup Name], [Property Type Name], [Value]</code>; note that in this one the measurement value and trait end up in the same column. You would probably use <code>JobName, ParameterTypeName, Value</code></p> <pre><code>CREATE VIEW dbo.vSetupValues AS SELECT [Setup Name] ,[Property Type Name] ,COALESCE(cast([Measurement Value] AS varchar(50)), [Trait]) AS [Val] FROM dbo.vProperties </code></pre> <p><strong>Step 3</strong>.</p> <p>Create list of properties (Parameters) with a column to order by</p> <pre><code>DECLARE @Props TABLE ( id int IDENTITY (1,1) ,PropName varchar(50) ); INSERT INTO @Props (PropName) SELECT DISTINCT [Name] FROM dbo.PropertyType </code></pre> <p><strong>Step 4</strong>.</p> <p>Now I will dynamically create the query text</p> <pre><code>DECLARE @qw TABLE( id int IDENTITY (1,1) , txt nchar(500) ) INSERT INTO @qw (txt) SELECT 'SELECT' UNION SELECT '[Setup Name]' ; INSERT INTO @qw (txt) SELECT ',MAX(CASE [Property Type Name] WHEN ''' + PropName + ''' THEN Val ELSE NULL END) AS [' + PropName + ']' FROM @Props ORDER BY id; INSERT INTO @qw (txt) SELECT 'FROM dbo.vSetupValues' UNION SELECT 'GROUP BY [Setup Name]' UNION SELECT 'ORDER BY [Setup Name]'; </code></pre> <p><strong>Step 5</strong>.</p> <p>And here is the text of the query, form this point I can package this into a stored procedure, another view, or into a variable to use as dynamic sql.</p> <pre><code>SELECT txt FROM @qw </code></pre> <p>returns</p> <pre><code>SELECT [Setup Name] ,MAX(CASE [Property Type Name] WHEN 'Diameter LSL' THEN [Val] ELSE NULL END) AS [Diameter LSL] ,MAX(CASE [Property Type Name] WHEN 'Diameter USL' THEN [Val] ELSE NULL END) AS [Diameter USL] ,MAX(CASE [Property Type Name] WHEN 'Force LSL' THEN [Val] ELSE NULL END) AS [Force LSL] ,MAX(CASE [Property Type Name] WHEN 'Force USL' THEN [Val] ELSE NULL END) AS [Force USL] ,MAX(CASE [Property Type Name] WHEN 'Leak LSL' THEN [Val] ELSE NULL END) AS [Leak LSL] ,MAX(CASE [Property Type Name] WHEN 'Leak USL' THEN [Val] ELSE NULL END) AS [Leak USL] ,MAX(CASE [Property Type Name] WHEN 'Press Travel LSL' THEN [Val] ELSE NULL END) AS [Press Travel LSL] ,MAX(CASE [Property Type Name] WHEN 'Press Travel USL' THEN [Val] ELSE NULL END) AS [Press Travel USL] ,MAX(CASE [Property Type Name] WHEN 'Seal Height LSL' THEN [Val] ELSE NULL END) AS [Seal Height LSL] ,MAX(CASE [Property Type Name] WHEN 'Seal Height USL' THEN [Val] ELSE NULL END) AS [Seal Height USL] FROM dbo.vSetupValues GROUP BY [Setup Name] ORDER BY [Setup Name] </code></pre> <p>And if I run this:<br/> <a href="http://www.damirsystems.com/dp_images/machinesetup_results.png" rel="nofollow noreferrer">alt text http://www.damirsystems.com/dp_images/machinesetup_results.png</a></p> <p><br/> <strong>UPDATE</strong>: fixed bug at step 4, was missing max() and added results example.</p>
    singulars
    1. This table or related slice is empty.
    1. POSQL Syntax to Pivot multiple tables
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USDamir Sudarevic
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSQL Syntax to Pivot multiple tables
      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. VTUndeletion
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you... your time and efforts are very much appreciated. Bit more complicated than I first though this task would be so will be sifting through your example today and will break it down into steps.
      singulars
      1. PO
      1. USBelliez
    2. CODamir, Your a genius, thank you. That works perfectly. Just one last question. How would I run this command like you have above. Step 5 "SELECT txt FROM @qw" display the command in a table (via SQL Management Studio) and I have to copy and paste it into a new query. Again, thank you
      singulars
      1. PO
      1. USBelliez
    3. COThe idea is to "package" the final query into a view, a procedure or a variable -- depending on how often your list of parameters may change. If those 400 parameters change few times a year, use view and build changes manually. If they change daily, put this into a variable and execute as dynamic sql -- also automate steps 3 and 4.
      singulars
      1. PO
      1. USDamir Sudarevic
 

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