SQL Merging Rows

Question

I am trying to break and merge the results of a table like this. (For Bingo!) I can write CASEs easily enough to break the columns up by the first letter. However I end up with nulls displaying as the rows are treated as distinct by their IDs. I have tried some of the merge examples here but seem to be stumped. The IDs don't matter and I have control of how the initial data is collected.

ID  |  Number     |
====================
1   |      N6     | 
2   |      B22    |
3   |      B5     |
4   |      I9     |
5   |      N4     |


B  |  I  |  N  |  G  |  O  |
============================
B5 |  I9 | N4  |  -- | --  |
B22| --  | N6  |  -- | --  |

It's SQL 2008 SP1. I don't really have a functioning select statement yet :/ At least that does what I am after. — bnw, Jan 28 '13 at 21:54

score 3 · Accepted Answer · answered Jan 28 '13 at 21:55

;WITH CTE AS
(
SELECT *,
       LEFT([Number],1) AS Letter,
       ROW_NUMBER() OVER (PARTITION BY LEFT([Number],1) 
                              ORDER BY CAST(SUBSTRING([Number],2,2) AS INT) ) AS RN
FROM Table1
)
SELECT 
    MAX(CASE WHEN Letter = 'B' THEN [Number] END) AS B,
    MAX(CASE WHEN Letter = 'I' THEN [Number] END) AS I,
    MAX(CASE WHEN Letter = 'N' THEN [Number] END) AS N,
    MAX(CASE WHEN Letter = 'G' THEN [Number] END) AS G,
    MAX(CASE WHEN Letter = 'O' THEN [Number] END) AS O
FROM CTE
GROUP BY RN

SQL Fiddle

Taryn · Answer 2 · 2013-01-28T22:11:27.420

This type of data transformation is a pivot. There are several ways that you can rotate the data.

Case with Aggregate: this uses a case expression and an aggregate function to create the columns

select 
  max(case when letter = 'b' then number end) B,
  max(case when letter = 'i' then number end) I,
  max(case when letter = 'n' then number end) N,
  max(case when letter = 'g' then number end) G,
  max(case when letter = 'o' then number end) O
from
(
  select left(number, 1) letter,
    number,
    row_number() over(partition by left(number, 1) 
                     order by cast(substring(number, 2, 2) as int)) rn
  from yourtable
) src
group by rn
order by rn

See SQL Fiddle with Demo

PIVOT:

Or since you are using SQL Server 2005+, you can use the PIVOT function:

select b, i, n, g, o
from
(  
  select left(number, 1) letter,
    number,
    row_number() over(partition by left(number, 1) 
                      order by cast(substring(number, 2, 2) as int)) rn
  from yourtable
) src
pivot
(
  max(number)
  for letter in (b, i, n, g, o)
) piv

See SQL Fiddle with Demo

Nice example with `PIVOT` - much better solution IMO – Jan 28 '13 at 22:03 — , Jan 28 '13 at 22:03

SQL Merging Rows

2 Answers2