Need CrossApply Logic. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need CrossApply Logic.

Author

Topic

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-12-11 : 15:20:52

[code]ID AGE A B C Name
6 20 200 0 0 MARY
8 21 220 0 120 RE

OutPut:
ID AGE Code AMT Name
6 20 E1 200 MARY
8 21 E2 220 RE
8 21 E4 120 RE [/code]

Rule for Code Column:

If Age = 20 and Col A then code = E1
IF Age = 20 and Col B then code = E1A
If Age = 20 and Col C then code = E2A

If Age = 21 and ColA then code = E2
If Age = 21 and ColB then code = E3
If Age = 21 and ColC then code = E4

I need to populate only when COLA or COLB or COL C has values

Name,ID and Age should be repeated.

Can somebody throw lights?

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-11 : 15:26:51

Can you unpivot the middle part (A, B, C) to get something like

6 200 A
8 220 A
6 0 B
8 0 B
6 0 C
8 120 C

And then use that a a derived table making ID = unpivot.ID and applying your logic?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-11 : 16:11:07

Maybe the others would come up with a better solution...but I could think of this..

declare @t table 
(ID int,AGE int,A int,	B int,C int,[Name] varchar(30))
insert @t
select 6,	20,	200,	0,	0,	'MARY ' union all
select 8,	21,	220,	0,	120,	'RE'

select ID, 
       AGE, 
       [Name], 
       amount, 
       case 
         when AGE = 20 
              and code = 'A' 
         then 'E1' 
         else case 
                when AGE = 20 
                     and code = 'B' 
                then 'E1A' 
                else case 
                       when AGE = 20 
                            and code = 'C' 
                       then 'E2A' 
                       else case 
                              when AGE = 21 
                                   and code = 'A' 
                              then 'E2' 
                              else case 
                                     when AGE = 21 
                                          and code = 'B' 
                                     then 'E3' 
                                     else case 
                                            when AGE = 21 
                                                 and code = 'C' 
                                            then 'E4' 
                                            else null 
                                          end 
                                   end 
                            end 
                     end 
              end 
       end as [Code] 
from   (select ID, 
               AGE, 
               [Name], 
               amount, 
               code 
        from   ((select ID, 
                        AGE, 
                        [Name], 
                        [A], 
                        [B], 
                        [C] 
                 from   @t)) p 
               unpivot 
               (amount 
                for code in ( [A],[B],[C] ) ) as unpvt) t 
where  amount > 0

I know...the code looks clumsy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-11 : 22:23:46

[code]
declare @sample table
(
ID int,
AGE int,
A int,
B int,
C int,
Name varchar(5)
)
insert into @sample
select 6, 20, 200, 0, 0, 'MARY' union all
select 8, 21, 220, 0, 120, 'RE'

; with data (ID, AGE, AmtCol, AMT, [Name])
as
(
select ID, AGE, AmtCol = 'A', AMT = A, [Name]
from @sample
where A <> 0

union all

select ID, AGE, AmtCol = 'B', AMT = B, [Name]
from @sample
where B <> 0

union all

select ID, AGE, AmtCol = 'C', AMT = C, [Name]
from @sample
where C <> 0

)
select ID, AGE,
Code = case
when AGE = 20 and AmtCol = 'A' then 'E1'
when AGE = 20 and AmtCol = 'B' then 'E1A'
when AGE = 20 and AmtCol = 'C' then 'E2A'
when AGE = 21 and AmtCol = 'A' then 'E2'
when AGE = 21 and AmtCol = 'B' then 'E3'
when AGE = 21 and AmtCol = 'C' then 'E4'
end,
AMT, Name
from data

/*
ID AGE Code AMT Name
----------- ----------- ---- ----------- -----
6 20 E1 200 MARY
8 21 E2 220 RE
8 21 E4 120 RE

(3 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-12 : 01:11:56

quote:
Originally posted by sodeep

ID AGE A B C Name
6 20 200 0 0 MARY
8 21 220 0 120 RE

OutPut:
ID AGE Code AMT Name
6 20 E1 200 MARY
8 21 E2 220 RE
8 21 E4 120 RE

Rule for Code Column:

If Age = 20 and Col A then code = E1
IF Age = 20 and Col B then code = E1A
If Age = 20 and Col C then code = E2A

If Age = 21 and ColA then code = E2
If Age = 21 and ColB then code = E3
If Age = 21 and ColC then code = E4

I need to populate only when COLA or COLB or COL C has values

Name,ID and Age should be repeated.

Can somebody throw lights?


SELECT t.ID,t.AGE,tmp.AMT,tmp.Code,tmp.AMT,t.Name
FROM tbl t
CROSS APPLY (SELECT A AS AMT,
             CASE WHEN AGE =20 THEN 'E1' WHEN AGE=21 THEN  'E2' END AS Code
             FROM tbl
             WHERE ID=t.ID
             AND A >0
             UNION ALL
             SELECT B ,
             CASE WHEN AGE =20 THEN 'E1A' WHEN AGE=21 THEN  'E3' END 
             FROM tbl
             WHERE ID=t.ID
             AND B >0
             UNION ALL
             SELECT C,
             CASE WHEN AGE =20 THEN 'E2A' WHEN AGE=21 THEN  'E4' END
             FROM tbl
             WHERE ID=t.ID
             AND C >0
             )tmp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources