StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><a href="http://sqlfiddle.com/#!6/93dbc/8" rel="nofollow">SQL Fiddle</a></p> <p><strong>Schema Setup</strong>:</p> <pre><code>create table history (date datetime, name varchar(10), value int); insert history values ('20130101', 'user1', 123), ('20130101', 'user2', 124), ('20130101', 'user3', 125), ('20130102', 'user1', 223), ('20130102', 'user3', 223), ('20130103', 'user2', 323); create table colours (name varchar(10), colour_name varchar(10)); insert colours values ('user1', 'blue'), ('user2', 'green'), ('user3', 'brown'); </code></pre> <p><strong>Query</strong>:</p> <pre><code>DECLARE @scols nvarchar(max), @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); select @cols = STUFF(( SELECT ',' + QUOTENAME(C.Name) from (select distinct name from [History]) C ORDER BY C.Name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''); select @scols = STUFF(( SELECT ',' + QUOTENAME(Name) + ' AS ' + QUOTENAME(colour_Name) from (select distinct c.name, x.colour_name from [History] C JOIN colours x on x.name = c.name) y ORDER BY Name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''); set @query = ' SELECT [Date],' + @scols +' from ( select [Date], Name, Value from [History] ) x pivot ( max(value) for Name in (' + @cols + ') ) p '; -- print @query --<< uncomment this line to see the query that gets generated exec (@query); </code></pre> <p><strong><a href="http://sqlfiddle.com/#!6/93dbc/8/0" rel="nofollow">Results</a></strong>:</p> <pre><code>| DATE | BLUE | GREEN | BROWN | ------------------------------------------------------------- | January, 01 2013 00:00:00+0000 | 123 | 124 | 125 | | January, 02 2013 00:00:00+0000 | 223 | (null) | 223 | | January, 03 2013 00:00:00+0000 | (null) | 323 | (null) | </code></pre>

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