Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>As has been said this isn't actually possible, the closest you could get is:</p> <pre><code>January2014CalculationDate | January2014PLResult | February2014CalculationDate | February2014PLResult ---------------------------+---------------------+-----------------------------+------------------ 2014-01-02 | 100 | 2014-02-03 | 300 2014-01-03 | 200 | 2014-02-04 | 400 NULL | NULL | 2014-02-27 | 500 </code></pre> <p>And even that is not simple and I would still advise handling formatting like this outside of sql. The first step is to partition the data by month, and then rank the dates in each month:</p> <pre><code>SELECT CalculationDate, PLResult, CalculationMonth, DenseRank = DENSE_RANK() OVER(PARTITION BY CalculationMonth ORDER BY CalculationDate) FROM ( SELECT CalculationDate, PLResult, CalculationMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) pl; </code></pre> <p>This gives:</p> <pre><code>CalculationDate PLResult CalculationMonth DenseRank 2014-01-02 100 2014-01-01 1 2014-01-03 200 2014-01-01 2 2014-02-03 300 2014-02-01 1 2014-02-04 400 2014-02-01 2 2014-02-27 500 2014-02-01 3 </code></pre> <p>You can then pivot this data:</p> <pre><code>WITH Data AS ( SELECT CalculationDate, PLResult, CalculationMonth, DenseRank = DENSE_RANK() OVER(PARTITION BY CalculationMonth ORDER BY CalculationDate) FROM ( SELECT CalculationDate, PLResult, CalculationMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) pl ) SELECT Jan2014CalcDate = MIN(CASE WHEN CalculationMonth = '20140101' THEN CalculationDate END), Jan2014Result = SUM(CASE WHEN CalculationMonth = '20140101' THEN PLResult END), Feb2014CalcDate = MIN(CASE WHEN CalculationMonth = '20140201' THEN CalculationDate END), Feb2014Result = SUM(CASE WHEN CalculationMonth = '20140201' THEN PLResult END) FROM Data GROUP BY DenseRank ORDER BY DenseRank; </code></pre> <p>This gives:</p> <pre><code>Jan2014CalcDate Jan2014Result Feb2014CalcDate Feb2014Result 2014-01-02 100 2014-02-03 300 2014-01-03 200 2014-02-04 400 NULL NULL 2014-02-27 500 </code></pre> <p>Then since you have a dynamic number of months you need to build the above statement dynamically and use <code>SP_EXECUTESQL</code> to run it:</p> <pre><code>DECLARE @SQL NVARCHAR(MAX) = ''; WITH Months AS ( SELECT M, ColName = DATENAME(MONTH, M) + DATENAME(YEAR, M), CharFormat = CONVERT(VARCHAR(8), M, 112) FROM ( SELECT DISTINCT M = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) m ) SELECT @SQL = 'WITH Data AS ( SELECT CalculationDate, PLResult, CalculationMonth, DenseRank = DENSE_RANK() OVER(PARTITION BY CalculationMonth ORDER BY CalculationDate) FROM ( SELECT CalculationDate, PLResult, CalculationMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) pl ) SELECT ' + STUFF(( SELECT ', ' + ColName + 'CalculationDate = MIN(CASE WHEN CalculationMonth = ''' + CharFormat + ''' THEN CalculationDate END), ' + ColName + 'PLResult = SUM(CASE WHEN CalculationMonth = ''' + CharFormat + ''' THEN PLResult END)' FROM Months ORDER BY M FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 'FROM Data GROUP BY DenseRank ORDER BY DenseRank;'; EXECUTE SP_EXECUTESQL @SQL; </code></pre> <p><strong><a href="http://sqlfiddle.com/#!3/d41d8/27382" rel="nofollow">Example on SQL Fiddle</a></strong></p> <p><em>Please note, I still advise against this technique, and think SQL should be left to storing/retrieving data, and the presentation layer for formatting it</em></p>
    singulars
    1. This table or related slice is empty.
    1. POTransform vertical result into horizontal mode (T-SQL)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGarethD
    1. USGarethD
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POTransform vertical result into horizontal mode (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. VTUpMod
    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. COThanks very much for your effort in explaining in details.
      singulars
      1. PO
      1. UShadi teo
 

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