CODEWITHSUNDEEP
sqlsql-server
Data_101
Data_101

Reputation: 953

Add new column to temp table based on values from a column

I would like to create a temp table from the below table.

------------------------|--------
Date                    | Length
------------------------|--------
2014-08-28 00:00:00.000 | 1.5 
2014-08-28 00:00:00.000 | 2.6
2014-08-28 00:00:00.000 | 1.5
2014-08-28 00:00:00.000 | 3.3
2014-08-28 00:00:00.000 | 1.1
2014-08-28 00:00:00.000 | 8.5
2014-08-28 00:00:00.000 | 8.6
2014-08-28 00:00:00.000 | 11.3

And have the temp table look like the one below. 

Date                    | Length  | Length_Range
------------------------|---------|--------------
2014-08-28 00:00:00.000 |  1.5    |   1-4
2014-08-28 00:00:00.000 |  2.6    |   1-4
2014-08-28 00:00:00.000 |  6.5    |   5-10
2014-08-28 00:00:00.000 |  3.3    |   1-4
2014-08-28 00:00:00.000 |  1.1    |   1-4
2014-08-28 00:00:00.000 |  8.5    |   5-10
2014-08-28 00:00:00.000 |  8.6    |   5-10
2014-08-28 00:00:00.000 |  11.3   |   11-15

I would like to be able to define the [Length_Range].

Microsoft SQL Server 2016. Compatibility level: SQL Server 2005 (90)

Upvotes: 0

Views: 985

Answers (5)

Robert Andrzejuk
Robert Andrzejuk

Reputation: 5222

You can use the Create Table ... As Select... syntax

CREATE TABLE temp_table AS
SELECT [date], [length], 
       CASE  
         WHEN length BETWEEN 1 AND 4 THEN '1-4'
         WHEN length BETWEEN 5 AND 10 THEN '5-10'
         WHEN length BETWEEN 11 AND 15 THEN '11-15' 
       END AS LENGTH_RANGE
 FROM orig_table

Sources:

Tech on the Net - SQL: CREATE TABLE AS Statement

MSDN - CREATE TABLE AS SELECT

Oracle - Create Table

...

Upvotes: 1

Chanukya
Chanukya

Reputation: 5883

CREATE TABLE #TABLE1
    ([DATE] DATETIME, [LENGTH] FLOAT)
INSERT INTO #TABLE1
    ([DATE], [LENGTH])
VALUES
    ('2014-08-28 00:00:00', 1.5),
    ('2014-08-28 00:00:00', 2.6),
    ('2014-08-28 00:00:00', 1.5),
    ('2014-08-28 00:00:00', 3.3),
    ('2014-08-28 00:00:00', 1.1),
    ('2014-08-28 00:00:00', 8.5),
    ('2014-08-28 00:00:00', 8.6),
    ('2014-08-28 00:00:00', 1.3)

SELECT *,CASE  
WHEN LENGTH BETWEEN 1 AND 4 THEN '1-4'
WHEN LENGTH BETWEEN 5 AND 10 THEN '5-10'
WHEN LENGTH BETWEEN 11 AND 15 THEN '11-15' END AS LENGHT_RANGE
FROM #TABLE1

OUTPUT

Date                    Length  LENGHT_RANGE
2014-08-28 00:00:00.000  1.5          1-4
2014-08-28 00:00:00.000  2.6          1-4
2014-08-28 00:00:00.000  1.5          1-4
2014-08-28 00:00:00.000  3.3          1-4
2014-08-28 00:00:00.000  1.1          1-4
2014-08-28 00:00:00.000  8.5          5-10
2014-08-28 00:00:00.000  8.6          5-10
2014-08-28 00:00:00.000  1.3          1-4

Upvotes: 1

Yugandhar
Yugandhar

Reputation: 87

--May help this

;WITH cte (
    [Date]
    ,[Length]
    )
AS (
    SELECT cast('2014-08-28 00:00:00.000' AS DATETIME)
        ,CAST('1.5' AS DECIMAL(4, 2))

    UNION ALL

    SELECT '2014-08-28 00:00:00.000'
        ,'2.6'

    UNION ALL

    SELECT '2014-08-28 00:00:00.000'
        ,'1.5'

    UNION ALL

    SELECT '2014-08-28 00:00:00.000'
        ,'3.3'

    UNION ALL

    SELECT '2014-08-28 00:00:00.000'
        ,'1.1'

    UNION ALL

    SELECT '2014-08-28 00:00:00.000'
        ,'8.5'

    UNION ALL

    SELECT '2014-08-28 00:00:00.000'
        ,'8.6'

    UNION ALL

    SELECT '2014-08-28 00:00:00.000'
        ,'11.3'
    )
SELECT *
    ,CASE 
        WHEN Length < 1
            THEN '< 1'
        WHEN Length BETWEEN 1
                AND 4
            THEN '1-4'
        WHEN Length BETWEEN 5
                AND 10
            THEN '5-10'
        WHEN Length BETWEEN 11
                AND 15
            THEN '11-15'
        WHEN Length > 15
            THEN '> 15'
        END AS Length_Range
FROM cte

Upvotes: 0

Nayan Patel
Nayan Patel

Reputation: 154

I understand your question. but your answer already available on following link.

Click here

So refer this link and get more details.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Use case:

select t.*,
       (case when length >= 1 and length < 4 then '1-4'
             when length < 10 then '5-10'
             when length < 15 then '11-15'
             else '16+'
        end) as length_range
into #temp_t
from t;

Upvotes: 1

Related Questions