Dynamically create more columns in SQL Server 2012 query

Question

I have a SQL table with [Items] and their associated [Cost], and I am looking to create a histogram showing cost distribution. This is my (working) query used to create bins:

SELECT
    [$0-$25]=COUNT(CASE WHEN [Cost]>=0 AND [Cost]<25 THEN 1 END),
    [$25-$50]=COUNT(CASE WHEN [Cost]>=25 AND [Cost]<50 THEN 1 END)
    --etc.
FROM 
    table_name

The cost range can vary wildly for different items in the table - in other words a highest cost cannot be hard-coded. What's the best way to start at $0 and create additional $25 columns as needed? I think the solution involves using MAX([Cost]) and PIVOT together, but I'm not sure how to go about it.

If you're just getting this data from the table, you might be better of by getting the data in rows in stead of colums. You could use this query: select count(Cost/25) as number,(cost/25) * 25 as costrange from table group by Cost/25 order by 2 — Luc, Jun 05 '15 at 20:50

score 0 · Answer 1 · edited May 23 '17 at 12:22

If you are okay with rows, you can do this:

SELECT  CAST(CAST([Cost] AS INT)/25 * 25 AS VARCHAR) + '-' + 
        CAST(CAST([Cost] AS INT)/25 * 25 + 25 AS VARCHAR) AS Range, count(*) As cnt
FROM    Table_Name
group by CAST(CAST([Cost] AS INT)/25 * 25 AS VARCHAR) + '-' + 
        CAST(CAST([Cost] AS INT)/25 * 25 + 25 AS VARCHAR)

If you want to transpose the results, you'll have to write some dynamic sql. something like this.

shA.t · Answer 2 · 2015-06-09T06:39:41.847

I suggest you to create a stored procedure like this:

CREATE PROCEDURE [dbo].[SP_Test]
    @MaxValue decimal(5,2),
    @StepValue decimal(2,2)
AS
BEGIN

DECLARE @SQL nvarchar(max) = '';
DECLARE @ItemsNo decimal(2,2) = @MaxValue / @StepValue;

;WITH CTE(s)  AS (
    SELECT 0
    UNION ALL
    SELECT s + 1
    FROM CTE 
    WHERE s < @ItemsNo - 1)
SELECT @SQL = @SQL  + CASE WHEN @SQL = '' THEN '' ELSE ',' END 
                    + '[$' + CAST((s * @StepValue) AS nvarchar(5)) 
                    + '-$' + CAST(((s + 1) * @StepValue) AS nvarchar(5)) 
                    + ']=COUNT(CASE WHEN [Cost]>=' + CAST((s * @StepValue) AS nvarchar(5))
                    + ' AND [Cost]<' + CAST(((s + 1) * @StepValue) AS nvarchar(5)) 
                    + ' THEN 1 END)'
FROM CTE

SET @SQL = 'SELECT ' + @SQL + ' FROM table_name'
EXEC(@SQL)

END

Now you can run stored procedure like this:

EXEC [dbo].[SP_Test] 
     @MaxValue = 100.0, 
     @StepValue = 25.0

Dynamically create more columns in SQL Server 2012 query

2 Answers2