StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You might do it by ordering rows first by template flag, then by date desc. Template row should always be the last one. Each row is assigned a number in that order. Using max() we are finding fist occupied cell (in descending order of numbers). Then we select columns from rows matching those maximums.</p> <pre><code>; with rows as ( select test.*, -- Template row must be last - how do you decide which one is template row? -- In this case template row is the one with id = 1 row_number() over (order by case when id = 1 then 1 else 0 end, date) rn from test -- Your list of rows to merge goes here -- where id in ( ... ) ), -- Finding first occupied row per column positions as ( select max (case when Name is not null then rn else 0 end) NamePosition, max (case when Address is not null then rn else 0 end) AddressPosition, max (case when City is not null then rn else 0 end) CityPosition, max (case when State is not null then rn else 0 end) StatePosition, max (case when Active is not null then rn else 0 end) ActivePosition, max (case when Email is not null then rn else 0 end) EmailPosition, max (case when Date is not null then rn else 0 end) DatePosition from rows ) -- Finally join this columns in one row select (select Name from rows cross join Positions where rn = NamePosition) name, (select Address from rows cross join Positions where rn = AddressPosition) Address, (select City from rows cross join Positions where rn = CityPosition) City, (select State from rows cross join Positions where rn = StatePosition) State, (select Active from rows cross join Positions where rn = ActivePosition) Active, (select Email from rows cross join Positions where rn = EmailPosition) Email, (select Date from rows cross join Positions where rn = DatePosition) Date from test -- Any id will suffice, or even DISTINCT where id = 1 </code></pre> <p><a href="http://sqlfiddle.com/#!3/3e753/8" rel="nofollow">You might check it at Sql Fiddle</a>.</p> <p>EDIT:</p> <p>Cross joins in last section might actually be inner joins on rows.rn = xxxPosition. It works this way, but change to inner join would be an improvement.</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