Tutorial :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?


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)  


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?


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  


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)  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »