I am have difficulty wrapping my head around the following issue. I currently have a set of data in one table as shown below:
As you can see, there is an invoice ref number along with a date, and two value fields. Each row has either a Y or N for the desired group the record belongs to.
Unfortunately I don't have access to re-format the data (coming out of an ERP system), and ideally I want to pivot the date, Value1, Value2 fields into columns per group. At the moment I have a rather long winded approach of creating 6 small temp table vars with the InvoiceRef and Date/Value1/Value2 for each of the 3 groups.
I then read the main table, joining (left join) on InvoiceRef to each of the InvoiceRef fields within each of the temp table vars to get the following output:
So as you can see, I get the Date/Value1/Value2 fields for each InvoiceRef in the desired group across the columns..
My question is I feel there is a more efficient way to do this? although just trying to grapple with pivot/crosstab is proving a challenge - I feel I need some pointers or even confirmation the temp table solution is generally an acceptable solution in this case?
Many thanks in advance.
P.S: For reference I am using MS-SQL 2008 R2