CODEWITHSUNDEEP
sqlsql-server
DRUIDRUID
DRUIDRUID

Reputation: 369

SELECT to return duplicate distinct column values

Tying to have query that can duplicate some column values based off values in other columns. ID1 column will always have a value and ID2 and ID3 may or may not. Data in table looks like this.

RefNum  DetailDesc        ID1        ID2      ID3        HRs
43      Test detail 1.    110011     220022   330033     1.5
43      Test detail 2.    110011     220022   330033     0.75
43      Test detail 3.    110011     220022   NULL       1.25
43      Test detail 4.    110011     220022   NULL       1.5
43      Test detail 5.    110011     NULL     NULL       0.5
43      Test detail 6.    110011     NULL     NULL       2

Wanting to to query this table to show resultset like this

RefNum  IDOrder   ID        DetailDesc        HRs
43      1         110011    Test detail 1.    1.5
43      2         220022    Test detail 1.    1.5
43      3         330033    Test detail 1.    1.5
43      1         110011    Test detail 2.    0.75
43      2         220022    Test detail 2.    0.75
43      3         330033    Test detail 2.    0.75
43      1         110011    Test detail 3.    1.25
43      2         220022    Test detail 3.    1.25
43      1         110011    Test detail 4.    1.5
43      2         220022    Test detail 4.    1.5
43      1         110011    Test detail 5.    0.5
43      1         110011    Test detail 6.    2

Been stabbing at this trying case selects, and inserting into temp tables. Im stuck.

Upvotes: 0

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use cross apply to unpivot the data:

select t.RefNum, v.IDOrder, v.ID, t.DetailDesc, t.HRs
from t cross apply
     (values (1, t.id1), (2, t.id2), (3, t.id3)
     ) v(idorder, id)
where v.id is not null;

Upvotes: 1

Related Questions