3

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

3

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
;
  • Crikey! talk about "can't see the wood for the trees'... Many thanks for clarifying that! perfect and a lot cleaner of course than temp table vars!!! – Dav.id Dec 3 '15 at 9:58
  • Actually, although the solution was very clear and did the main job of slotting the correct set of values into the required "buckets", it didn't group/keep together the sets of data i.e. if there were two sets of data for say group1 throughout the recordset, they would appear in the correct bucket, however of course not grouped. Obviously I hadn't specified this properly, so definitely not complaining here! so grouping gave me the totals which is what, ultimately, I was looking for. Either way, a simple clean solution! – Dav.id Dec 16 '15 at 9:45
  • 1
    Yes, if I'd known that aggregation of the results was needed, I would've certainly suggested grouping. Glad you've managed to figure it out yourself! – Andriy M Dec 16 '15 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.