1

I have data like this:

ID   | Ref | Value1 | Value2
-----+-----+--------+--------
1    | R1  | 1R11   | 1R12
1    | R2  | 1R21   | 1R22
2    | R1  | 2R11   | 2R12
2    | R2  | 2R21   | 2R22

I want to transform it into this:

ID   | R1-Value1 | R1-Value2 | R2-Value1 | R2-Value2
-----+-----------+-----------+-----------+-----------
1    | 1R11      | 1R12      | 1R21      | 1R22
2    | 2R11      | 2R12      | 2R21      | 2R22

This seems at least similar to a pivot to me, but from reading around it doesn't seem like it's something PIVOT supports. Is that correct, or have I missed something?

Additional: The two main things I'm having trouble with are

  1. Generating two columns from one row i.e. "R1-Value1" and "R1-Value2" both come from a single row
  2. I would like the Ref values I query on to be easily changeable. e.g. sometime I want results for R1 and R2, sometimes for R3 and R4 and R5, for a large number of possible combinations.
Dan
  • 7,446
  • 6
  • 32
  • 46
  • Yes, you could. `PIVOT`ing or conditional aggregation is exactly what you need (I recommend the latter). There are plenty of examples of doing this; what about those existing questions didn't you understand or what was your attempt and why didn't it work? – Thom A Aug 25 '22 at 11:39
  • Does this answer your question? [In SQL Server how to Pivot for multiple columns](https://stackoverflow.com/questions/38067490/in-sql-server-how-to-pivot-for-multiple-columns) – Ankit Bajpai Aug 25 '22 at 11:42
  • The only thing I've found so far is to combine the Value1 and Value2 columns with something like CONCAT(Value1, 'safe_separator', Value2), pivot on that combined column and then split that column into two. But that seems hacky, inefficient, and difficult to scale to lots of ref values. – Dan Aug 25 '22 at 13:19

1 Answers1

1

This is classic conditional aggregation, aka PIVOT. But it's often easier to do the aggregation using MIN(CASE WHEN rather than the official PIVOT operator, as it's far more flexible that way, for example to pivot on two columns at once.

In this case, you want to be able to specify the two Ref values dynamically

DECLARE @FirstRef varchar(30) = 'R1';
DECLARE @SecondRef varchar(30) = 'R2';

SELECT
  t.ID,
  FirstRefValue1  = MIN(CASE WHEN t.Ref = @FirstRef  THEN t.Value1 END),
  FirstRefValue2  = MIN(CASE WHEN t.Ref = @FirstRef  THEN t.Value2 END),
  SecondRefValue1 = MIN(CASE WHEN t.Ref = @SecondRef THEN t.Value1 END),
  SecondRefValue2 = MIN(CASE WHEN t.Ref = @SecondRef THEN t.Value2 END)
FROM YourTable t
GROUP BY
  t.ID;

db<>fiddle

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • Thanks, this is similar to what I've found looking around and I was hoping that there was something better/easier. With this method I'll have to generate the query dynamically depending on how many refs I want and what their values are. It is useful to know that this is the way though. – Dan Aug 26 '22 at 08:26
  • What you could do is have a fixed query with say 10 columns, and some of them will just be `NULL`. Using dynamic SQL is fraught with difficulties and security issues, avoid if you can. Certainly using dynamic SQL is going to be *more* complicated than this – Charlieface Aug 26 '22 at 10:32
  • Yes, I meant using dynamic SQL to basically generate this SQL but with the relavant references. Fortunatley for me I've found a way to avoid the whole pivot/dynamic thing in the code I'm working on. – Dan Aug 27 '22 at 11:43
  • @Dan - It would be nice if you explained that. – Jeff Moden Aug 28 '22 at 03:07
  • Unfortunately it’s very specific to what I’m working on so not much use to anyone else. There are still pivots, but outside the bit I’m making reusable. – Dan Aug 29 '22 at 06:02