Roll multiple records into 1

• legeboka

  SSC Veteran

  Points: 268

  More actions

  March 2, 2020 at 4:06 pm

  #3730201

  I'm trying to roll multiple records into one replacing null value.

  IF (SELECT OBJECT_ID('tempdb..#Table50'))is not null

  DROP TABLE #Table50

  CREATE TABLE #Table50 (

  MyID int,

  A int,

  B int,

  C int)

  INSERT INTO #Table50 ( MYID, A, B, C)

  SELECT 1, NULL,NULL, 53

  UNION ALL

  SELECT 1, NULL,NULL, 54

  UNION ALL

  SELECT 1, NULL,924, NULL

  UNION ALL

  SELECT 1, NULL,920, NULL

  UNION ALL

  SELECT 1, 27,NULL, NULL

  UNION ALL

  SELECT 1, 27,NULL, NULL

  UNION ALL

  SELECT 2, NULL, 825, NULL

  UNION ALL

  SELECT 2, 23,NULL, NULL

  ---------------------------------

  Expected outcome below

  SELECT 1 AS 'MyID', 27 AS A,924 AS B,53 AS C

  UNION ALL

  SELECT 1, 27,920,54

  UNION ALL

  SELECT 2,23,525,NULL

  Thank you

   

   

  • This topic was modified 4 years, 1 month ago by  legeboka.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 2, 2020 at 4:25 pm

  #3730211

  Why do 53 and 924 go in the first row of your results and 54 and 920 in the second?  Is it because that's the order they were inserted into the table?  A table isn't an ordered set and therefore you will need a column to break the tie between 53 and 54 and between 924 and 920.

  John

• legeboka

  SSC Veteran

  Points: 268

  More actions

  March 2, 2020 at 4:29 pm

  #3730212

  You right. There should be order

• michael.cole 47030

  SSC Eights!

  Points: 804

  More actions

  March 11, 2020 at 12:15 am

  #3733044

  In which case you would need to add another column, possibly simply an identity column to provide that order.

  Then, you would select all values from each column individually, in order, creating a new column being the row_number over (order by [the identity column]) (you can look this function up).  And then from those three queries, join them based on the row number.

  See how you go with this.