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.