Data extending data points

In SSMS, I have a table with columns x, y, and z. The table describes the outer edge of a 3 dimensional landscape.

For each of these points, I want to create two new rows.

Row 1 (horizontal transformation)

  • If the row is on the left half of the x range then I want to add a row with an x value of zero and I want to keep the same y and z values
  • If the row is on the right half of the x range then I want to add a row with an x value of max(x) and I want to keep the same y and z values

Row 2 (vertical transformation)

  • If the row is on the bottom half of the y range then I want to add a row with a y value of 0 and I want to keep the same x and z values
  • If the row is on the top half of the y range then I want to add a row with a y value of max(y) and I want to keep the same x and z values

Sample Data

y           x            z
549963.253  293753.256  -1757.842
549677.683  297976.459  -1620.247
558873.083  309630.362  -1577.014
551514.017  285295.081  -1658.569
555753.949  304347.046  -1492.961
536874.470  298448.332  -1558.507
552574.391  297353.342  -1546.272
546114.102  314972.371  -1565.327
531771.077  292503.656  -1407.629
556505.724  306902.763  -1538.788
549090.490  288432.773  -1371.135
548953.512  302602.260  -1497.929
548690.957  305676.277  -1487.923
562452.487  307043.899  -1417.623
544858.390  311193.462  -1482.092
548554.806  297235.212  -1367.308
553771.112  305062.276  -1363.437
546734.711  283794.627  -1474.116
564066.496  307061.364  -1385.391
564559.510  303185.772  -1517.316
545010.760  305789.647  -1374.378
551738.567  304001.895  -1430.129
564864.830  305701.078  -1477.312
571958.715  301258.917  -1373.429
558356.397  291184.015  -1450.972
570675.003  300697.550  -1372.930
565582.654  311963.297  -1351.212
561914.940  292780.756  -1357.631
557361.304  295066.815  -1451.466
565150.027  283631.470  -1265.197
576099.475  280598.922  -1270.946
563291.031  298065.163  -1493.635
564513.518  297643.693  -1482.868
565473.258  300830.589  -1292.136
551821.588  296706.306  -1336.422
578457.061  290637.947  -1316.241
568051.844  295262.721  -1264.061
560403.936  292078.795  -1358.460
580655.446  289384.664  -1344.182
567051.307  303542.217  -1262.574
586391.245  298296.507  -1307.636
563443.629  294610.675  -1369.074
573287.477  287207.832  -1421.306
570396.315  286699.258  -1260.884
569138.520  299396.374  -1404.514
558212.414  290440.978  -1266.421
568117.293  302236.377  -1328.212
575731.212  286185.554  -1408.601
570917.003  288588.953  -1471.324
581951.944  301935.116  -1428.110
576289.570  287879.973  -1384.389
579719.593  298122.950  -1367.387
578786.688  296023.543  -1312.228
583767.111  307032.486  -1280.412
588382.842  297357.328  -1281.912
584809.964  295886.098  -1335.179
586010.252  292049.677  -1323.906
580153.419  289230.973  -1323.889
605095.221  305602.448  -1357.968
594649.250  293677.044  -1355.983
597170.092  313305.495  -1344.910
594051.634  285127.937  -1213.801
584710.587  299341.755  -1256.289
595733.382  280755.433  -1206.703
590237.055  307659.289  -1201.313
585845.543  299665.307  -1324.558
595060.226  298845.650  -1251.863
586105.384  299610.237  -1235.170
596660.231  288586.487  -1169.211
592859.509  298351.276  -1198.438
602007.608  283748.480  -1329.079
591558.062  293431.407  -1202.938
600133.990  294958.829  -1256.143
610423.996  292256.291  -1233.516
600825.888  302732.720  -1148.829
594207.291  289864.311  -1217.495
592249.657  287346.842  -1166.952
599140.528  313087.065  -1154.344
601740.225  298190.310  -1186.768

2 answers

  • answered 2018-01-11 21:13 Paparazzi

    Top half is not clear

    This is the horizontal transformation

      declare @minX money = ( select min([StartingPrice]) FROM [Test].[dbo].[Auction]  )
      declare @maxX money = ( select max([StartingPrice]) FROM [Test].[dbo].[Auction]  )
      declare @avgX money = ( (@minX + @maxX) / 2  )
    
      select @minX 
      select @maxX 
      select @avgX 
    
    
      select [AuctionID], [StartingPrice] from [Test].[dbo].[Auction]
      union all
      select [AuctionID], 0 from [Test].[dbo].[Auction] where [StartingPrice]     <  @avgX 
      union all
      select [AuctionID], @maxX from [Test].[dbo].[Auction] where [StartingPrice] >= @avgX
    

  • answered 2018-01-11 21:13 HABO

    The following code shows one approach. It makes a pass through the data gathering the minima and maxima (or getting them from the appropriate indexes), calculates the center values, and then generates the rows to be inserted with the adjusted x and y values.

    As implied in the original question, rows with values matching the range center values are skipped. If that is not correct then the where clauses should be removed and the case expressions tweaked as required to substitute the correct values.

    The insert statement is left as an exercise for the reader.

    -- Sample data.
    declare @Samples as Table ( SampleId Int Identity,
      X Decimal(10,4), Y Decimal(10,4), Z Decimal(10,4) );
    insert into @Samples ( X, Y, Z ) values
      ( -5, 1, 22 ), ( 0, 10, 18 ), ( 5, 20, -16 );
    select * from @Samples;
    
    -- Demonstrate getting the minima, maxima and center values.
    with
      Step1 as (
        select Min( X ) as MinX, Max( X ) as MaxX, Min( Y ) as MinY, Max( Y ) as MaxY
          from @Samples ),
      Step2 as (
        select MinX, MaxX, ( MinX + MaxX ) / 2.0 as CenterX, MinY, MaxY, ( MinY + MaxY ) / 2.0 as CenterY
          from Step1 )
      select * from Step2;
    
    -- Get the rows to be inserted.
    with
      Step1 as (
        select Min( X ) as MinX, Max( X ) as MaxX, Min( Y ) as MinY, Max( Y ) as MaxY
          from @Samples ),
      Step2 as (
        select MinX, MaxX, ( MinX + MaxX ) / 2.0 as CenterX, MinY, MaxY, ( MinY + MaxY ) / 2.0 as CenterY
          from Step1 )
      select S.SampleId, case when X < CenterX then 0 else MaxX end as X, Y, Z, 'NewX' as RowType
        from @Samples as S cross join
          Step2 as S2
        where S.X != S2.CenterX
      union all
      select S.SampleId, X, case when Y < CenterY then 0 else MaxY end, Z, 'NewY'
        from @Samples as S cross join
          Step2 as S2
        where S.Y != S2.CenterY;