Crosstab Pivot or not? 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
This has nothing to do with pivoting, you simply want to replicate your three columns conditionally three times, according to the number of groups. A series of CASE expressions would do the job perfectly:

SELECT
  InvoiceRef,
  Group1Date   = CASE Group1 WHEN 'Y' THEN Date   END,
  Group1Value1 = CASE Group1 WHEN 'Y' THEN Value1 END,
  Group1Value2 = CASE Group1 WHEN 'Y' THEN Value2 END,
  Group2Date   = CASE Group2 WHEN 'Y' THEN Date   END,
  Group2Value1 = CASE Group2 WHEN 'Y' THEN Value1 END,
  Group2Value2 = CASE Group2 WHEN 'Y' THEN Value2 END,
  Group3Date   = CASE Group3 WHEN 'Y' THEN Date   END,
  Group3Value1 = CASE Group3 WHEN 'Y' THEN Value1 END,
  Group3Value2 = CASE Group3 WHEN 'Y' THEN Value2 END
FROM
  dbo.atable
;