Case Statement

• Stephen crocker

  SSCrazy

  Points: 2512

  More actions

  October 20, 2007 at 9:59 am

  #199332

  I have a situation where I could use some help

  Here is the table structure

  cstid________point type______ point value

  000001------educ-min-------- 2.00

  000001------total------------ 5.00

  000002------educ-min-------- 2.00

  000002------total------------ 5.00

  (Sorry table looks bad)

  I am using the following statement

  Select cstid,

  Case when [Point type] = 'educ-min' then [Point Value] as Edupts

  Case when [Point type] = 'total' then [Point Value] as Totalpts

  from {tablename}

  SELECT CSTID,

  (Select

  Case

  When [Point Type]= 'Educ - Min' then [Point]

  end) as EduPts,

  (Select

  Case

  When [Point Type]= 'Total' then [Point]

  end) as TTLPts

  from {tblname}

  What is get is two rows with the data that looks like this

  cstid________EduPts_________TTLPts

  00001--------2.00-------------null

  00001--------Null--------------5.00

  and so on with the data based on cstid.

  What I need is CstID on one line with edupts and ttlpts on the same line instead of the null statements in there so the data will look like this

  cstid________EduPts________TTLPts

  00001--------2.00-----------5.00

  Can anyone help with this. Thanks in advance

• Rajesh Patavardhan

  SSCrazy

  Points: 2801

  More actions

  October 20, 2007 at 11:32 am

  #743870

  Use group by instead of CASE, see attached script

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  October 20, 2007 at 1:12 pm

  #743874

  This is a classic "Name/Value" table that you have where both the name of the attribute and the value of the attribute are contained in rows instead of having separate columns for each attribute. Some use it as a method for making a table with "infinite" user-definable columns... they have some utility but I generally don't like them because all values must be stored as some for of character based datatype... that requires implicit/explicit conversions of all numbers and dates, etc. Makes for some pretty slow performance and you can't usually take direct advantage of some of the inherent power of certain data types such as DATETIME. That, notwithstanding...

  Instead of doing multiple correlated sub-queries in the SELECT list (which tends to further impinge upon performance), consider using a CROSSTAB to return the data... makes for some very fast returns compared to sub-queries and other methods...

  --===== This simulates your table and is NOT PART OF THE SOLUTION!

  -- It's just for demonstration purposes

  DECLARE @yourtable TABLE (CstID INT, [Point Type] VARCHAR(20), [Point Value] DECIMAL (9,2))

  INSERT INTO @yourtable

  (CstID, [Point Type], [Point Value])

  SELECT '000001','educ min','2.00' UNION ALL

  SELECT '000001','total' ,'5.00' UNION ALL

  SELECT '000002','educ min','2.00' UNION ALL

  SELECT '000002','total' ,'5.00'

  --===== This is CROSSTAB solution. Lookup CROSSTAB in Books On Line for details

  SELECT CstID,

  SUM(CASE WHEN [Point Type] = 'educ min' THEN [Point Value] ELSE 0 END) AS EduPts,

  SUM(CASE WHEN [Point Type] = 'total' THEN [Point Value] ELSE 0 END) AS TTLPts

  FROM @yourtable

  GROUP BY CstID

  I also think it a bit odd that you have fully spelled out words and spaces as column names in your source table, yet you display your "report" with abbreviates. Not the most convenient thing to do in either case...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)