2

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?

3 Answers 3

1

Using generate_series() it's cunningly easy:

select t.material, 1 as count
from t, generate_series(1, t.count) ;

Test in sqlfiddle.com.

3

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 <= 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 <= 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 <= 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.

7
  • The solutions using generate_series() can be (vastly) simplified ;) May 9, 2018 at 16:22
  • Yes, I feel I should shoot my self after seeing your solution:-)
    – Lennart
    May 9, 2018 at 16:24
  • 2
    My , is a hidden cross join lateral so the two queries (mine and your last one) are essentially doing the same thing) May 9, 2018 at 16:41
  • 1
    sqlfiddle.com/#!17/7de8c/9 May 9, 2018 at 16:47
  • Is it true in general that you don't have to use LATERAL in psql when selecting from a function returning a table? Db2 requires a TABLE (synonym for LATERAL, but oddly enough it does not accept LATERAL in this context)?
    – Lennart
    May 9, 2018 at 16:49
-1

First create udf function

CREATE FUNCTION [dbo].[udf_ReturnVal]
(
@Input INT
)
RETURNS @Data  TABLE (
val INT
)
AS 
Begin
    Declare @IntialVal INT=1,
            @count INT=@Input
    WHILE (@IntialVal <= @count)
    BEGIN
        INSERT INTO @Data
        SELECT 1
        SET @IntialVal=@IntialVal+1
    END
    RETURN
END

Sample Data and Sql query

DECLARE  @TempData AS TABLE (Material Varchar(100),[count] INT)
INSERT INTO @TempData
SELECT 'Rock'  , 3 UNION ALL    
SELECT 'Gold'  , 2 UNION ALL     
SELECT 'Silver', 1 UNION ALL      
SELECT 'Wood'  , 2    

SELECT d.Material,
        1 AS [count]
FROM @TempData d
CROSS APPLY [dbo].[udf_ReturnVal](d.[count])

Expected Result

Material    count
-----------------
Rock         1
Rock         1
Rock         1
Gold         1
Gold         1
Silver       1
Wood         1
Wood         1
2
  • 2
    Have you verified your solution against psql? It looks as if your answer is for sql-server.
    – Lennart
    May 10, 2018 at 10:25
  • Square brackets, dbo schema, CROSS APPLY, lack of statement terminators ... Looks like a SQL Server answer, not a Postgres one. May 10, 2018 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.