CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
user725925
user725925

Reputation: 11

sql query to split a single column value into multiple rows

I want to create a sql query to split a single column value into multiple rows like: 

SELECT ID, PRODUCT_COUNT FROM MERCHANT WHERE ID = 3050

ID          PRODUCT_COUNT
----------- -------------
3050        591

Based on this result, I want 6 rows as follows:

ID      RANGE    
3050    0-100
3050    101-200
3050    201-300
3050    301-400
3050    401-500
3050    501-591

How can I acheive this in a query ?

Upvotes: 1

Views: 4340

Answers (5)

Jeff Moden
Jeff Moden

Reputation: 3503

Sorry... code removed. I made a mistake where if the Product_Count was evenly divisible by 100, it gave an incorrect final row.

UPDATE: Andriy's code is still correct. I was missing a "-1" in mine. I've repaired that and reposted both the test setup and my alternative solution.

Both Andriy's and my code produce the output in the correct order for this experiment, but I added an ORDER BY to guarantee it.

Here's the code for the test setup...

    --===== Conditionally drop and create a test table for
         -- everyone to work against.
         IF OBJECT_ID('tempdb..#Merchant','U') IS NOT NULL
            DROP TABLE #Merchant
    ;
     SELECT TOP 10000
            ID            = IDENTITY(INT,1,1),
            Product_Count = ABS(CHECKSUM(NEWID()))%100000
       INTO #Merchant
       FROM sys.all_columns ac1
      CROSS JOIN sys.all_columns ac2
    ;
      ALTER TABLE #Merchant
        ADD PRIMARY KEY CLUSTERED (ID)
    ;
    --===== Make several entries where there's a known test setup.
     UPDATE #Merchant
        SET Product_Count = CASE
                                WHEN ID = 1 THEN 0
                                WHEN ID = 2 THEN 1
                                WHEN ID = 3 THEN 99
                                WHEN ID = 4 THEN 100
                                WHEN ID = 5 THEN 101
                                WHEN ID = 6 THEN 99999
                                WHEN ID = 7 THEN 100000
                                WHEN ID = 8 THEN 100001
                            END
  WHERE ID < = 8
 ;

Here's the alternative I posted before with the -1 correction.

WITH
cteCreateRanges AS
(--==== This determines what the ranges are
 SELECT m.ID,
        RangeStart = t.Number*100+SIGN(t.Number),
        RangeEnd   =(t.Number+1)*100,
        Product_Count
   FROM master.dbo.spt_Values t
  CROSS JOIN #Merchant        m
  WHERE t.Number BETWEEN 0 AND (m.Product_Count-1)/100
    AND t.Type = 'P'
    AND m.ID BETWEEN 1 AND 8 -- = @FindID -<<<---<<< Or use a single variable to find.
)--==== This makes the output "pretty" and sorts in correct order
 SELECT ID,
        [Range] = CAST(RangeStart AS VARCHAR(10)) + '-'
                + CASE
                  WHEN RangeEnd <= Product_Count
                  THEN CAST(RangeEnd AS VARCHAR(10))
                  ELSE CAST(Product_Count AS VARCHAR(10))
                  END
   FROM cteCreateRanges
  ORDER BY ID, RangeStart
;

Sorry about the earlier mistake. Thanks, Andriy, for catching it.

Upvotes: 1

Andriy M
Andriy M

Reputation: 77737

WITH cte AS (
  SELECT
    m.ID,
    PRODUCT_COUNT,
    LoBound = (v.number - 1) * 100 + 1,
    HiBound = v.number * 100
  FROM MERCHANT m
    INNER JOIN master..spt_values v
      ON v.type = 'P' AND v.number BETWEEN 1 AND (m.PRODUCT_COUNT - 1) / 100 + 1
  WHERE m.ID = 3050
)
SELECT
  ID,
  RANGE = CAST(CASE LoBound
                 WHEN 1 THEN 0
                 ELSE LoBound
               END AS varchar)
        + '-'
        + CAST(CASE
                 WHEN HiBound < PRODUCT_COUNT THEN HiBound
                 ELSE PRODUCT_COUNT
               END AS varchar)
FROM cte

The first CASE makes sure the first range starts with 0, not with 1, same as in your sample output.

Upvotes: 2

Lamak
Lamak

Reputation: 70678

You can try a recursive CTE.

WITH CTE AS
(
    SELECT Id, 0 MinB, 100 MaxB, [Range]
    FROM YourTable
    UNION ALL
    SELECT Id, CASE WHEN MinB = 0 THEN MinB+101 ELSE MinB+100 END, MaxB + 100, [Range]
    FROM CTE
    WHERE MinB < [Range]
)

SELECT  Id, 
        CAST(MinB AS VARCHAR) + ' - ' + CAST(CASE WHEN MaxB>[Range] THEN [Range] ELSE MaxB END AS VARCHAR) [Range]
FROM CTE
WHERE MinB < [Range]
ORDER BY Id, [Range]
OPTION(MAXRECURSION 5000)

I put a limit to the recursion level on 5000, but you can change it (or leave it at zero, that means basically to keep doing recursion until it can)

Upvotes: 0

Tom H
Tom H

Reputation: 47402

It looks like those ranges are a piece of data, so they should really be in a table (even if you don't expect them to change, because they will). That has the nice side benefit of making this task trivial:

CREATE TABLE My_Ranges (    -- Use a more descriptive name
    range_start    SMALLINT    NOT NULL,
    range_end      SMALLINT    NOT NULL,
    CONSTRAINT PK_My_Ranges PRIMARY KEY CLUSTERED (range_start)
)
GO

SELECT
    P.id,
    R.range_start,
    CASE
        WHEN R.range_end < P.product_count THEN R.range_end
        ELSE P.product_count
    END AS range_end
FROM
    Products P
INNER JOIN My_Ranges R ON
    R.range_start <= P.product_count

If your ranges will always be contiguous then you can omit the range_end column. Your query will become a little more complex, but you won't have to worry about ranges overlapping or gaps in your ranges.

Upvotes: 0

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59553

You could create a table like this (I am changing the first range to include 100 elements like the others to make it easier, and basing it at one, so that the indexes will match the total count):

CountRangeBoundary

MinIndexInRange
---------------
1
101
201
301
401
501
601
...

Then do a θ-join like this:

SELECT m.ID,
       crb.MinIndexInRange AS RANGE_MIN,
       MIN( crb.MinIndexInRange + 100, m.PRODUCT_COUNT) AS RANGE_MAX
FROM   MERCHANT m
       JOIN CountRangeBoundry crb ON crb.MinIndexInRange <= m.PRODUCT_COUNT
WHERE  m.ID = 3050

Upvotes: 0

Related Questions