CODEWITHSUNDEEP
sql-server
Steve H
Steve H

Reputation: 63

SQL Rows into columns with unique column names

I have the following data:

Id  RaceId  Position    Letter_v_Letter Number  Letter  Letter_Number
1   1       1           H-C HARD        5       C HARD  C HARD5
2   1       2           J-D             3       D       D3
3   1       3           D               8       D       D8

I need to return a single row [RaceId] with columns 1 to 3 for each of the Letter_v_Letter, Number, Letter and Letter_Number

I have been looking at pivot, union all and cross apply examples on here but cant get the result I need which is:

RaceId    Letter_v_Letter_1  Letter_v_Letter_2  Letter_v_Letter_3  Number_1  Number_2  etc.
1         H-C Hard           J-D                D                  5         3

I'm using this to create a view from 2 tables to simplify a lookup query

Is it going to be a temp table job?

Upvotes: 2

Views: 36

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81960

A conditional aggregation would do the trick here

Select RaceID
      ,Letter_v_Letter_1 = max(case when Position=1 then Letter_v_Letter end)
      ,Letter_v_Letter_2 = max(case when Position=2 then Letter_v_Letter end)
      ,Letter_v_Letter_3 = max(case when Position=3 then Letter_v_Letter end)
      ,Number_1          = max(case when Position=1 then Number end)
      ,Number_2          = max(case when Position=2 then Number end)
      ,Number_3          = max(case when Position=3 then Number end)
      ,Letter_1          = max(case when Position=1 then Letter end)
      ,Letter_2          = max(case when Position=2 then Letter end)
      ,Letter_3          = max(case when Position=3 then Letter end)
      ,Letter_Number_1   = max(case when Position=1 then Letter_Number end)
      ,Letter_Number_2   = max(case when Position=2 then Letter_Number end)
      ,Letter_Number_3   = max(case when Position=3 then Letter_Number end)
 From  YourTable
 Group By RaceID

Returns

enter image description here

Upvotes: 2

Related Questions