CODEWITHSUNDEEP
sql-server-2005
amr osama
amr osama

Reputation: 1149

sql union problem

in this SQL code

DECLARE @n tinyint
   WHILE (@n > 0)
   BEGIN
    SELECT  @n AS 'Number'
      ,CASE
        WHEN (@n % 2) = 1
          THEN 'EVEN'
        ELSE 'ODD'
       END AS 'Type'
    SET @n = @n - 1
   END

How could I put union clause in this could to have the result shown in one result set?

Upvotes: 0

Views: 220

Answers (4)

priyanka.sarkar
priyanka.sarkar

Reputation: 26498

You can even try this

DECLARE @n tinyint
declare @tbl table(number tinyint,NumberType varchar(10))

set @n  = 10
   WHILE (@n > 0)
   BEGIN
   insert into @tbl

    SELECT  @n AS 'Number'
      ,CASE
        WHEN (@n % 2) = 0
          THEN 'EVEN'      
       END AS 'Type'

       UNION

       SELECT  @n AS 'Number'
      ,CASE
        WHEN (@n % 2) <> 0
          THEN 'ODD'
       END AS 'Type' 

    SET @n = @n - 1

   END
   select * from @tbl where NumberType is not null

The output is 

**number    NumberType**
10  EVEN
9   ODD
8   EVEN
7   ODD
6   EVEN
5   ODD
4   EVEN
3   ODD
2   EVEN
1   ODD

But what you actually want to achieve?

Upvotes: 1

Andrew
Andrew

Reputation: 967

It is easier to rethink your problem. Think in sets!

First generate the set of numbers. Then clasify the set of numbers as Even or Odd.

declare @numberCount int 
set @numberCount = 10;

with numbers as
(
  select 1 as number
  union all
  select number + 1 from numbers where number < @numberCount
)

select number,
       case        
          when (number % 2) = 1     
             then 'EVEN'      
             else 'ODD'      
       end AS 'Type' 
 from numbers option(maxrecursion 10000)

Upvotes: 0

Himadri
Himadri

Reputation: 8876

Try the following:

DECLARE @n TINYINT
DECLARE @sql VARCHAR(max)
SET @sql=''
SET @n=10
   WHILE (@n > 0)
   BEGIN
  SET @sql=@sql+' SELECT  '+CONVERT(VARCHAR(50) ,@n)+' AS ''Number''
      ,CASE
        WHEN ('+CONVERT(VARCHAR(50) ,@n)+' % 2) = 1
          THEN ''ODD''
        ELSE ''EVEN''
       END AS TYPE '
       IF @n>1 SET @sql=@sql+' Union '
    SET @n = @n - 1
   END

   EXEC( @sql)

Upvotes: 0

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

you could just do this:

DECLARE @n TINYINT
SET @n = 100
SELECT  number,
        CASE WHEN (number % 2) = 1 THEN 'EVEN' ELSE 'ODD' END AS 'Type'
FROM    (
            SELECT ROW_NUMBER() OVER(ORDER BY number) AS number
            FROM master..spt_values
        ) t
WHERE   number < @n

Upvotes: 0

Related Questions