0

For application sake, the main goal here is that in my front-end I want to be able to pull the field data from a sql server table and store it in my own database. (The application creates data files from database values). But I want the metadata and the

Say I have table FOO defined as below:

create  TABLE foo 
( id      INT NOT NULL
, col2    INT NOT NULL
, col3    VARCHAR(10) NULL
, col4    DATE NULL
, col5    DECIMAL(14,5) NULL
);

With data as defined: 

INSERT INTO foo (id, col2, col3, col4, col5) VALUES  
  (1,1,NULL,NULL,NULL)
,(2,2,'2','2/2/2012',22.22)
,(3,3,'3','3/3/2013',333.333)
,(4,4,NULL,'4/4/2014',4444.4444)
,(5,5,'5',NULL,55555.55555)
,(6,6,'6','6/6/2016',NULL);

Assuming this can be applied to any table where I don't know the columns, I want the following returned:

column_name | value
----------------------------
id          |   1
col2        |   1
col3        |   2
col4        |   2/2/2012
col5        |   22.22

So basically, I want to retrieve the column_name with the first non-null value for that column, but at this point I'll settle for a solution that will just return the first value even if it is null.

Now, I have a query to get all the metadata, but i'm not sure how to add another column to the query to also get the data for that column name. (simplified to just pull column_name as I'm also retrieving other metadata). Again, I have to do it dynamically and can't store it as a procedure. 

SELECT 
    sc.name AS column_name
FROM
    sys.columns sc 
WHERE 
    sc.object_id = OBJECT_ID('FOO') 
ORDER BY 
    sc.column_id

All of the answers I have seen have been non-dynamic with hardcoded column names, but I'm looking for an option where it will work across tables by just plugging in the table name. I'm not sure if it's possible or not. i.e. this solution

Dawson Herron
  • 1
  • 1
  • 1
  • This output is a horrific design. It is even worse than a standard EAV anti-pattern. Define first. A table by definition is an unordered set. So if you don't know the columns how can you order the rows? And to do this for any given table means dynamic sql. And what happens when you find one of those funky columns that is not possible to convert to varchar? Something like varbinary(max) or a few others. What is the point of poisoning your data like this with partial row values? – Sean Lange Jun 05 '18 at 20:31
  • 2
    Why not just `SELECT * FROM FOO`? All the column type and name metadata will be sent to the client app, where you can dynamically process the data. – David Browne - Microsoft Jun 05 '18 at 21:18
  • 1
    Use your last query to return the meta data to your application, then build a new sql statement using that meta data (as a string, in your application), then execute that new sql statement. – MatBailie Jun 05 '18 at 22:47
  • Possible duplicate of [Unpivot ALL Columns in a SQL Table](https://stackoverflow.com/questions/21996988/unpivot-all-columns-in-a-sql-table), getting first not-null will be rather involved (and indeed requires that you specify the ordering) -- perhaps you'll settle for MAX()? – Nickolay Jun 05 '18 at 23:26
  • Please remember rows in sql are part of a set, unordered set, so in your scenario even if you solve the critical piece of the puzzle somehow, you may still get different results every time. I would suggest you to normalize the table, and then write a view on top of it. – Surendra Jun 06 '18 at 00:15
  • I'll agree it's poor design, but I have limited control here. The concept is that we have an application that we can read from multiple databases and build an output file from the data, but for our client app, instead of manually importing each table/view, we can run the query to get all the information. Unfortunately, we also want to store a sample value and that's the part I'm struggling with. As for pulling the metadata from java, I haven't seen a way to get the description. I'll try the unpivot and then just build 2 queries if not. Thanks all for the feedback, i'm rather inexperienced. – Dawson Herron Jun 06 '18 at 03:32

0 Answers0