Uintentional multiplicity of rows returned

Question

Not being a savvy SQL programmer, I'm in a following pickle. From a table I can select as follows.

select Sign as Siggy, Value as Greeky from Table
where ID = 1

This presents me with following output.

Siggy Greeky
a-ish alpha
b-ish beta
c-ish gamma

The problem is that, for reason not discussed here and not affectable, I need it to be like this.

A-ish B-ish C-ish
alpha beta gamma

Important to note is that I do know for absolutely sure that there's a fix number of elements (possibly null valued). I can safely assume that the number of columns mapped into from the rows is three or less.

I attempted to resolve it by joins like so.

select T1.ID, T1.Value, T2.Value, T3.Value
from Table as T1
join Table as T2 on T2.ID = T1.ID and T2.Siggy = 'b-ish'
join Table as T3 on T3.ID = T1.ID and T2.Siggy = 'c-ish'

This produces a first row that is what I need but it's repeated (and variating somewhat) because, as I suspect, the ON addition to JOIN statements isn't in effect for FROM.

I can get the first row only but it'd be nice to make it more optimal. How?!

Please not that pivotation isn't the way to go here because I've got a fixed limit of rows to be transposed and I need to respect that the output of columns doesn't exceeds that number.

@SelectDistinct Not sure what you mean. I strongly feel that I only need to add some kind of *ON* to the *FROM* as I do to *JOIN*. But how? (Also, mind that I'm not savvy when it comes to SQL). — Konrad Viltersten, Oct 15 '14 at 10:17
Does this solve your issue? [http://stackoverflow.com/questions/15745042/](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — proPhet, Oct 15 '14 at 10:22
@proPhet Not quite. The pivotation is applicable in a more general case, while I have a fix limit on the number of rows to be transposed. I need to ensure that the number of columns doesn't exceed that number, too, because of reasons out of my control. I'm really looking for the equivalent of *ON* but for *FROM* instead of *JOIN*. — Konrad Viltersten, Oct 15 '14 at 10:44

Mureinik · Accepted Answer · 2014-10-15T14:08:11.413

You're missing a condition on the table in the from clause to have Siggy = 'a-ish'. If you convert all terms to subqueries, the query will become clearer:

select a.ID, a.Value, b.Value, c.Value
from   (SELECT ID, Value
        FROM   Table
        WHERE  Sign = 'a-ish') a
join   (SELECT ID, Value
        FROM   Table
        WHERE  Sign = 'b-ish') b ON a.ID = b.ID
join   (SELECT ID, Value
        FROM   Table
        WHERE  Sign = 'c-ish') c ON a.ID = c.ID

Uintentional multiplicity of rows returned

1 Answers1