StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are several ways that you can get the result, including using the PIVOT function. </p> <p>You can use an aggregate function with a CASE expression:</p> <pre><code>select t1.aid, t1.status, t1.[group], max(case when t2.traitname = 'trait1' then t3.trait end) trait1, max(case when t2.traitname = 'trait2' then t3.trait end) trait2, max(case when t2.traitname = 'trait3' then t3.trait end) trait3 from table1 t1 inner join table3 t3 on t1.aid = t3.aid inner join table2 t2 on t3.atid = t2.atid group by t1.aid, t1.status, t1.[group]; </code></pre> <p>See <a href="http://sqlfiddle.com/#!3/f55e4/6" rel="nofollow">SQL Fiddle with Demo</a></p> <p>The PIVOT function requires an aggregate function this is why you would need to use either the MIN or MAX function (since you have a string value).</p> <p>If you have a limited number of <code>traitnames</code> then you could hard-code the query:</p> <pre><code>select aid, status, [group], trait1, trait2, trait3 from ( select t1.aid, t1.status, t1.[group], t2.traitname, t3.trait from table1 t1 inner join table3 t3 on t1.aid = t3.aid inner join table2 t2 on t3.atid = t2.atid ) d pivot ( max(trait) for traitname in (trait1, trait2, trait3) ) piv; </code></pre> <p>See <a href="http://sqlfiddle.com/#!3/f55e4/4" rel="nofollow">SQL Fiddle with Demo</a>. </p> <p>If you have an unknown number of values, then you will want to look at using dynamic SQL to get the final result:</p> <pre><code>DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(traitname) from Table2 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT aid, status, [group],' + @cols + ' from ( select t1.aid, t1.status, t1.[group], t2.traitname, t3.trait from table1 t1 inner join table3 t3 on t1.aid = t3.aid inner join table2 t2 on t3.atid = t2.atid ) x pivot ( max(trait) for traitname in (' + @cols + ') ) p ' execute sp_executesql @query; </code></pre> <p>See <a href="http://sqlfiddle.com/#!3/f55e4/5" rel="nofollow">SQL Fiddle with Demo</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