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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/