StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPivot a table on a value but group the data on one line by another?
text
Body
copied!<p>My table </p> <pre><code>CREATE TABLE #table ([Indicator] int, [Scenario_code] smallint, [period] nvarchar(50), [Value] int, [AREA code] nvarchar(10), [Release_Code] int) ; INSERT INTO #table ([Indicator], [Scenario_code], [period], [Value], [AREA code], [Release_Code]) VALUES (2, 7, '2000-06-13', 1000, 'OP014', 17), (2, 16, '2000-09-12', 1100, 'OP014', 17), (2, 17, '2002-06-22', 1200, 'OP014', 17), (3, 7, '2000-01-12', 1300, 'OP014', 17), (3, 16, '2000-06-17', 500, 'OP014', 17), (3, 17, '2008-05-04', 550, 'OP014', 17), (4, 7, '2000-06-12', 600, 'OP014', 17), (4, 16, '2000-12-12', 650, 'OP014', 17), (4, 17, '2013-06-12', 150, 'OP014', 17) </code></pre> <p>I'd like the fields [period] and [Value] to be pivoted somehow based on their [indicator] and [scenario_code] fields. There are three indicator values (2,3,4) and three scenario codes (7,16,17). I'm looking to group the rows by scenario_code and have each corresponding indicator value as it's own field. The result, three rows, should look like this. </p> <blockquote> <p>{[Scernario_code], [Period 2], [Value 2], [Period 3], [Value 3], [Period 4], [Value 4], [Area Code], [Release code]}</p> <p>7, '2000-06-13', 1000, '2000-01-12', 1300, '2000-06-12', 600, 'OP014', 17</p> <p>16, '2000-09-12', 1100, '2000-06-17', 500, '2000-12-12', 650, 'OP014', 17</p> <p>17, '2002-06-22', 1200, '2008-05-04', 550, '2013-06-12', 150, 'OP014', 17</p> </blockquote> <p>The period and value columns have been spread across, based on their three indicator values(2,3,4) which are bound to one [scenario_code]. I've suffixed the columns with the indicator value it was pivoted on. Ideally I will alias them as something else. </p> <h2>Thoughts</h2> <p>This is obviously screaming pivot or unpivot (Or even both) but my text books don't have something where I need to consider two columns for the spreading element [period] & [Value]. I need data to be rotated by indicator value so they are columns, but grouped on the same line as it's scenario code. Maybe a concatenation would help...?</p> <p>I've seen CROSS APPLY with a Pivot which looks promising but I haven't been able to get it to work as I don't fully understand how this is utilised. I've recently started using SQL Server 2012. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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