SQL Help

[pandemkodi]

idi naa source table DB2

 

        pk         code                       value 

           1       code1                       10

          2        code2                        100

           3       code 3                       1000 

           

target       

                  code1   code2 code3 

                    10         100       1000

 

naku ila vastondi case stmt tho 

             code1            code2    code 3

                10                null              null 

             null                   100         null 

           null                      null              1000

NUlls raakudadhu and all in single row lo ravali 

please help

[reality]

Use Coalesce 

[Bhai]

17 minutes ago, pandemkodi said:

idi naa source table DB2

 

        pk         code                       value 

           1       code1                       10

          2        code2                        100

           3       code 3                       1000 

           

target       

                  code1   code2 code3 

                    10         100       1000

 

naku ila vastondi case stmt tho 

             code1            code2    code 3

                10                null              null 

             null                   100         null 

           null                      null              1000

NUlls raakudadhu and all in single row lo ravali 

please help

nee code padeyyi ikkada.

 

andhulo bokkalu ento chebutaru

[pandemkodi]

this is what i am using 

 select (CASE WHEN  CODE ='CODE1' THEN VALUE END) AS CODE1,
         (CASE WHEN  CODE ='CODE2' THEN VALUE END) AS CODE2,
         (CASE WHEN  CODE ='CODE3' THEN VALUE END) AS CODE3 FROM TABLE1
 

[ponugupati]

https://www.techonthenet.com/oracle/pivot.php

 

use pivot

[pandemkodi]

3 minutes ago, ponugupati said:

https://www.techonthenet.com/oracle/pivot.php

 

use pivot

db2 lo pivot ledu bayya ..Thanks 

[Bhai]

7 minutes ago, ponugupati said:

https://www.techonthenet.com/oracle/pivot.php

 

use pivot

not all dbs support pivot

[pandemkodi]

40 minutes ago, reality said:

Use Coalesce 

can u explain more bayya..i cant even aggregate as well

[prabhu_pramod]

OPTION#1: You can add MAX or MIN to your each of CASE statement

option#2: create a function which will take input as ID and return value

[pushparajukatre]

anni case statements mundu max petti try cheyi vasthundi 

[reality]

42 minutes ago, pandemkodi said:

can u explain more bayya..i cant even aggregate as well

You just have to put colums (or) expressions for those columns (like case stmt) inside coalesce function...

like

coalesce (expression1, expression2, column3, etc)

[reality]

Select Coalesce (CASE WHEN  CODE ='CODE1' THEN VALUE END) AS CODE1,
         Coalesce (CASE WHEN  CODE ='CODE2' THEN VALUE END )AS CODE2,
         Coalesce (CASE WHEN  CODE ='CODE3' THEN VALUE END) AS CODE3)

FROM TABLE1
 

Corrected...try

 

[JollyBoy]

@pandemkodi

use this

DECLARE @col NVARCHAR(MAX),@sql NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + ',''' + value +''' as ['+ code+']'
FROM tablename
   SELECT @sql = 'select ' +stuff(@col,1,1,'');
   exec(@sql)

[4Vikram]

54 minutes ago, JollyBoy said:

@pandemkodi

use this

DECLARE @col NVARCHAR(MAX),@sql NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + ',''' + value +''' as ['+ code+']'
FROM tablename
   SELECT @sql = 'select ' +stuff(@col,1,1,'');
   exec(@sql)

[pandemkodi]

1 hour ago, pushparajukatre said:

anni case statements mundu max petti try cheyi vasthundi 

max or min chestey i will loose some values ..will not work for my case