CODEWITHSUNDEEP
sqlsql-server
GreatKing
GreatKing

Reputation: 63

Aggregates on the right side of an APPLY cannot reference columns from the left side

I am trying to make some sense of a xBase type database with some 2000 tables. Rather than importing them all into a SQL Server database, I wanted to import the tables one-by-one using a 'SELECT INTO tmpDBF' statement, then extract what I want to know like table structure and value ranges for each of the columns. Then, when I import the next table I want to be able to run the same query against a differently structured tmpDBF table.

I was hoping to do this using a cross apply, but I come up against the above error message.

select cols.column_name 'Name', cols.data_type 'Type', mv.minV 'Minimum'
  from information_schema.columns cols
  cross apply (select MIN(cols.column_name) minV FROM tmpDBF ) mv
  where cols.table_name = 'tmpDBF'

Is there way to restructure the query or did I turn into a dead-end street?

Added on October 6:

Given tmpDBF

 Who     | Zip
 --------|------
 Charlie | 97689
 Foxtrot | 92143
 Delta   | 12011

I would like to see the following result

 Name | Type    | Minimum | Maximum
 -----|---------|---------|--------
 who  | varchar | Charlie | Foxtrot
 Zip  | int     | 12011   | 96789

I realise that the Minimum and Maximum columns need to be cast as varchars.

Upvotes: 2

Views: 6173

Answers (2)

Sebastian Meine
Sebastian Meine

Reputation: 11823

This is not possible for two reasons.

  1. you cannot dynamically change a column name in a query
  2. you cannot mix multiple datatypes in a single column.

But to get you something similar to what you are looking for you can flip the problem around like this:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.a(c1 INT, c2 INT, c3 DATE);

INSERT INTO dbo.a VALUES(1,2,'2013-04-05'),(4,5,'2010-11-10'),(7,8,'2012-07-09');

Query 1:

SELECT 
   MIN(c1) c1_min,MAX(c1) c1_max,
   MIN(c2) c2_min,MAX(c2) c2_max,
   MIN(c3) c3_min,MAX(c3) c3_max
  FROM dbo.a;

Results:

| C1_MIN | C1_MAX | C2_MIN | C2_MAX |     C3_MIN |     C3_MAX |
|--------|--------|--------|--------|------------|------------|
|      1 |      7 |      2 |      8 | 2010-11-10 | 2013-04-05 |

That gives you all the column minima and maxima in a single row. (It's not dynamic yet. Stay with me...)

To make it a little more readable you can use a sort of UNPIVOT like this:

Query 2:

SELECT 
   CASE X.FN WHEN 1 THEN 'MIN' ELSE 'MAX' END AS FN,
   CASE X.FN WHEN 1 THEN c1_min ELSE c1_max END AS c1,
   CASE X.FN WHEN 1 THEN c2_min ELSE c2_max END AS c2,
   CASE X.FN WHEN 1 THEN c3_min ELSE c3_max END AS c3
  FROM(
  SELECT 
     MIN(c1) c1_min,MAX(c1) c1_max,
     MIN(c2) c2_min,MAX(c2) c2_max,
     MIN(c3) c3_min,MAX(c3) c3_max
    FROM dbo.a)AGG
 CROSS JOIN (VALUES(1),(2))X(FN)
 ORDER BY X.FN;

Results:

|  FN | C1 | C2 |         C3 |
|-----|----|----|------------|
| MIN |  1 |  2 | 2010-11-10 |
| MAX |  7 |  8 | 2013-04-05 |

Now to make it dynamic we have to build that query on the fly, like this:

Query 3:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 
 'SELECT CASE X.FN WHEN 1 THEN ''MIN'' ELSE ''MAX'' END AS FN'+
 (SELECT ',CASE X.FN WHEN 1 THEN '+name+'_min ELSE '+name+'_max END AS '+name
    FROM sys.columns WHERE object_id = OBJECT_ID('dbo.a')
     FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')+
 ' FROM(SELECT '+
 STUFF((SELECT ',MIN('+name+') '+name+'_min,MAX('+name+') '+name+'_max'
    FROM sys.columns WHERE object_id = OBJECT_ID('dbo.a')
     FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')+
 '   FROM dbo.a)AGG CROSS JOIN (VALUES(1),(2))X(FN) ORDER BY X.FN;';

EXEC(@cmd);

Results:

|  FN | C1 | C2 |         C3 |
|-----|----|----|------------|
| MIN |  1 |  2 | 2010-11-10 |
| MAX |  7 |  8 | 2013-04-05 |

This query takes the columns of the table at runtime, builds the appropriate query dynamically and executes it. It contains the table name ('dbo.a') in three places. If you want it to work with different tables you need to replace all three.

Upvotes: 2

M.Ali
M.Ali

Reputation: 69594

Try something like 

select cols.column_name 'Name', cols.data_type 'Type', mv.minV 'Minimum'
      from information_schema.columns cols
      cross apply (select MIN(cols.column_name) minV FROM tmpDBF 
                    WHERE tmpDBF.CommonCol = cols.CommonCol) mv
      where cols.table_name = 'tmpDBF'

Upvotes: 0

Related Questions