SELECT Multirow with one query (but changed please)

Question

How can i get this result with changing query below.

foo
01
02
03

 declare @a as int 
 set @a = 1

 select single.* from
 (select 
 case
 when 0=0 then '0'+
  case 
  when @a = 1 then '1'
  when @a = 2 then '2'
  when @a = 3 then '3'
  end 
 end
 as foo) single
 cross join
 (select 1 as bir union all select 2 union all select 3) multi

As you have seen, i need to write first '1' then a = a + 1 in loop (in every "when" statement)

PS: I have a big query and i need to write every row three times but with some changes.(I need to change last 2 rows from "A0" to "**" under the output -Last column-)

 TR13 BA20 1143 2009-08-31  2009-08-31  ***615134  TR74063001 40 1937,52 A0
 TR13 BA20 1143 2009-08-31  2009-08-31  ***615134  TR74063001 50 1937,52 **
 TR13 BA20 1143 2009-08-31  2009-08-31  ***615134  TR74063001 50 1937,52 **

Answer 1

You could create a table variable with one column and insert 1,2,3 into it, then OUTER join it to your data, then use your case statement.

something like:

DECLARE @onetwothree table (
 num nvarchar(2)
)
INSERT into @onetwothree (num) VALUES ('01')
INSERT into @onetwothree (num) VALUES ('02')
INSERT into @onetwothree (num) VALUES ('03')

SELECT YourBigQuery.*, OneTwoThree.num  
FROM YourBigQuery, @onetwothree AS OneTwoThree

with the case statement:

DECLARE @onetwothree table (
 num tinyint
)
INSERT into @onetwothree (num) VALUES (1)
INSERT into @onetwothree (num) VALUES (2)
INSERT into @onetwothree (num) VALUES (3)

SELECT YourBigQuery.*, (
      CASE OneTwoThree.num 
        WHEN 1 THEN YourBigQuery.YourLastColumn 
        ELSE '**'
      END
    )  
FROM YourBigQuery, @onetwothree AS OneTwoThree

Answer 2

I'll be honest with you: I have no idea what the purpose of the query in the question is. The only thing obvious is the futility of most of it.

However, if I understand your question correctly you have a bunch or records in a table based upon which you would like to generate a result set that contains three copies of each record but with slight modifications to each copy. So if you have something like:

SourceTable
--------|---------|---------|--------|
 field1 |  filed2 |  filed3 | field4 |
--------|---------|---------|--------|
   TR13 |  BA20   | 2009-08 | A0     |
   TR14 |  BA21   | 2009-08 | A1     |
   TR15 |  BA22   | 2009-08 | A2     |
   TR16 |  BA23   | 2009-08 | A3     |

And you would like to get this:

   TR13 |  BA20   | 2009-08 | A0     |
   TR13 |  BA20   | 2009-08 | **     |
   TR13 |  BA20   | 2009-08 | **     |
   TR14 |  BA21   | 2009-08 | A1     |
   TR14 |  BA21   | 2009-08 | **     |
   TR14 |  BA21   | 2009-08 | **     |
   TR15 |  BA21   | 2009-08 | A2     |
   TR15 |  BA21   | 2009-08 | **     |
   TR15 |  BA21   | 2009-08 | **     |
   TR16 |  BA23   | 2009-08 | A3     |
   TR16 |  BA23   | 2009-08 | **     |
   TR16 |  BA23   | 2009-08 | **     |

Then you could write your query like this:

 select SourceTable.field1
        , SourceTable.field2
        , SourceTable.field3
        , case when multi.bir = 1 then SourceTable.field4 else '**' end
  from SourceTable
   cross join
   (select 1 as bir union all select 2 union all select 3) multi

Answer 3

Almost same with MikyDs answer.

    declare @a as int 
    set @a = 1

    select 
      case 
        when multi.bir <> 1 then '**' 
        else single.foo
      end        
    from
    (select 
    case
    when 0=0 then '0'+
            case 
            when @a = 1 then '1'
            when @a = 2 then '2'
            when @a = 3 then '3'
            end 
    end
    as foo) single
    cross join
    (select 1 as bir union all select 2 union all select 3) multi

Result:

(No column name)
01
**
**