locked
Split Record into Multiple Records RRS feed

  • Question

  • I'm looking for a sql statement where a single row/record can be duplicated into n records.

    For example if got a select statement like:

    select * from TBL_TMP where ID=14

    The result is a single record.

    Now I would like to do a while statement to duplicate this record into exact duplicate records. I don't want to split the contect of this record but just show it in the table n times.

    while @int<@diffdate  --@diffdate(difference in months between to dates)
    begin
        select * from TBL_TMP where ID=14
        @int = @int +1
    end

    Only the result isn't in a single table but in separate result tables.
    Should I use something like union or joins to the same table TBL_TMP

     

    Wednesday, July 20, 2011 1:33 PM

Answers

  • Either use a Numbers table (see http://www.aspfaq.com/show.asp?id=2516 for what they are, how to build one, and some of it's many uses), or build one in your query or use a cte to build a small one for purposes of this query, something like

    With cte2 As
    (Select 1 As Number Union All Select 1),
    cte4 As
    (Select 1 As Number From cte2 n1 Cross Join cte2 n2),
    cte16 As
    (Select 1 As Number From cte4 n1 Cross Join cte4 n2),
    cte256 As
    (Select 1 As Number From cte16 n1 Cross Join cte16 n2),
    Numbers As
    (Select Row_Number() Over (Order By Number) As Number From cte256)
    Select *
    From TBL_TMP
    Inner Join Numbers On Number < @diffdate;
    

    Tom

    • Proposed as answer by Naomi N Wednesday, July 20, 2011 2:23 PM
    • Marked as answer by Kalman Toth Wednesday, July 27, 2011 12:53 PM
    Wednesday, July 20, 2011 1:45 PM
  • Try :

    declare @query varchar(MAX)
    declare @int int 
    declare @diffdate int
    set @int=0
    set @diffdate=3
    set @query=''
    while @int<@diffdate --@diffdate(difference in months between to dates)
    begin
    Set @query=@query + ' select * from TBL_TMP where ID=14 Union all '
    Set @int = @int +1
    end
    set @query=left(@query,Len(@query)-10)
    Exec(@query)
    

     

     

    Best regards
    • Marked as answer by FAKNL Wednesday, July 27, 2011 1:03 PM
    Wednesday, July 20, 2011 2:05 PM
  • Another Solution

     

    DECLARE @N INT

    CREATE TABLE #pqr

    (

    ColumnName VARCHAR(100),

    AliasName  VARCHAR(100)

    )

     

     

    SET @N=5 --5 times

    INSERT INTO #pqr

    VALUES

    ('nvarchar1','Designation'),

    ('nvarchar2', 'Location'),

    ('nvarchar3', 'Company'),

    ('nvarchar4', 'Department')

     

    ;WITH CCC(AA)

    AS

    (

    SELECT @N AS AA

    UNION ALL

    SELECT * FROM(

    SELECT AA-1 aa FROM CCC) AA WHERE AA>0

    )

    SELECT * FROM CCC 

    CROSS JOIN #pqr

     

    DROP TABLE #PQR

     

    Spandan B

    • Marked as answer by FAKNL Wednesday, July 27, 2011 1:03 PM
    Wednesday, July 20, 2011 3:44 PM

All replies

  • Either use a Numbers table (see http://www.aspfaq.com/show.asp?id=2516 for what they are, how to build one, and some of it's many uses), or build one in your query or use a cte to build a small one for purposes of this query, something like

    With cte2 As
    (Select 1 As Number Union All Select 1),
    cte4 As
    (Select 1 As Number From cte2 n1 Cross Join cte2 n2),
    cte16 As
    (Select 1 As Number From cte4 n1 Cross Join cte4 n2),
    cte256 As
    (Select 1 As Number From cte16 n1 Cross Join cte16 n2),
    Numbers As
    (Select Row_Number() Over (Order By Number) As Number From cte256)
    Select *
    From TBL_TMP
    Inner Join Numbers On Number < @diffdate;
    

    Tom

    • Proposed as answer by Naomi N Wednesday, July 20, 2011 2:23 PM
    • Marked as answer by Kalman Toth Wednesday, July 27, 2011 12:53 PM
    Wednesday, July 20, 2011 1:45 PM
  • Hi,

    This should do the trick...

    CREATE TABLE ##Test
    (col1 int)
    go
    insert into ##Test values(1), (2)
    
    DECLARE @DateDiff INT
    SET @DateDiff = 5
    
    ;WITH MyCTE (Col1, col2)
    AS
    (
    SELECT Col1, 1 
    FROM ##Test
    UNION ALL
    SELECT Col1, col2 + 1 as col2
    FROM MyCTE
    WHERE col2 < @DateDiff)
    SELECT Col1 FROM MyCTE

     

    Peter Carter http://sqlserverdownanddirty.blogspot.com/
    Wednesday, July 20, 2011 1:50 PM
  • Hi,

    See if this helps

    --data creation for demo
    declare @TBL_TMP table
    ( id int ,
    startdate datetime,
    enddate datetime,
    othervalue nvarchar(100))
    
    insert into @TBL_TMP
    select 1,'20110720','20111021','test1'
    union all
    select 2,'20110621','20111021','test2'
    
    
    --Demo query
    ; WITH
    L0 AS (SELECT 1 AS C UNION ALL SELECT 1), 
    L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
    L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
    L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),
    number AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L3)
    
    select id,othervalue,DATEDIFF(mm,startdate,enddate) as monthdiff 
    from @TBL_TMP
    inner join number n on n.n <= DATEDIFF(mm,startdate,enddate)
    order by id
    

     


    - Chintak (My Blog)

    Wednesday, July 20, 2011 1:53 PM
  • Try :

    declare @query varchar(MAX)
    declare @int int 
    declare @diffdate int
    set @int=0
    set @diffdate=3
    set @query=''
    while @int<@diffdate --@diffdate(difference in months between to dates)
    begin
    Set @query=@query + ' select * from TBL_TMP where ID=14 Union all '
    Set @int = @int +1
    end
    set @query=left(@query,Len(@query)-10)
    Exec(@query)
    

     

     

    Best regards
    • Marked as answer by FAKNL Wednesday, July 27, 2011 1:03 PM
    Wednesday, July 20, 2011 2:05 PM
  • Thanks for all the replies.

    I"ll give it a try and let you know what the outcome is.

    Wednesday, July 20, 2011 3:09 PM
  • Another Solution

     

    DECLARE @N INT

    CREATE TABLE #pqr

    (

    ColumnName VARCHAR(100),

    AliasName  VARCHAR(100)

    )

     

     

    SET @N=5 --5 times

    INSERT INTO #pqr

    VALUES

    ('nvarchar1','Designation'),

    ('nvarchar2', 'Location'),

    ('nvarchar3', 'Company'),

    ('nvarchar4', 'Department')

     

    ;WITH CCC(AA)

    AS

    (

    SELECT @N AS AA

    UNION ALL

    SELECT * FROM(

    SELECT AA-1 aa FROM CCC) AA WHERE AA>0

    )

    SELECT * FROM CCC 

    CROSS JOIN #pqr

     

    DROP TABLE #PQR

     

    Spandan B

    • Marked as answer by FAKNL Wednesday, July 27, 2011 1:03 PM
    Wednesday, July 20, 2011 3:44 PM