May 16, 2003 at 1:53 pm
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.
May 16, 2003 at 2:49 pm
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
May 16, 2003 at 2:51 pm
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.
May 16, 2003 at 2:59 pm
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.
May 16, 2003 at 3:31 pm
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
May 16, 2003 at 3:35 pm
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)
May 17, 2003 at 2:57 pm
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.
May 19, 2003 at 4:27 am
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.
May 19, 2003 at 7:47 am
Thank you so much amelvin, and Antares686. That worked.
Thanks to rdfozz, and rbinnington for your response.
May 20, 2003 at 9:18 am
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
May 22, 2003 at 2:45 am
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)?
May 22, 2003 at 1:01 pm
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