Store each row value in a separate variable in one statement

• pietlinden

  SSC Guru

  Points: 63621

  More actions

  November 13, 2017 at 6:29 pm

  #1967886

  Sounds like you need this article: Crosstabs and Pivots by Jeff Moden...

• markavis.mail

  SSC Enthusiast

  Points: 106

  More actions

  November 13, 2017 at 11:13 pm

  #1967892

  I had a look at the link provided and got a clue.

  
DECLARE @SlowLimit INT, @MediumLimit INT, @ExpressLimit INT, @DevLimit INT

      SELECT
         @SlowLimit = SUM(CASE WHEN processthreadtype = 'SLOW' THEN ProcessLimit ELSE 0 END)
        ,@ExpressLimit = SUM(CASE WHEN processthreadtype = 'EXPRESS' THEN ProcessLimit ELSE 0 END)
        ,@DevLimit = SUM(CASE WHEN processthreadtype = 'DEV' THEN ProcessLimit ELSE 0 END)
        ,@MediumLimit = SUM(CASE WHEN processthreadtype = 'MEDIUM' THEN ProcessLimit ELSE 0 END)
    FROM ProcessThreadTypeLimits

  SELECT @SlowLimit AS '@SlowLimit' , @MediumLimit AS '@MediumLimit ', @ExpressLimit AS '@ExpressLimit ', @DevLimit AS '@DevLimit';


  
  Or as a row.
  
SELECT 
         SUM(CASE WHEN processthreadtype = 'SLOW' THEN ProcessLimit ELSE 0 END) as 'SLOW'
        ,SUM(CASE WHEN processthreadtype = 'EXPRESS' THEN ProcessLimit ELSE 0 END) as 'EXPRESS'
        ,SUM(CASE WHEN processthreadtype = 'DEV' THEN ProcessLimit ELSE 0 END) as 'DEV'
        ,SUM(CASE WHEN processthreadtype = 'MEDIUM' THEN ProcessLimit ELSE 0 END) as 'MEDIUM'
    FROM ProcessThreadTypeLimits

  
  

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  November 14, 2017 at 9:01 am

  #1968004

  Maybe this:
  SELECT
   MAX(CASE WHEN processthreadtype = 'SLOW' THEN ProcessLimit ELSE NULL END) AS [@Slow]
   ,MAX(CASE WHEN processthreadtype = 'EXPRESS' THEN ProcessLimit ELSE NULL END) AS [@Express]
   ,MAX(CASE WHEN processthreadtype = 'DEV' THEN ProcessLimit ELSE NULL END) AS [@Dev]
   ,MAX(CASE WHEN processthreadtype = 'MEDIUM' THEN ProcessLimit ELSE NULL END) AS [@Medium]
  FROM ProcessThreadTypeLimits;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)