dynamic Case

  • My sample query is like this

    SELECT col1,col2,col3,

    SUM(CASE col4 WHEN 'EMP' THEN col5 ELSE 0 END) AS EMP from sample

    group by col1,col2,col3

    Is there any way that I can use When condition dynamically in the above query.

    Thanks much in advance.

  • Are you meaning you want to do something like this.

    SELECT col1,col2,col3,

    SUM(CASE WHEN col4 = @val THEN col5 ELSE 0 END) AS EMP from sample

    group by col1,col2,col3

  • YES, BUT 'AS' ALSO SHOULD @val AND i SHOULD LOOP THROUGH THE LIST OF VALUES FOR VAL IN THE SAME SELECT STATEMENT.

    IS THAT POSSIBLE?

  • Well, this would give you the sums on column 5 for all values of col 4:

    SELECT col1,col2,col3,col4,sum(col5) as [Sum for column 4]

    FROM sample

    group by col1,col2,col3,col4

    This sounds close to what you want, at least....

    RD Francis


    R David Francis

  • Let me be more clear.

    The query was like this

    SELECT col1,col2,col3,

    SUM(CASE col4 WHEN 'first' THEN col5 ELSE 0 END) AS first,

    SUM(CASE col4 WHEN 'second' THEN col5 ELSE 0 END) AS second,

    SUM(CASE col4 WHEN 'third' THEN col5 ELSE 0 END) AS third

    from sample

    group by col1,col2,col3

    So basically col4 has values -'first','second','third' ,.........

    I want to modify the query in such a way that I need not mention values for Col4, I just want to select sum for all the distinct values that are in col4.

    I am trying to display data horizontally, that is stored vertically in the table

    Thanks.

  • I do not want to group by col4.

    Actually I will get multiple col4 values for the same value in col1. we can assume col1 as person name, and col4 as his/her requests, col5 as cost for the request

    So I want to list all the requests made by that person in one row.

    Thanks.

  • I don't know if this is what you are trying to do, but you said you wanted to display horizontally what is vertically in a table

    I got this elsewhere on this site but you can stick this function into your select and get a comma-delimeted list of your information in col4.

    select col1, col2, col3, GetCol4(col1, col2, col3)

    from sample

    group by col1, col2, col3

    
    
    Create FUNCTION GetCol4 (@col1 datatype, @col2 datatype, @col3 datatype)
    RETURNS varchar(5000)
    AS
    BEGIN

    DECLARE @Cols varchar(5000)
    SET @Cols = ''


    SELECT @Cols =
    CASE @Cols
    WHEN '' THEN col4
    ELSE @Cols + ', ' + col4
    END
    FROM sample

    RETURN(@Cols)

    Hope this helps,

    Richard

  • I am sorry the code should be:

    
    
    Create FUNCTION GetCol4 (@col1 datatype, @col2 datatype, @col3 datatype)RETURNS varchar(5000)AS
    BEGIN
    DECLARE @Cols varchar(5000)
    SET @Cols = ''

    SELECT @Cols =
    CASE @Cols
    WHEN '' THEN col4
    ELSE @Cols + ', ' + col4
    END
    FROM sample
    where col1 = @col1
    and col2 = @col2
    and col3 = @col3

    RETURN(@Cols)
  • If you are going to set @val = to say

    SET @val = '1,2,3'

    then you might consider an SP to strip the values to either a temp table or table data type so you can do

    SUM(CASE WHEN col4 IN (SELECT vals FROM temptable) THEN col5 ELSE 0 END)

    Or wrap the whole thing in dynalic SQL so you can build a proper SQL string to use in an sp_executesql or EXECUTE method.

    And you might try something dynamic with submitting both you @val value and the value of col5 to see if value 5 is in you @val and return a true value to your case.

  • I think I understand this one, you want the names to appear as the column names and the values to appear under them - ie a cross-tab report- and you want as many columns as there are names.

    As Antares686 suggested you could do something like this...

    set nocount on

    create table #startTable (col1 varchar(10), col2 varchar(10), col3 varchar(10), myName varchar(50), myValue int)

    create table #tempVals (tempVals_id int identity (1,1), myName varchar(50))

    insert into #startTable values ('col11','col21','col31','Luke',20)

    insert into #startTable values ('col12','col22','col32','Mark',49)

    insert into #startTable values ('col13','col23','col33','John',22)

    insert into #startTable values ('col14','col24','col34','Mark',11)

    insert into #startTable values ('col15','col25','col35','Luke',17)

    insert into #startTable values ('col16','col26','col36','Luke',12)

    insert into #tempVals

    select distinct [myName] from #startTable

    set nocount off

    declare @intCols int

    declare @intCount int

    declare @strSQL nvarchar(4000)

    declare @thisName varchar(100)

    select @intCols=(select count(*) from #tempVals)

    set @intCount=0

    set @strSQL=N'select '

    while @intCount<@intCols

    begin

    set @intCount=@intCount+1

    select @thisName=(select myName from #tempVals where tempVals_id=@intCount)

    select @strSQL=@strSQL+ 'sum(case st.myName when ''' + @thisName + ''' then st.myValue else 0 end) as ''' + @thisName + ''''

    if (@intCount<@intCols) set @strSQL=@strSQL+','

    end

    set @strSQL=@strSQL+' from #startTable st'

    exec sp_executesql @strSQL

    drop table #tempVals

    drop table #startTable

    This would give you a result like

    John Luke Mark

    ----------- ----------- -----------

    22 49 60

    (1 row(s) affected)

    ...

    looking at the preview some of the plusses and apostrophes were stripped out but I think you will understand anyway.

  • Thank you so much amelvin, and Antares686. That worked.

    Thanks to rdfozz, and rbinnington for your response.

  • amelvin, thanks for the great example. I'm a real beginner with CASE, but this is just what I need to do. I also need to put it into a cursor for printing. Would you mind inserting the code for that? I'm not sure which local variables I need to define, or exactly where to put them.

    Many thanks,

    ikf

  • ikf

    I don't understand your post. Do you want a print-out of the code, or examples of cursors (there are lots on this site)?

  • I need to know how to put your code into a cursor that the application program will process to print a report. My report calculates the run-times of a series of reports that are supposed to be manually executed. Here is what I have so far:

    <<

    CREATE TABLE #temp

    ( mnemonic char(6) NULL,

    start_dtime smalldatetime NULL,

    end_dtime smalldatetime NULL

    )

    INSERT #temp

    SELECT mnemonic, start_dtime, end_dtime

    FROM document_audit

    WHERE start_dtime between @p1_dtime and @p2_dtime

    and mnemonic in ('MLFV1A', 'MLFV1B', 'MOFV1A', ... 'MOFV4B')

    --there are 10 in all

    --Build cursor to print results

    --This is how I would do it for any other report, but it did not work for a CROSS-TAB:

    DECLARE @rundate smalldatetime,

    @MLFV1A char(6),

    @MLFV1B char(6),

    @MOFV1A char(6),

    ...

    @MOFV4B char(6)

    DECLARE C1 CURSOR FOR

    SELECT start_dtime,

    MLFV1A = CASE mnemonic WHEN 'MLFV1A' THEN convert(char(8),(datediff(mi,start_dtime,end_dtime)),10) ELSE '' END,

    MLFV1B = CASE mnemonic WHEN 'MLFV1B' THEN convert(char(8),(datediff(mi,start_dtime,end_dtime)),10) ELSE '' END,

    --select all 10 this same way

    FROM #temp

    for read only

    open C1

    >>

    <WHILE C1 into @rundate, @MLFV1A, @MLFV1B ... @MOFV4B>

    [@rundate] [@MLFV1A] [@MLFV1B]...[@MOFV4B]

    <END WHILE>

    <<close C1>>

    <<deallocate cursor C1>>

    I would really appreciate your help on this.

    ikf

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply