A Different kind of transformation of data in sql

Question

I'm looking for a query were i can derive a new column based on columns values as shown below in example

P      X     Y    Z    A   B     C
1      1.1   2.1  1.3  1   Null  3
2      Null  1.4  3.1  2   4.7   1
3      2.2   Null 4.6  4   3.5   1 
4      Null  1.8  3.4  2   1.7   4

Which i want to show as shown below;

P  Group   X    y   Z   A    B    C
1   Xgrp   1.1  -    -   -    -    - 
1   Ygrp    -  2.1   -   -    -    - 
1   Zgrp             1.3 -    -    -  
1   Agrp                 1   
1   Bgrp                     Null
1   Cgrp                          3

Please help me :)

Regards Andy

Answer 1

What gotqn posted is missing some rows - specifically ones where all columns should be NULL (such as the "Bgrp" record where P=1).

You can get what you're looking for without PIVOT or UNPIVOT, just a simple CROSS JOIN, GROUP BY and CASE statement like so:

-- sample data
DECLARE @DataSource TABLE
(
    [P] TINYINT
   ,[X] DECIMAL(9,1)
   ,[Y] DECIMAL(9,1)
   ,[Z] DECIMAL(9,1)
   ,[A] DECIMAL(9,1)
   ,[B] DECIMAL(9,1)
   ,[C] DECIMAL(9,1)
);

INSERT INTO @DataSource ([P], [X], [Y], [Z], [A], [B], [C])
VALUES (1, 1.1, 2.1, 1.3, 1, NULL, 3)
      ,(2, NULL, 1.4, 3.1, 2, 4.7, 1)
      ,(3, 2.2, NULL, 4.6, 4, 3.5, 1 )
      ,(4, NULL, 1.8, 3.4, 2, 1.7, 4);

-- solution
SELECT 
  p, [group], 
  X = CASE [group] WHEN 'Xgrp' THEN MAX(X) END,
  Y = CASE [group] WHEN 'Ygrp' THEN MAX(Y) END,
  Z = CASE [group] WHEN 'Zgrp' THEN MAX(Z) END,
  A = CASE [group] WHEN 'Agrp' THEN MAX(A) END,
  B = CASE [group] WHEN 'Bgrp' THEN MAX(B) END,
  C = CASE [group] WHEN 'Cgrp' THEN MAX(C) END
FROM (VALUES ('Xgrp'),('Ygrp'),('Zgrp'),('Agrp'),('Bgrp'),('Cgrp')) groups([group])
CROSS JOIN @DataSource d
GROUP BY p, [group]
-- Uncomment this ORDER BY for testing:
--ORDER BY [P]
--        ,CASE [group]
--            WHEN 'Xgrp' THEN 1
--            WHEN 'Ygrp' THEN 2
--            WHEN 'Zgrp' THEN 3
--            WHEN 'Agrp' THEN 4
--            WHEN 'Bgrp' THEN 5
--            WHEN 'Cgrp' THEN 6
--        END;

Results

Answer 2

I am not sure how you are defining these dashes, so they are ignored in the code below. There is some additional logic in the ORDER BY clause in order to show the results like in your example. Basically, we are performing UNPIVOT to define the group values and then performing again PIVOT:

DECLARE @DataSource TABLE
(
    [P] TINYINT
   ,[X] DECIMAL(9,1)
   ,[Y] DECIMAL(9,1)
   ,[Z] DECIMAL(9,1)
   ,[A] DECIMAL(9,1)
   ,[B] DECIMAL(9,1)
   ,[C] DECIMAL(9,1)
);

INSERT INTO @DataSource ([P], [X], [Y], [Z], [A], [B], [C])
VALUES (1, 1.1, 2.1, 1.3, 1, NULL, 3)
      ,(2, NULL, 1.4, 3.1, 2, 4.7, 1)
      ,(3, 2.2, NULL, 4.6, 4, 3.5, 1 )
      ,(4, NULL, 1.8, 3.4, 2, 1.7, 4);

SELECT *
FROM
(
    SELECT [P]
          ,[value]
          ,[column]
          ,[column] + 'grp'
    FROM @DataSource
    UNPIVOT
    (
        [value] FOR [column] IN ([X], [Y], [Z], [A], [B], [C])
    ) UNPVT
) DS ([P], [value], [column], [group])
PIVOT
(
    MAX([value]) FOR [column] IN ([X], [Y], [Z], [A], [B], [C])
) PVT
ORDER BY [P]
        ,CASE [group]
            WHEN 'Xgrp' THEN 1
            WHEN 'Ygrp' THEN 2
            WHEN 'Zgrp' THEN 3
            WHEN 'Agrp' THEN 4
            WHEN 'Bgrp' THEN 5
            WHEN 'Cgrp' THEN 6
        END