Sql-server – Crosstab Pivot or not

pivotsql serversql-server-2008-r2t-sql

I am have difficulty wrapping my head around the following issue. I currently have a set of data in one table as shown below:

enter image description here

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:

enter image description here

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

Best Answer

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
;
Related Question