robin.pryor (5/2/2014)

---

Sean Lange (4/28/2014)

---

robin.pryor (4/28/2014)

---

Can someone please give me advise on a better way to write the following?

SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4

FROM boxes a

LEFT OUTER JOIN widgets b ON a.boxid = b.boxid and b.parameter = 1

LEFT OUTER JOIN widgets c ON a.boxid = c.boxid and b.parameter = 2

LEFT OUTER JOIN widgets d ON a.boxid = d.boxid and b.parameter = 3

LEFT OUTER JOIN widgets e ON a.boxid = b.boxid and b.parameter = 4

I'm likely giving a horrible example, but trust me, the real thing is way way way uglier.

Due to an interface that already exists, the big requirement is that I HAVE to end up with those 4 columns.

Look at what you posted and ask yourself if you honestly think anybody can offer any real advice here. We have nothing but a vague query that sort of represents your actual query. We have no basis for what your are trying to do at all.

Maybe a cross tab would work here? Take a look at the links in my signature. Hard to say unless you give us some details.

Sorry about the vagueness. I did the best I could. I work for a govt agency and the particulars of this specific thing involve sensitive data and field names that completely give away where I work. Call it paranoia, but my "company" would crap if I put the real structure out there. Do the real field and table names really matter? What I used, while meaningless, could really exist as a table.