Query to combine multiple rows into separate rows with multiple columns

Question

I do not consider myself a complete newbie in SQL, but I have been looking at this problem for two days, on and off, and I'm starting to think, maybe I am!

Using the following two tables:

ID Category 1 Animal 2 Color 3 Sport Name ID Value Fred 1 Cat Fred 2 Blue Fred 3 Football Sally 1 Dog Sally 3 Jogging James 2 Green Anne 3 Swimming 

I was unable to find any combination of group, sub-query, union, cte or pivot commands that will combine several rows returned from the query '(ID = 1 or ID = 2) into separate rows with several columns.

eg:.

 where (ID=1 or ID=2) Name Animal Color Fred Cat Blue Sally Dog NULL James NULL Green 

Can anyone advise me if there is an effective SQL solution for this or am I wasting my time on what should be processed by the code in the report?

thanks

Answer 1

Here is the PIVOT syntax

 SELECT Name,[1] as Animal, [2] as Color FROM (SELECT Name,Id,Value FROM Table) AS SourceTable PIVOT ( MIN(Value) FOR Id IN ([1], [2]) ) AS PivotTable; 

Answer 2

You can do it with

 Select a.Name as Name, a.Value as Animal, c.Value as Color FROM (SELECT Name, Value FROM table2 INNER JOIN table1 ON table2.ID = table1.ID AND table2.ID =1) as a LEFT JOIN (SELECT Name, Value FROM table2 INNER JOIN table1 ON table2.ID = table1.ID AND table2.ID =2) as c ON a.Name = c.Name UNION Select c.Name as Name, a.Value as Animal, c.Value as Color FROM (SELECT Name, Value FROM table2 INNER JOIN table1 ON table2.ID = table1.ID AND table2.ID =1) as a RIGHT JOIN (SELECT Name, Value FROM table2 INNER JOIN table1 ON table2.ID = table1.ID AND table2.ID =2) as c ON a.Name = c.Name 

Table 2 is strange and not very well designed.

Answer 3

 WITH filtered_table AS ( SELECT Name, ID, Value FROM table2 WHERE ID IN (1, 2) ) SELECT t2.Name, Animal = MAX(CASE t1.ID WHEN 1 THEN ft.Value END), Color = MAX(CASE t1.ID WHEN 2 THEN ft.Value END) FROM (SELECT DISTINCT Name FROM filtered_table) t2 INNER JOIN table1 t1 ON t1.ID IN (1, 2) LEFT JOIN filtered_table ft ON t2.Name = ft.Name AND t1.ID = ft.ID GROUP BY t2.Name