duplicate rows based on a column value Rather than merging rows I need to 'expand' rows, here's an example to explain:

|Material|count|  
|Rock    | 3   |  
|Gold    | 2   |   
|Silver  | 1   |   
|Wood    | 2   |  


I need one row per material e.g. translated to this

|Material|count|   
|Rock    | 1   |   
|Rock    | 1   |   
|Rock    | 1   |   
|Gold    | 1   |   
|Gold    | 1   |   
|Silver  | 1   |   
|Wood    | 1   |   
|Wood    | 1   |   


Is there an sql solution for this type of problem?
You need a sufficiently large table to multiply the source table. In your example it is sufficient to do a self join:

select material, 1 
from (
    select x.material, x.count
         , row_number() over (partition by x.material) rn 
    from t as x 
    cross join t as y
) as u 
where rn &lt;= count 
order by material;

Gold               1
Gold               1
Rock               1
Rock               1
Rock               1
Silver             1
Wood               1
Wood               1


PostgreSQL also have a nifty table function generate_series that can be used:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn 
    from t 
    cross join generate_series(1,10)
) as u 
where rn &lt;= count
order by material;


instead of 10 you can determine:

select max(count) from t


and use that to avoid creating an unnecessary large table.

While educating myself I realized that generate_series can take a variable as argument:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn 
    from t 
    cross join ( SELECT  generate_series(1, cnt)
                 FROM ( SELECT MAX(COUNT) FROM t ) u (cnt)
               ) as v 
) as u 
where rn &lt;= count
order by material;


Using LATERAL means we can correlate cnt with count in the outmost table, and thereby remove the need for row_number():

select material, 1 
from (
    select t.material, t.count
    from t 
    cross join LATERAL ( SELECT  generate_series(1, cnt)
                         FROM ( 
                             SELECT COUNT 
                             FROM t as t1
                             WHERE t.material = t1.material 
                         ) u (cnt)
                       ) as v 
) as u 
order by material;


If you made it this far, go read @ypercubeᵀᴹ answer. It does'nt get much simpler or better than that. Please vote for that one.