Showing posts from 2017

How do I compare all the column values based on the Partition by with ID

I have a case where there are many rows for the same ID, I just wanted to know if there is any way to find out whether the given ID includes some specific value or not.
Table 1ID Dept Salary Flag Date 1 IT 5000 N 2017-01-011 IT 5000 N 2017-01-021 IT 5000 N 2017-01-031 IT 5000 N 2017-01-041 IT 5000 N 2017-01-052 HR 4000 N 2017-01-012 HR 4000 N 2017-01-022 HR 4000 Y 2017-01-032 HR 4000 N 2017-01-042 HR 4000 N 2017-01-053 Fin 4500 N 2017-01-083 Fin 4500 N 2017-01-093 Fin 4500 N 2017-01-103 Fin 4500 N 2017-01-113 Fin 4500 N 2017-01-124 Edu 4800 N�����…

SQL Server 2008 R2: Get first not null values from column

In sqlserver, when you have multiple data with some column have null value and you need the row which not null.

So this example will help you.

CREATETABLE Family ( ID int, Name varchar(20), Gender char(1));INSERTINTO Family VALUES(1,'Ram','M'),(2,'Suraj','M'),(3,'Sunitha','F'),(4,'Deepika','F'),(5,'Minakshi','F'),(6,'Somu','M');CREATETABLE Child_parent ( Child_ID int, Parent_ID int );INSERTINTO Child_parent VALUES(1,2),(1,3),(4,5),(4,6);Expected Result:
Child_ID ChildName FatherName MotherName -----------------------------------------------
1        Ram       Suraj      Sunitha 
4        Deepika   Somu       Minakshi

Query to get Desire result with self join as below.

SELECT cp.Child_ID, f1.Name as ChildName, max(CASEWHEN f2.Gender ='M'THEN f2.Name END) FatherName, max(CASEWHEN f2.Gender ='F'THE…

❣DIY Fairy House Lamp Using Coke Plastic Bottles❣