Store each row value in a separate variable in one statement

• markavis.mail

  SSC Journeyman

  Points: 86

  More actions

  November 13, 2017 at 5:50 pm

  #340444

  I am having trouble working out how get each separate ProcessLimit into its own variable without 4 SELECTS, if easily possible.
  I have tried Self Joining and PIVOTing but either this is not the correct way or I was doing it wrong.
  Does anybody have an "easy" way? .. thanks

  Initial Table
  
  I am trying to get each ProcessThreadThreadType limit stored in its own variable.

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

  

  CREATE TABLE [dbo].[ProcessThreadTypeLimits](
    [ThreadLimitID] [int] IDENTITY(1,1) NOT NULL,
    [ProcessThreadType] [varchar](50) NULL,
    [ProcessLimit] [smallint] NULL
) ON [PRIMARY]
GO

  SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

  SET IDENTITY_INSERT [dbo].[ProcessThreadTypeLimits] ON;

  BEGIN TRANSACTION;
INSERT INTO [dbo].[ProcessThreadTypeLimits]([ThreadLimitID], [ProcessThreadType], [ProcessLimit])
SELECT 1, N'SLOW', 3 UNION ALL
SELECT 2, N'EXPRESS', 5 UNION ALL
SELECT 3, N'DEV', 1 UNION ALL
SELECT 4, N'MEDIUM', 5
COMMIT;
RAISERROR (N'[dbo].[ProcessThreadTypeLimits]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

  SET IDENTITY_INSERT [dbo].[ProcessThreadTypeLimits] OFF;

  go

  select * from [ProcessThreadTypeLimits]


• pietlinden

  SSC Guru

  Points: 63428

  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 Journeyman

  Points: 86

  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: 110639

  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)