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

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

How to update the user level when ever user report any tweet in sql

-
  ALTER PROCEDURE [dbo].[SP_UPDATEUSERLEVEL] @Userid INT AS BEGIN Declare @CountOfUserReportedTweets INT Declare @LevelBadgeId INT SET @CountOfUserReportedTweets=(select count(*) from OnGoingReports where UserId=@Userid) --CHECKS WHETHER THE LEVEL EXIST OR NOT IF EXISTS(select Top 1 * from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) BEGIN --IF LEVEL EXISTS UPDATE OR INSERT WITH THAT LEVEL ID SET @LevelBadgeId=(select Top 1 Id from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLevel SET LevelBadgeId=@LevelBadgeId WHERE UserId=@Userid END ELSE BEGIN INSERT INTO UserLevel(UserId,LevelBadgeId) VALUES (@Userid,@LevelBadgeId) END END ELSE BEGIN --IF NO LEVEL EXISTS THEN INSERTING DEFAULT LEVEL IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLeve...
Read more

Implement virtualisation in kendo drop down list using jquery ajax in mvc, aspx page

-
virtualization helps to load huge data fastly. $("#abcd").kendoDropDownList({                 filter: "contains",                 dataTextField: "WorkOrderNumber",                 dataValueField: "WorkOrderNumber",                 virtual: {                     itemHeight: 26,                     valueMapper: function (options) {                     }     ...
Read more