Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The simplest way to get the result would be using an aggregate function with a CASE expression:</p> <pre><code>select scenario_code, max(case when indicator = 2 then period end) [Period 2], max(case when indicator = 2 then value end) [Value 2], max(case when indicator = 3 then period end) [Period 3], max(case when indicator = 3 then value end) [Value 3], max(case when indicator = 4 then period end) [Period 4], max(case when indicator = 4 then value end) [Value 4], [area code], Release_Code from yourtable group by scenario_code, [area code], Release_Code </code></pre> <p>See <a href="http://sqlfiddle.com/#!3/49777/9" rel="nofollow">SQL Fiddle with Demo</a></p> <p>But you can use the PIVOT function to get the result but you would also need to unpivot the <code>Period</code> and <code>Value</code> columns first, since you want to pivot on two columns.</p> <p>Since you are using SQL Server 2012 you can use CROSS APPLY with VALUES to unpivot. The basic syntax will be:</p> <pre><code>select scenario_code, [area code], release_code, col = col +' ' +cast(indicator as varchar(10)), val from yourtable cross apply ( values ('Period', convert(varchar(10), period, 120)), ('Value', convert(varchar(10), value)) ) c (col, val); </code></pre> <p>See <a href="http://sqlfiddle.com/#!3/49777/7" rel="nofollow">SQL Fiddle with Demo</a>. This is going to get your data into the format:</p> <pre><code>| SCENARIO_CODE | AREA CODE | RELEASE_CODE | COL | VAL | |---------------|-----------|--------------|----------|------------| | 7 | OP014 | 17 | Period 2 | 2000-06-13 | | 7 | OP014 | 17 | Value 2 | 1000 | | 16 | OP014 | 17 | Period 2 | 2000-09-12 | | 16 | OP014 | 17 | Value 2 | 1100 | </code></pre> <p>You'll notice that we had to cast/convert both columns to the same datatype in order for this unpivoting process to work. Once the data has been unpivoted, then you can easily apply the PIVOT function and convert your values in <code>COL</code> to the new column headers:</p> <pre><code>select scenario_code, [Period 2], [Value 2], [Period 3], [Value 3], [Period 4], [Value 4], [area code], release_code from ( select scenario_code, [area code], release_code, col = col +' ' +cast(indicator as varchar(10)), val from yourtable cross apply ( values ('Period', convert(varchar(10), period, 120)), ('Value', convert(varchar(10), value)) ) c (col, val) ) d pivot ( max(val) for col in ([Period 2], [Value 2], [Period 3], [Value 3], [Period 4], [Value 4]) ) piv; </code></pre> <p>See <a href="http://sqlfiddle.com/#!3/49777/8" rel="nofollow">SQL Fiddle with Demo</a>. Both versions give a final result of:</p> <pre><code>| SCENARIO_CODE | PERIOD 2 | VALUE 2 | PERIOD 3 | VALUE 3 | PERIOD 4 | VALUE 4 | AREA CODE | RELEASE_CODE | |---------------|------------|---------|------------|---------|------------|---------|-----------|--------------| | 7 | 2000-06-13 | 1000 | 2000-01-12 | 1300 | 2000-06-12 | 600 | OP014 | 17 | | 16 | 2000-09-12 | 1100 | 2000-06-17 | 500 | 2000-12-12 | 650 | OP014 | 17 | | 17 | 2002-06-22 | 1200 | 2008-05-04 | 550 | 2013-06-12 | 150 | OP014 | 17 | </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POPivot a table on a value but group the data on one line by another?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTaryn
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POPivot a table on a value but group the data on one line by another?
      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. VTAcceptedByOriginator
    1. COBoth great answers
      singulars
      1. PO
      1. USUseless_Wizard
 

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