CODEWITHSUNDEEP
sqlsql-serverdatabase
user2302158
user2302158

Reputation: 463

how to select a value based on unique id column

Please help me ,

I have table with 3 column , when i select the column i need to dulicate the value based on the id

  Id      Days      Values
   1      5         7
   1     NULL      NULL
   1     NULL      NULL
   2      7         25
   2     NULL      NULL
   2      8        274
   2     NULL      NULL

I need a Result as 

  Id     Days      Values
   1      5         7
   1      5         7
   1      5         7
   2      7         25
   2      7         25
   2      8         274
   2      8         274

`

Upvotes: 1

Views: 695

Answers (1)

xQbert
xQbert

Reputation: 35323

Generate a set of data with the desired repeating values (B). Then join back to the base set (A) containing the # of record to repeat. This assumes that each ID will only have one record populated. If this is not the case, then you will not get desired results.

SELECT B.ID, B.MDays as Days, B.Mvalues as values 
FROM  TABLE A
INNER JOIN (SELECT ID, max(days) mDays, Max(values) Mvalues
            FROM Table
            GROUP BY ID) B
 on A.ID = B.ID

And due to updates in question....--

This will get you close but without a way to define grouping within ID's I can't subdivide the records into 2 and 2

SELECT B.ID, B.Days as Days, B.values as values 
FROM  TABLE A
INNER JOIN (SELECT Distinct ID, days, values
            FROM Table
            GROUP BY ID) B
 on A.ID = B.ID
 and A.days is null

This isn't even close enough as we still Don't know how to order the rows... It assumes order within the table which can't be trusted. We generate a row number for each row in the table using the Row_number Over syntax Grouping (partition by) the ID and days with the order of ID days (which doesn't work because of the null values)

We then join this data set back to a distinct set of values on ID and days to get us close... but we still need some grouping logic. beyond that of ID that handles the null records and lack of order or grouping.

With CTE AS (
SELECT ID, Days, Values, Row_Number() Over (partition by ID, Days ORDER BY ID, Days) RN
FROM Table)

SELECT * 
FROM (SELECT Distinct ID, Days, Values, max(RN) mRN FROM CTE GROUP BY ID, Days, Values) A
INNER JOIN CTE B
  ON A.ID = B.ID
 and A.Days = B.Ddays
 and mRN <= B.RN
Order by B.RN

Upvotes: 1

Related Questions