Using same column twice with different Filter conditions

• Rakesh.Chaudhary

  SSC Eights!

  Points: 901

  More actions

  January 24, 2014 at 12:21 am

  #282727

  Hi ,

  I am trying to use same column twice with different filer conditions ,any suggestions on this will be helpfull for me.

  SELECT

  Id

  ,CASE WHEN cffit.IdentifierId = 'IIN' THEN cffit.IdentifierValue END AS 'IIN'

  ,CASE WHEN cffit.IdentifierId = 'IMS' THEN cffit.IdentifierValue END AS 'IMS'

  FROM

  dbo.clo_Identifier_TB cffit

  WHERE Id=24

  This is how I am trying ,but it is giving me null values also.

• Koen Verbeeck

  SSC Guru

  Points: 259165

  More actions

  January 24, 2014 at 12:37 am

  #1683016

  You didn't specify an ELSE clause, so if cffit.IdentifierId is neither IIN or IMS, you will get a NULL value.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Rakesh.Chaudhary

  SSC Eights!

  Points: 901

  More actions

  January 24, 2014 at 2:23 am

  #1683035

  I want these values as seprate :

  Id , IIN ,IMS

  So ,If i am using else here it is giving me blank values ,I want to use only the column with values only values :

  IDIINIMS

  24NULLNULL

  2400088NULL

  24NULL111

  24NULLNULL

  I want only

  IDIINIMS

  24 00088 111

• Koen Verbeeck

  SSC Guru

  Points: 259165

  More actions

  January 24, 2014 at 2:29 am

  #1683037

  Maybe it's a good idea to provide sample data when you initially ask the question.

  Check the first link in my signature on how to ask questions.

  SELECT

  Id

  ,IIN = MAX(IIN)

  ,IMS= MAX(IMS)

  FROM dbo.clo_Identifier_TB cffit

  GROUP BY Id

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Rakesh.Chaudhary

  SSC Eights!

  Points: 901

  More actions

  January 24, 2014 at 2:49 am

  #1683044

  Hi,

  Thanks a lot for your help , Its working with using Max and group By .