Dynamic Name Of Result Column In S-proc?
Oct 12, 2004
How can I make this stored procedure concept work:
-----------------------------------------------
Create Procedure MyProc @MyValue varchar(5)
As
Declare @ColumnName as varchar(11)
Set @ColumnName = 'Price_' + @MyValue
Select Sum(Price) As @ColumnName --????
From MyTable where Item = @MyValue
Return
-----------------------------------------------
Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
Bjorn
View 2 Replies
ADVERTISEMENT
Mar 24, 2007
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
View 10 Replies
View Related
Oct 25, 2007
Is there an example of how to execute a stored proc using the php ctp driver? In the following code, I get an error in the sqlsrv_stmt_fetch_array method. The error is [Microsoft][SQL Native Client]Invalid cursor state.
$proc = "EXECUTE dbo.spGetId $table";
$stmt = sqlsrv_conn_execute($connection, $proc);
if ( !$stmt )
{
$error = sqlsrv_errors();
catchError(3, $error[0]['message'], __FILE__, __LINE__);
}
$row = sqlsrv_stmt_fetch_array($stmt);
if ( !$row )
{
$error = sqlsrv_errors();
catchError(3, $error[0]['message'], __FILE__, __LINE__);
}
When I execute the stored proc in query analyzer, I get 1 record with 1 column.
I'm looking forward to using this driver. This is the first issue I've come across. Similarly, is there a way to get output parameter values from a stored proc?
Thanks,
Greg
View 10 Replies
View Related
Aug 29, 2006
I hope I can express what I need to know.Here it goes. I am planning on writing a single stored procedure to include two select statements and followed by a third like so:Select * etcUNION ALLSelect * etcGOSelect something elseMy question is this: does this return one record set (returning all records from the three Select statements) or two, the first being the result from the UNION and the second from the select following the GO. I will need to access the data returned from the resultant UNION and from the select after the Go, so I wonder if I'll be dealing with a single dataset or two. I hope this made sense.
View 3 Replies
View Related
Jul 20, 2005
In SQL how can a cursor be opened to iterate the result set returnedfrom a stored proc-Rahul SoodJoin Bytes!
View 1 Replies
View Related
Jul 20, 2005
I'm trying to write a SQL that stores a result from a stored-procedureinto a temporary table.Is there any way of doing this?
View 3 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Jan 3, 2008
I have a SP that returns a different dataset based on different id(parameter).
So that I can NOT get fields in the Report Builder.
Is there a way to have a "generic" Report that displays whatever it gets from the SP.
In a simple "table" format.
If not, could you, please, get me some suggestions how to address this problem.
One idea that I have in mind is to create Report Definitions dynamically...
I'd rather not to go there.. but..
Thank you very much!
View 1 Replies
View Related
Nov 22, 2004
Please help, I am trying to write a dynamic search stored procedure using three fields.
I use the same logic on the front end using VB to SQL server which works fine, but never tried a stored procedure with the logic.
Can you please help me, construct a stored procedure.
User can choose any of the three(progno, projno, contractno) fields as a where condition.
I ma using asp.net as front end with sql server backend.
CREATE PROCEDURE dbo.USP_Searchrecords
(@ProgNO nvarchar(50),
@ProjNOnvarchar(50) ,
@ContractNOnvarchar(50))
AS
DECLARE @myselect nvarchar(2000)
DECLARE @psql nvarchar(2000)
DECLARE @strsql nvarchar(2000)
SET NOCOUNT ON
@psql = "SELECT * FROM Mytable"
IF @ProgNO <> '' then
strsql = WHERE ProgNO = @ProgNO
end if
If @ProjNO <> '' then
if strsql <> '' then
strsql = strsql & " and ProjNO =@ProjNO
ELSE
strsql = wHERE ProjNO =@ProjNO
END IF
END IF
If @ContractNO <> '' then
if strsql <> '' then
strsql = strsql & " and ContractNO =@ContractNO
ELSE
strsql = wHERE ContractNO =@ContractNO
END IF
END IF
@myselect = @psql + @strsql
EXEC(@myselect)
Please help. Thank you very much.
View 3 Replies
View Related
Sep 28, 2007
I have a table with 52 columns named 'Week1', 'Week2' etc. with values 1, 0 etc. I want to check values in each column. I have following lines in my procedure.
Declare @l_str varchar(50),
@l_count int
Select @l_count = 1
Select @l_str = 'Week' + Convert(varchar, @l_count)
Now how do I compare the value stored in the @l_str which should be wither 0 or 1 and not 'Week1'?
Is there any better method to compare read these 52 table variables?
Thanks in advance
View 3 Replies
View Related
Jul 20, 2005
I am trying to run 3 dynamic selects from stored proc, really onlythe table name is dynamic.. Anway I'm kinda lost on how I canaccomplish this.. this is what I have but it only returns the firstresult.. that being basicCREATE PROCEDURE email_complexity@TableName VarChar(100)ASDeclare @SQL VarChar(1000)Declare @SQL1 VarChar(1000)Set nocount onSELECT @SQL = 'SELECT Count(complexity) AS basic FROM 'SELECT @SQL = @SQL + @TableNameSELECT @SQL = @SQL + ' WHERE len(complexity) = 5'Exec ( @SQL)SELECT @SQL1 = 'SELECT Count(complexity) AS moderate FROM 'SELECT @SQL1 = @SQL1 + @TableNameSELECT @SQL1 = @SQL1 + ' WHERE len(complexity) = 8'Exec ( @SQL1)ReturnIs there a better way of doing this??tiaDave
View 2 Replies
View Related
May 7, 2008
Hi,
I'm looking for some sample code rather than having to re-invent the wheel.
I need to write an analysis services stored proc that will invoke a SQL stored prod in my DW dataabse to retrieve a list of client_id's. I then need to construct and return a set object. This AS stored proc will be referenced from a role.
I've read few things about using the Set object rather than StrToSet function but then the only way I see to create a Member orSet object from a literal is to build an Expresion object and call the CalculateMdxObject(null).ToSet() function. Is this not equivalent to MDX.StrToSet()?
Also, ideally I'd like to connect to my SQL db by accessing the connection string from the Data Source objects in my AS DB. So far I have not found a way to do this.
Some guidance and sample code would be much appreciated.
Thanks.
View 8 Replies
View Related
Dec 13, 2007
Is this possible as given below
declare @Qry as varchar(8000)
declare @Cnt as int
begin
set @Qry = 'select @Cnt=count(*) from ' + @TableName
exec @Qry
select @Cnt
end
But its not working....
can any one help me out in this.....
Thnx
Parag
View 1 Replies
View Related
Oct 14, 2004
Hi all,
I'm gonna need some help with this one.
I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:
SELECTN_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROMdbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ...............and so on
What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.
So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this??? Help!!
Thanks
View 2 Replies
View Related
Oct 5, 2006
im running a dynamic query with open rowset in it
pseudocode:
@CMD=declare @ RETURN SELECT @RETURN =SUM(X) FROM OPENROWSET(....) SELECT @RETURN
EXEC @CMD
This pseudocode dipplay the result of @return
the problem:
capture @return into @myvalue outside the dynamic sql scope
something like
Select @myvalue=exec(@cmd)
I don't wanna run on ditributed transaction like this
insert mytable
exec(@cmd)
thanks,
joey
View 7 Replies
View Related
Apr 17, 2007
Created a stored procedure which returns Selected table from database.
I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure
(
@condition varchar(20),
@ID bigint,
@date1 as datetime,
@date2 as datetime
)
AS
/* SET NOCOUNT ON */
IF @condition LIKE 'all'
SELECT CllientEventDetails.*
FROM CllientEventDetails
WHERE (ClientID = @ID)
IF @condition LIKE 'current_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom <= ISNULL(@date1, EventFrom)) AND
(EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE ''
SELECT CllientEventDetails.*
FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
View 2 Replies
View Related
Dec 19, 2000
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP
View 4 Replies
View Related
Jul 9, 2004
Hi:
Is there a way to assign a dynamic query result to a local variable?
declre @sqlString nvarchar(4000),
@minEventDate datetime,
@databaseName varchar(25)
selct @databaseName = 'customer_12345'
(actually, a cursor loop will assign the database name dynamically, here just to simplify the situation)
select @sqlString =
'select ' + @minEventDate + '= (select min(eventDate) from ' + @databaseName + '.dbo.tblABC)'
exec sql_executesql @sqlString
print '@minEventDate: ' + cast(@minEventDate as varchar(19))
Though the select min(eventDate) from customer_12345.dbo.tblABC
returns a date, ex. '02/01/2004 12:35 pm', however, the printed @minEventDate is always with Null value. It mean, the value was never correctly assigned to the local variable.
As an alternate way, I am using temp table to insert it with the query result and then assign to the local variable. Since I have many local variables to try to get the min, max, count for around 10 fields, perfer a way to direct assign to the local variable instead of 10 temp tables.
thanks
-D
View 4 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
Feb 16, 2005
Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter.
-----------------------------
declare @stringSQL varchar(200)
select @stringSQL=
'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0'
print @stringSQL_GetUserName
exec (@stringSQL_GetUserName)
select * from temp1
------------------------------
above dynamic query works fine.
Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:
1. when ran at a MSDE server
MSDTC on server is unavailable.
2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.
Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang...
Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?
thanks
David
View 4 Replies
View Related
Sep 17, 2007
I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
View 1 Replies
View Related
Aug 30, 2007
Hello all:
Here is a sample query:
DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'
DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'
DECLARE @KeyCol NVARCHAR(50);
SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR (1000)
SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +
' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '
The value resulting from Executing @KeyValue is an integer.
I want to store this value in a new variable say @VAR2
When I do this
DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue
its giving me an error.
can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?
View 3 Replies
View Related
Oct 3, 2006
Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below. Any clues on how to fix this? Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: }
Line 1445: try {
Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();
Line 1447: return returnValue;
Line 1448: }
View 3 Replies
View Related
Feb 27, 2008
Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.
View 9 Replies
View Related
Jan 17, 2008
Hi
I need help to build the below data to my requirement
i have table having the following data's
NoofLive
Action
Sold
Ratio
EffDt
10-49
43
0
0
1/1/2008
50-99
62
0
0
1/1/2008
100-199
73
0
0
1/1/2008
200-299
17
1
0.059
1/1/2008
300-499
25
0
0
1/1/2008
500-999
21
0
0
1/1/2008
1000+
45
0
0
1/1/2008
100-199
11
1
0.091
2/1/2008
1000+
1
0
0
2/1/2008
100-199
17
0
0
3/1/2008
500-999
2
0
0
3/1/2008
EffDt = (Jan-08,Feb-08,Mar-08)
I want creat result as follows from the above data's
Jan-08
Feb-08
Mar-08
Eligible Lives
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
10 - 49
43
0
0
0
0
0
0
0
0
50 - 99
62
0
0
0
0
0
0
0
0
100 - 199
73
0
0
11
0
0
0
0
0
200 - 299
17
1
0.059
0
0
0
17
0
0
300 - 499
25
0
0
0
0
0
0
0
0
500 - 999
21
0
0
0
0
0
0
0
0
1000+
45
0
0
1
0
0
2
0
0
Please anybody can help build a query to generate the above result.
Thanks in advance
sthanika
View 1 Replies
View Related
Feb 5, 2004
I have a brain teaser for you all.
The end result: I need one of the columns in my result set (col2) to have a preceeding apostrophe (i.e., '001234 ).
The problem: I am building my query as a string and then using the EXEC() function to execute it. For example:
SET @strSQL = 'SELECT col1, col2 FROM mytable'
EXEC(@strSQL)
Just to tame any answers that might not be directed to the specific question, the query Must be built as a string and then executed.
I have been unable to obtain the solution and I am wondering if it is even possible?
TIA
View 3 Replies
View Related
Jan 11, 2005
Simple example would look like that in MS SQL
SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)
So that the display is
English
a
as we assume that
SELECT language_name FROM language WHERE language_id = 1
returns only English
I have tried to that with a variable but it does not work
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that
LOL
I just tried another way as i was going to post
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"
and it worked!
Posting anyway so people might answer with a better solution with no variable
Thanks a lot
Mordan
View 1 Replies
View Related
May 7, 2007
Hi,
I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.
Thanks
View 9 Replies
View Related
Sep 7, 2006
I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance
View 2 Replies
View Related
Feb 7, 2006
I am trying to display component usage in January for the past year, if I want to display the year in different column, what should I do?
eg.
component_id component_description qty_used_on_2005_Jan qty_used_on_2006_Jan
C58B0BDD tape drive 2 3
Currently I am using this sql:
select cast(year(date_complete) as varchar(10)) + ' Jan' as Year, component_id, component_description,sum(component_qty) as total_qty_used
from view_jobComponent
where month(date_complete) = 1group by component_id, component_description, cast(year(date_complete) as varchar(10)) + ' Jan'order by component_id, component_description
which I will get something like this:
Year component_id component_description total_qty_used
2005 Jan C58B0BDD tape drive 22006 Jan C58B0BDD tape drive 3
View 9 Replies
View Related
Jun 8, 2008
Last query, mr. visakh help to produce a result as follow
Date_Taken | Main_ID | Daily_Rainfall
-------------------------------------------
5/1/2005 | 194 | 142
6/1/2005 | 194 | 155
7/1/2005 | 194 | 132
8/1/2005 | 194 | 199
5/1/2005 | 196 | 333
6/1/2005 | 196 | 544
7/1/2005 | 196 | 722
8/1/2005 | 196 | 845
...
...
*Combination of Date_Taken and Main_ID generate unique row
*Above data consist of 1 May 2005 to 1 AUG 2005 data (4 month data for each Main_ID), which is Date_Taken start from 1st day every month.
*Date_Taken | Main_ID | Daily_Rainfall
-------------------------------------------
5/1/2005 | 194 | 142
---> Mean, In May 2005 Main_ID=194 having 142 Daily_Rainfall
6/1/2005 | 196 | 544
---> Mean, In Jun 2005 Main_ID=196 having 544 Daily_Rainfall
Let's say, current month is 8
I stuck to do the query to display result like as follow
Date_Taken | Main_ID | CurrentMonth_DR | LastMonth_DR | Last2Month_DR| Last3Month_DR
--------------------------------------------------------------------------------------
8/1/2005 | 194 | 199 | 132 | 155 | 142
8/1/2005 | 196 | 845 | 722 | 544 | 333
...
...
*CurrentMonth_DR is 8/1/2005, LastMonth_DR is 7/1/2005, Last2Month_DR is 6/1/2005 and so on ( this can be harcoded in sql)
Plz help me
View 1 Replies
View Related
Jul 12, 2013
Is there a way to ignore a column/variable when the whole result set for the applied filter (where) is null?
Not all clients have data for every variable, i.e. some variables are client specific. There are too many variables and clients to amend the select query every time so I just want to ignore a col if its null.
I hope that makes sense (my inability to describe it might explain my inability to find anything related to it!)
The next step would be to run all clients' data in one go using, I believe, a cursor, but one step at a time!
View 7 Replies
View Related
Jan 17, 2014
I have a clustered index in a table. this column is of datatype date. how can i retrieve the following?:
select [date], valueColumn from myTable
where [date] = '2000-01-03' and
('2000-01-03'+1) and
('2000-01-03'+2)
My Goal ist to retrieve 3 values of valueColumn of 3 subsequent days, calculate the average of this 3 values and insert this average in a third colum called [average3days].
View 12 Replies
View Related
