Unknown Values

Question

Unknown Values

Martin D. Cymerman

SSCrazy

Points: 2079
More actions
November 29, 2006 at 12:21 pm

#368231

Is it possible to write a query in such a way that it only displays only those columns with values in it? For example a table has 10 columns and one row .Only 3 of those columns has data, and you do not know which ones …how could you query it just to bring back those 3 and not the rest?
Thanks
MC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

That would be very complicated server side... it is much simpler to do that once you get the data... also for only one row, sending 7 nulls won't slow the network .

Why do you need this exactly?

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 2

At some point in time I can envision someone asking me to show in a spreadsheet only those values in a table that have been updated. This table in question has @150 columns and only one column \row in a great while get updated. I’ve tried it with a trigger with no luck

Example:

Column 10, row 1 gets updated from “Red” to” Black”. A cell in a spreadsheet would display in “Black”

Then a week from now Column 25, row 1 gets updated from “blue” to “green” The spreadsheet would now show “Black, Blue”

Thanks

MC

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You could always insert the modifications in a name/value type table. That is usually slower but in your case I doubt it will be a problem.

Also if it is not a requirement yet, then I wouldn't go to extreme lengths to make that possible .

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 4

I would create an audit table and a trigger that writes out the row that's being changed along with the date. That way you can do a quick search on your audit table to look at the rows being changed. Depending on what your you want to store in the table, you can use the deleted or inserted tables. I generally use the deleted table for updates/deletes, and inserted for new rows.

Thanks

Tom

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

>> I generally use the deleted table for updates/deletes, and inserted for new rows.

For updates, deciding which table you should use depends on which set of data you want. The deleted table will have the record(s) with the old values (before the update) and the inserted table will have the record(s) with the new values.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]