How to create a table representing the minimum and maximum values from all columns given a table?

Question

I was wondering whether it is possible to create a summary table as follows. Given a table, for example

Id | Age | Banksaldo | Number of children | Car owner
1  | 27  | 2000      | 5                  | No
2  | 30  | 3000      | 2                  | Yes

should turn into:

Column name | Minimum | Minimum Id | Maximum | Maximum Id
Age         | 27      | 1          | 30      | 2
Banksaldo   | 2000    | 1          | 3000    | 2
Number of...| 2       | 2          | 5       | 1

Specifically I have problems with calling an SQL statement and then inserting it into a new table. In addition, I have also problems with creating it in the specified format above.

Could someone please give me some hints in how I should approach this problem?

Thanks for your efforts.

PS: I am by the way using Microsoft SQL Server 2014.

Answer 1

As a first step, you can use UNPIVOT:

 SELECT Id, [Column Name], Columns
   FROM 
     (SELECT Id, Age, Banksaldo, [Number of children]
      FROM mytable) p
   UNPIVOT
      (Columns FOR [Column Name] IN (Age, Banksaldo, [Number of children])
   )AS unpvt 

to get the following result set:

Id  Column Name         Columns
--------------------------------
1   Age                 27
1   Banksaldo           2000
1   Number of children  5
2   Age                 30
2   Banksaldo           3000
2   Number of children  2

The use ROW_NUMBER on the above derived table in order to locate min/max records:

SELECT Id, [Column Name], Columns,
       ROW_NUMBER() OVER (PARTITION BY [Column Name] 
                          ORDER BY Columns) AS minRn,
       ROW_NUMBER() OVER (PARTITION BY [Column Name] 
                          ORDER BY Columns DESC) AS maxRn
FROM (
  ... unpivot query here ...
) t 

Output from above:

 Id Column Name         Columns minRn   maxRn
-----------------------------------------------
2   Age                 30      2       1
1   Age                 27      1       2
2   Banksaldo           3000    2       1
1   Banksaldo           2000    1       2
1   Number of children  5       2       1
2   Number of children  2       1       2

As a final step, you can use conditional aggregation on minRn, maxRn to obtain the required result set:

SELECT [Column Name], 
       MAX(CASE WHEN minRn = 1 THEN Id END) AS [Minimum Id],
       MAX(CASE WHEN minRn = 1 THEN Columns END) AS [Minimum],
       MAX(CASE WHEN maxRn = 1 THEN Id END) AS [Maximum Id],
       MAX(CASE WHEN maxRn = 1 THEN Columns END) AS [Maximum]
FROM ( 
  SELECT Id, [Column Name], Columns, minRn, maxRn
  FROM (
  SELECT Id, [Column Name], Columns,
         ROW_NUMBER() OVER (PARTITION BY [Column Name] 
                            ORDER BY Columns) AS minRn,
         ROW_NUMBER() OVER (PARTITION BY [Column Name] 
                            ORDER BY Columns DESC) AS maxRn
  FROM (
     SELECT Id, [Column Name], Columns
     FROM 
       (SELECT Id, Age, Banksaldo, [Number of children]
        FROM mytable) p
     UNPIVOT
        (Columns FOR [Column Name] IN (Age, Banksaldo, [Number of children])
     )AS unpvt ) t 
) s
WHERE s.minRn = 1 OR s.maxRn = 1 ) u
GROUP BY [Column Name]

SQL Fiddle Demo