pape pape - 4 months ago 10
SQL Question

SQL - transform the column values

When I execute the query:

select Number,
Name,
[Date and Time],
Value
from Source;


I get the following result:

enter image description here

But I want this result:

enter image description here

Is this possible in SQL?

Answer

Using COALESCE(MAX(CASE you can get your expected result.

Sample execution with the given sample data:

DECLARE @Source TABLE (Number INT, Name VARCHAR (100), DateAndTime VARCHAR (20), Value INT);

INSERT INTO @Source(Number, Name, DateAndTime, Value) VALUES
(1, 'Tom', '1.5.2016 11:29', 1),
(1, 'Tom', '2.5.2016 10:45', 2),
(2, 'Angelina', '7.5.2016 11:36', 1),
(3, 'Fibi', '1.5.2016 11:34', 2),
(3, 'Fibi', '1.5.2016 11:56', 3);

SELECT Number, Name,
       COALESCE(MAX(CASE WHEN Value = 1 THEN DateAndTime END), '') AS [1],
       COALESCE(MAX(CASE WHEN Value = 2 THEN DateAndTime END), '') AS [2],
       COALESCE(MAX(CASE WHEN Value = 3 THEN DateAndTime END), '') AS [3]
FROM @Source
GROUP BY Number, Name
ORDER BY Number

Result:

Number  Name        1               2               3
------------------------------------------------------------------
1       Tom         1.5.2016 11:29  2.5.2016 10:45  
2       Angelina    7.5.2016 11:36      
3       Fibi                        1.5.2016 11:34  1.5.2016 11:56