RSS

Monthly Archives: July 2016

Need to generate n rows based on a value in a column t sql

By: gbn

I’ll assume

  1. MyRef etc is a column in TableA
  2. You have a numbers table

Something like:

SELECT * INTO #TableA
FROM
 (
    SELECT  1 AS ID, 3 AS QUANTITY, 'MyRef' AS refColumn
    UNION ALL
    SELECT 2, 2, 'AnotherRef'
) T


;WITH Nbrs ( Number ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + Number FROM Nbrs WHERE Number < 99
)
SELECT
   A.ID, A.refColumn + CAST(N.Number AS varchar(10))
FROM
   #TableA A
   JOIN
   Nbrs N ON N.Number <= A.QUANTITY

Copy from: http://stackoverflow.com/questions/6439716/need-to-generate-n-rows-based-on-a-value-in-a-column

 
Leave a comment

Posted by on July 15, 2016 in DBMS, SQL Server