I manage an application that has multiple users entering data through a web front-end into an MSSQL database. Each individual 'record' can have around 100 database columns across multiple tables (sometime multiple rows in the same table). It is relatively trivial to write SQL queries that will output 1 row per 'record' with all the columns we require for reporting purposes, for example:  Assessor  Date    Length  Colour  Weight Steve     2/4/17  23.4    NULL    45 John      4/4/17  NULL    Blue    NULL Brenda    4/4/17  NULL    NULL    NULL   I want to produce a simple output that lists everything where data was not recorded i.e fields that remain NULL. For example:  Assessor  Date    Field Steve     2/4/17  Colour John      4/4/17  Length John      4/4/17  Weight Brenda    4/4/17  Length Brenda    4/4/17  Length Brenda    4/4/17  Colour Brenda    4/4/17  Weight   Currently I've tried something along these lines:   select    assessor   ,date   ,Field    =  'Length'  from  dbo.table1  where [Length] is NULL UNION ALL  select    assessor   ,date   ,Field    =  'Colour'  from  dbo.table1  where [Colour] is NULL UNION ALL ...   Is there any way to produce my required output without building such a long list of unions? I've looked into UNPIVOT however this appears to exclude NULLs.
Consider the following table:  id  title   genre1  genre2  genre3  genre4  genre5 1   Movie1  Thriller    Crime   NULL    NULL    NULL 2   Movie2  Fantasy NULL    NULL    NULL    NULL 3   Movie3  Political   Philosophical   Historical  NULL    NULL 4   Movie4  Science Fiction NULL    NULL    NULL    NULL   When I unpivot genre1, genre2, ... the NULL values are removed, so that, for example, the rows for Movie1 would be:  id title  genre     original_column 1  Movie1 Thriller  genre1 1  Movie1 Crime     genre2   Is there a way to include NULL values, for example:  id title  genre     original_column 1  Movie1 Thriller  genre1 1  Movie1 Crime     genre2 1  Movie1 NULL      genre3 1  Movie1 NULL      genre4 1  Movie1 NULL      genre5   I can SELECT ISNULL(genre1, '*'), ISNULL(genre2, '*') ..., but suppose I want the values to appear as NULL? Is there a better way than:  WITH  -- SELECT that unpivots table AS unpivoted SELECT unpivoted.id,    unpivoted.title,    ,CASE unpivoted.genre WHEN '*' THEN NULL ELSE unpivoted.genre END    ,unpivoted.original_transaction FROM unpivoted   Also, I notice that the query that returns the source data to be unpivoted MUST have an alias. For example:  SELECT ... FROM   -- *must* give it an alias or it's a syntax error   (SELECT ... FROM) AS source    UNPIVOT(genre for original_column IN (genre1,genre2, genre3,genre4,genre5))   Does anyone know why? I have not found a way to refer to the alias anywhere in the query.
Which one of this two sentences is correct?   I need you to help me do this job I need you to help me to do this job   Can you also provide me some explanation? I want to show that if a real orthogonal matrix $A$ has determinant $-1$ then $\lambda=-1$ must be an eigenvalue of $A$.  I have proven this in a long-winded way and I was wondering if these is a quick way of seeing it. Suppose that $V, W$ are closed in $X$ and that $V\cap W, V\cup W$ are connected. Show that $V$ and $W$ are connected.  My attempt: Suppose that $V=U_1\cup U_2$ where $U_i$ are open in $V$, not empty and $U_1\cap U_2=\emptyset$. This induces a separation for $V\cap W$: $$ V\cap W = (U_1\cap W)\cup(U_2\cap W).$$  $U_i\cap W$ is open in $V\cap W$  $(U_1\cap W)\cup (U_2\cap W)=\emptyset$ as otherwise $U_1\cap U_2 \ne \emptyset$.  Now, I need to show that both sets are not empty. We know that $V\cup W$ is connected and closed in $X$. So, for all $x\in V\cup W=U_1\cup U_2\cup W$ and all neighborhoods $Z$ (in $X$!) of $x$ we have that $Z\cap (V\cup W) \ne \emptyset$. We can choose $x\in U_1 \subseteq V\cup W$. But then I would have to find a neighborhood of $x$ in $X$. I can't find one... How can I complete this bullet point?   Thanks. Can't start ubuntu-desktop after upgrade to 18.04 After the restart step of upgrading Ubuntu to 18.04, tty is opening instead ubuntu-desktop. And when I enter my login name and password, I am still in the tty. This is happening again in every restart. How can I start the graphical desktop as normal? Does $\int_a^bf(x) = 0$ imply $f(x) = 0$ for a Riemann Integral function? Let $a &lt; b$ and $f:[a, b] \rightarrow \mathbb R$ be a Riemann integral function. Suppose that $f(x) \geq 0$ for all $x \in [a, b]$. I want to show whether $\int_a^bf(x) = 0$ implies $f(x) = 0$ or not, but I'm not sure where to start. I don't know much about Riemann integral functions in general. I only know that $$\underline \int_a^b f= \overline \int_a^b f.$$ Also, I know the fact that if $f$ is continuous, then $f(x) = 0$ for all $x\in [a, b]$. Can anyone provide any hints for me, because I'm really confused about this whole thing. Resetting Ubuntu to factory settings After hitting 80% LTV, my mortgage lender refuses to remove PMI. Any recourse? I recently refinanced my home. At the time based on the appraisal vs. amortization I found I would be stuck with PMI (private mortgage insurance) for about 9 months.  I've paid extra on the mortgage each month and after 7 months I got my LTV (loan-to-value ratio) to be 79%. Yay! So I called Pacific Union and asked for PMI to be removed.  The woman said I'd need to fax in a written request. Then she added that my written request would likely be denied because my account had not been in good standing the last 12 months. I pointed out that I'd only had the loan for 7 months and had never been late, but that didn't seem to matter to her.  So I faxed in my written request. Then I read through all my documentation on my loan. Nowhere did it say that I needed to have the loan for 12 months.  After a week I called them back and they said it would be another week for a decision.  So, I waited another week and called. They now just had a recording saying they were too busy to take a call and I could leave a callback number. I did this and never heard back. I've now tried twice more to call them. Each time I leave a number and each time I don't hear back. So I've fired off a written request again.  At this point we've gone around the horn to the next month and in two short days I'll be paying PMI on March's payment.  Do they have any grounds to not remove PMI? Isn't it federally mandated that PMI must be removed at 80%? Any recourse anyone can think of?  [EDIT] - I heard back from Pacific Union. They state that I did not sign anything agreeing to these terms which is what I was certain of. They state these terms are simply automatic with Fannie Mai backed loans and it doesn't matter if I was informed or not about these terms. They also chose not to hide behind the "no late payments" clause and instead said they were requiring an appraisal to remove PMI. Of course an appraisal is $400 and I'll hit 78% LTV before I would pay that much extra in PMI. I have now contacted a lawyer. I'll update again with whatever opinion the lawyer has and whether I proceed or not.  [EDIT] - I filed a BBB complaint. They waited 30+ days to respond. In their response they indicate that the first woman (and some SE users below) are wrong. The loan does NOT have to be in good standing for 12 months. The loan simply needs no late payments within 12 months. They then hide behind the request for another appraisal as the sole reason to not remove it. I checked with Fannie Mae who backs the loan and was told this is optional per the lender, however the lender can force it and I have no recourse. Additionally, they could have asked for a Broker's Price Option (BPO) or a certificate of value which would cost me hundreds less.   At this point I cannot continue to fight them because the amount of PMI I'll pay until 78% LTV is equal to or less than the appraisal cost of $400.  [EDIT] As of August 2015 I've hit 78% LTV. I called Pacific Union to ensure that the automatic cancellation of PMI had happened. They said no, I'd need to request that in writing. This is a violation of federal law as it's supposed to be automatic. They also said they would still deny it because now they want 24 months of good history. I've sent a threatening letter off and am waiting for a reply. If any lawyers come along and read this I'd sure like to hire you.  [EDIT] After my letter they said they would remove PMI. They did not and I was charged again in September. I called them yet again and complained. They said PMI was going to be removed. They then charged my again in October! I called again. I called again and went berserk on the phone. PMI was finally removed for November 2015. First month without it. How can I test whether my camera is working after it was dropped? Issue : How can I evaluate my cameras performance synthetically?  Scenario : Bought a Nikon D3100 an year back. Photos looked good enough. The camera fell, zoom lens effected.  But works. Cleaned lens using soap, water etc.  Carefully. Now photos look much worse.  How do I know if Photos are really bad?  Is there a tool?    Sample Photo  Now (Bad)   Original (Good)   Please help me realize whether my camera has gone bad.  Can you tell your personal opinion also? Definition of electric charge and proper explanation How to determine if $n (n \geq 2)$ given numbers can be summed up to a certain number? I'm interested in knowing if there's a theorem for checking if combinations of $n$ given positive numbers can be summed up to a certain number.   For example, if I was given $n = 2$ numbers, which are $k_1$ and $k_2$, and I was asked if I could make a combination like $n_1 * k_1 + n_2 *k_2 = C$, all $\in \mathbb{N}^+$, I could check remainders to see if it is possible.   However, if $n = 3$ or higher, how could I check if combinations of $k_1, k_2, ..., k_n$ can be summed up to a $C$? Thank you for any inputs! I'm trying to do a volumetric light on Cycles, and I've followed tutorials, but nothing has worked yet. How can I make a proper volumetric light in Blender, like the following one?    But to leave the light in the middle, like, almost a star, is this possible? I'm at the point to use an image plane besides the light which would be better... I need to use it moving the camera.  I'm actually trying to do this because I want to simulate the brightness of the Sun.  I tried the effect, but it still gave me an error: Primes congruent to 1 mod 6 I came across a claim that I found interesting, but can't seem to prove for some reason. I have the feeling it should be easy  a prime $p$ can be written in the form $p = a^2 -ab +b^2$ for some $a,b\in\mathbb{Z}$ if and only if $p\equiv 1\bmod{6}$ How do you add CSS rules (eg strong { color: red }) by use of Javascript? Consider following structure:  . |_s |_s  |_ss  |_ss+   |_sss  |_ss |_s |_s   How can I demote a subsection to subsubsection without changing markups ?  . |_s |_s  |_ss   |_sss+    |_p  |_ss |_s |_s   How can I promote a subsection to section without changing markups ?  . |_s |_s  |_ss |_s+  |_ss  |_ss |_s |_s   A wrapper command would be excellent  \begin[+1]{level} \section{} \end{level} why don't we define vector multiplication component-wise? I was just wondering why we don't ever define multiplication of vectors as individual component multiplication.  That is, why doesn't anybody ever define $\langle a_1,b_1 \rangle \cdot \langle a_2,b_2 \rangle$ to be $\langle a_1a_2, b_1b_2 \rangle$?  Is the resulting vector just not geometrically interesting? Simple examples of uncorrelated but not independent $X$ and $Y$ Any hard-working student is a counterexample to "all students are lazy".  What are some simple counterexamples to "if random variables $X$ and $Y$ are uncorrelated then they are independent"? Should I use center or centering for figures and tables? What is the correct way to center figures and tables (figure, table)?  \begin{center} ... \end{center}   or   \begin{centering} ... \end{centering} python get only class attribute no superclasses What does it mean by 'bringing him gasping to all fours' in Harry potter? Immediately, his insides started writhing as though he'd just swallowed live snakes -- doubled up, he wondered whether he was going to be sick -- then a burning sensation spread rapidly form his stomach to the very ends of his fingers and toes -- next, bringing him gasping to all fours, came to a horrible melting feeling, as the skin all over his body bubbled like hot wax -- and before their eyes, his hands began to grow, the fingers thickened, the knuckles were bulging like bolts -- his shoulders stretched painfully and a prickling on his forehead told him that his hair was creeping down toward his eyebrows -- his robes ripped as his chest expanded like a barrel bursting its hoops -- his feet were agony in shoes four sizes too small      Harry potter and the chamber of secrets   It is the description of the feeling of drinking Polyjuice Potion, which makes someone into a different person, from the point of view of Harry Potter turning into Gregory Goyle.  And I don't understand what 'bring him gasping to all fours' means, especially 'four'. Does four here mean number 4? .NET Attributes: Why does GetCustomAttributes() make a new attribute instance every time? So I was playing around a little more with attributes in .NET, and realized that every call to Type.GetCustomAttributes() creates a new instance of my attribute. Why is that? I would think that the attribute instance would basically be a singleton-per-MemberInfo, with 1 instance bound to the Type, PropertyInfo, etc...  Here is my test code:  using System;  namespace AttribTest { [AttributeUsage(AttributeTargets.Class)] class MyAttribAttribute : Attribute {     public string Value { get; set; }      public MyAttribAttribute()         : base()     {         Console.WriteLine("Created MyAttrib instance");     } }  [MyAttrib(Value = "SetOnClass")] class MyClass { }  class Program {     static void Main(string[] args)     {         Console.WriteLine("Getting attributes for MyClass.");         object[] a = typeof(MyClass).GetCustomAttributes(false);         ((MyAttribAttribute)a[0]).Value = "a1";          Console.WriteLine("Getting attributes for MyClass.");         a = typeof(MyClass).GetCustomAttributes(false);         Console.WriteLine(((MyAttribAttribute)a[0]).Value);          Console.ReadKey();     } } }   Now if I were to implement attributes, I would expect the output to be:  Created MyAttrib instance Getting attributes for MyClass. Getting attributes for MyClass. a1   Where the "class loader" (sorry, I have more of a Java background, not 100% sure how .net loads its types) would compile MyClass, and create an instance of MyAttribAttribute, and store them together somewhere. (probably the Perm Gen in the heap if this were Java) The 2 calls to GetCustomAttributes() would then just return the same earlier created instance.  But the actual output is:  Getting attributes for MyClass. Created MyAttrib instance Getting attributes for MyClass. Created MyAttrib instance SetOnClass   So... why? It seems like creating a new instance of all these objects for every call is a bit excessive, and not good for performance/memory management.  Is there any way to always get the same instance over and over?  Anyone have any ideas why it was designed this way?  The reason I care at all is because I made a custom attribute that internally holds some validation information, so in the Attribute I basically have a "private bool Validated" that I set to true. The validation stuff takes a while, so I don't want to run it every time. Now the problem is that since it creates a new instance of the attribute each time I get the attributes, Validated is always "false".