Reprocess SQL DATA

Transact-SQL
1

MS SQL SERVER 2008 R2 Data Reprocessing

Reprocess SQL DATA

I would like to reprocess below data and Insert in a different table with new Columns as EntityMinLimit and EntityMaxLimit and EntityActualValue

it should reduce two rows to a single row like
BatchID EntityName EntityMinLimit EntityMaxLimit EntityActualValue
134 Body Diameter 39.8 40.2 39.911
134 Collar Diameter 19.7 20.2 19.842
134 Neck Diameter 15 17 16.769
134 Collar Partial Height 3.4 3.8 2.427
134 Neck Height 8 9 8.026
134 Shoulder Angle 0 40 23.588

this is not about calculating column MIN or MAX

2

Next time try to post data sample and create statement script.

Try this.

   CREATE TABLE #data
(
  Rowid int, 
  Batchid int,
  Name varchar(50),
  Limit decimal(6,3),
  value decimal(6,3)
)
INSERT INTO #data 
VALUES
(1,134,'Body Diameter min',39.8, 39.911),
(2,134,'Body Diameter max',40.2, 39.911),
(3,134,'Collar Diameter min',19.7, 19.842),
(4,134,'Collar Diameter max',20.2, 19.842)


WITH Ent
AS
(
 SELECT  Name AS MainName,   Batchid, right(Name,3) AS Name, Limit, Value FROM #data
),
pvt
AS
(
SELECT  SUBSTRING( Mainname,1,lEN(mainName)-3) AS MainName,  batchid, [Min],[Max], value FROM Ent
pivot(MAX(limit) FOR Name in ([Min],[Max]) )as pvt 
)

SELECT Name, Batchid, EntityMinLimit,EntityMaxLimit,Value
FROM
	(
	select row_number()over(partition by mainName order by (select 1))as ct,  MainName as Name, batchid, 
	'EntityMinLimit'=Case when Min is null then lead(Min)over(order by (select null)) else Min end,
	'EntityMaxLimit'=Case when Max is null then lead(Max)over(order by (select null)) else Max end
	, value from pvt 
	)AS FINAL
WHERE ct=1

3

My suggestion:

select batchid
      ,substring([name],1,len(name)-4) as entityname
      ,min(case when right([name],4)=' min' then limit else null end) as entityminlimit
      ,min(case when right([name],4)=' max' then limit else null end) as entitymaxlimit
      ,min(case when right([name],4)=' min' then [value] else null end) as entityactualvalue
  from yourtable
 group by batchid
         ,substring([name],1,len(name)-4)
;
1 Like
4

Hi Scarela, thanks for information that will help me to explore further , just realized my production server is SQL 2008 R2 so LEAD AND LAG functions will not work on SQL 2008 R2, do you have any other suggestions ??

5

Without the lead() function

WITH Ent
AS
(
 SELECT rowid,  Name AS MainName,   Batchid, right(Name,3) AS Name, Limit, Value FROM #data
),
pvt
AS
(
SELECT   SUBSTRING( Mainname,1,lEN(mainName)-3) AS MainName,  batchid, [Min],[Max], value FROM Ent
pivot(MAX(limit) FOR Name in ([Min],[Max]) )as pvt 
)

SELECT MainName, batchid, Max(Min) AS Min, Max(Max) AS Max, Max(value) AS Value FROM pvt 
GROUP BY MainName, batchid