how to extract or get data from multiple rows in single row by eliminating null values in sql server

-


Let us consider the below table,

Table Data:

In the above table, we have common fields like Work_Order and unit. Here every time a new record is inserted while inserting TotUnits, ETCUnits, RwUnits. So we need to get them in one row by eliminating null values as we have Work_Order and unit same for three of them as shown below,

OUTPUT:


Here we get data in a single row for every distinct Work_Order and unit.

Query as Follows:

declare @units table
(
    unit varchar(1000)
)
declare @result table
(
    workorder varchar(1000),TotalUnits varchar(1000),ETCUnits varchar(1000),RwUnits varchar(1000),UnitID varchar(1000),Total varchar(1000)
)
insert into @units SELECT distinct Unit FROM  te 

--Cursor for iterating
declare @tableCursor cursor,
        @unit varchar(100)

set @tableCursor = cursor for select * from @units

open @tableCursor
fetch next from @tableCursor into @unit
while(@@fetch_status = 0)
begin

insert into @result

SELECT 
  (SELECT distinct WORK_ORDER as w FROM  te WHERE(unit=@unit)) as workorder,
 (SELECT distinct TotUnits FROM  te WHERE(TotUnits IS NOT NULL and unit=@unit)) as TotalUnits,
  (SELECT distinct ETCUnits FROM  te WHERE(ETCUnits IS NOT NULL and unit=@unit)) as ETCUnits,
  (SELECT distinct RwUnits FROM  te WHERE(RwUnits IS NOT NULL and unit=@unit)) as RwUnits,
   (SELECT distinct unit FROM  te WHERE(unit=@unit)) as UnitID,
   (SELECT distinct STUFF((SELECT ',' +  Total    FROM te   where unit=@unit 
    FOR XML PATH('')), 1, 1, '')  [Total]
    FROM te  where unit=@unit) as Total
 fetch next from @tableCursor into  @unit
end
close @tableCursor
deallocate @tableCursor

select * from @result

Popular posts from this blog

Top MNC Interview Questions- Full Stack Developer

-
Common Questions: What is Reflection? What is data integrity In SQL Server? Different types of relationships in SQL Server? Different b/w Union, Union All, Minus, Intersect? When we use the method overloading concept in c#? Real-time example for oops concepts? Why we go for Interfaces when we have abstract Classes? (Note: Provide Reason other than multiple inheritances, and abstraction concept). What is the Bundling and Minification Concept? What are the different options available in google chrome developer tools? Can we call the same request again from the network tab in the google chrome developer tab? What are ACID properties in SQL Server? What is CTE? Where we can use it and why? What are the components of ado.net? Types of Cookies in MVC and how the memory allocated? Diff. b/w array.clone() and array.copyto()? Why we need relations in SQL? Diff. b/w Task and Thread? How do you implement dependency injection in MVC .net standard f/w? Async and Await? Task and Task<T>? Diffe...
Read more

Interview Questions-2

-
 output of following program and if any errors what should be the correct syntax class baseclass {​​​​​​​​​      public void func1() {​​​​​​​​ Console.WriteLine ("Base Fun1()")​}​​​​​​​​​      public virtual void func2() {​​​​​​​​ Console.WriteLine ("Base Fun2()")​}​​​​​​​​​      public virtual  void func3() {​​​​​​​​​ Console.WriteLine ("Base Fun3()")}​​​​​​​​​ }​​​​​​​​​ class derivedclass :baseclass {​​​​​​​​​      new public void func1() {​​​​​​​​ Console.WriteLine ("derived Fun1()")​}​​​​​​​​​      public override void func2() {​​​​​​​​ Console.WriteLine ("derived Fun2()")​}​​​​​​​​​      public new void func3() {​​​​​​​​ Console.WriteLine ("derived Fun3()")​}​​​​​​​​​ }​​​​​​​​​ class Program {​​​​​​​​​      static void Main(string[] args)      {​...
Read more

How to get the user details, user current level, next level, Rank in sql server

-
 ALTER PROCEDURE [dbo].[SP_GETUSERPROFILE] @UserId int AS BEGIN --checks whether user reported any tweet or not IF EXISTS(SELECT * FROM OnGoingReports WHERE UserId=@UserId) BEGIN --if user already reported tweets then gets the user details and rank select UserId,UserName,Name,UserReportedTweetCount,Rank      from  (select UserId,UserName,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank  from      (select ogr.UserId,uc.UserName,Name,Count(*) UserReportedTweetCount from UserCredentials uc       inner join OnGoingReports ogr       on       ogr.UserId=uc.UserId        GROUP BY ogr.UserId,uc.Name,uc.UserName) as TopList ) as UserRank      where UserId=@UserId END ELSE BEGIN --if user not reported any tweets then it get the default value as 0 and rank based on tweets reported select UserId,UserName,Nam...
Read more