StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPivot Rows to Columns
text
Body
copied!<p>I am trying to pivot a table and am having a bit of difficulty. My table schema looks like this:</p> <pre><code>ID W_C W_P A_C A_P 3 257 251342 217 206078 4 443 109023 332 87437 6 17 9985 32 13515 </code></pre> <p>What I am trying to accomplish is something to the effect of:</p> <pre><code> 3 4 6 w_c 257 443 17 w_p 251342 109023 9985 a_c 217 332 87437 a_p 206078 87437 13515 </code></pre> <p>I do not need to display the <code>w_c</code>, <code>w_p</code>, <code>a_c</code>, or <code>a_p</code> I just was using it as a point of reference.</p> <p>I think there may be a way of doing it with pivot/unpivot but I am not very familiar with them, and what I have read isn't helping me.</p> <p>I tried doing something using CTE's but I believe it is overly complicated and is just bad practice:</p> <pre><code>;with [3_Data] as ( Select 1 as [common_key3] ,max(Case when [id] = 3 then [w_c] else 0 end) as [3_Records] From [example] ), [4_data] as ( Select 1 as [common_key4] ,max(Case when [id] = 4 then [w_c] else 0 end) as [4_Records] From [example] ), [6_data] as ( Select 1 as [common_key6] ,max(Case when [id] = 6 then [w_c] else 0 end) as [6_Records] From [example] ) Select [3_Records], [4_Records], [6_Records] From [3_Data] inner join [4_data] on [common_key3] = [common_key4] inner join [6_data] on [common_key3] = [common_key6] </code></pre> <p>Sql fiddle with table already created: <a href="http://sqlfiddle.com/#!3/02ef2/6" rel="nofollow">http://sqlfiddle.com/#!3/02ef2/6</a></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