SQL Server 2014 :: Passing Multiple Columns Values From Query To One Variable?
Aug 10, 2014
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
[code].....
View 1 Replies
ADVERTISEMENT
Jul 31, 2015
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
View 5 Replies
View Related
Jul 30, 2015
WE have a query which pulls revenue by country and client for the last 3 years. Right now we have each year being reported in separate columns but we would like to have the revenues for each year for each client to appear on one row. Below is the current query we have setup.
SELECT
p.country_code,
p.local_client_code,
wwc.local_client_name,
case when pr.fiscal_year = 2015 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end as '2015 Revenue',
[Code] ....
View 7 Replies
View Related
Feb 17, 2015
Attempting to build a report were you can place a specific code in the parameter field and it will return all row values based on that particular code. I have a similar report that works great, but the specific code is just in 1 column, the one I'm trying to create has the potential to have that code in up to 20 different spots. I have the report built, but the issue I'm facing is linking the parameter. Is there a way to link 1 parameter to multiple column options?
Here's an example:
Docflo Distribution Group Queue Status Pend1 Pend 2 Pend 3 Pend 4 Pend 5
ABC ABC1 Catch All NEW 123 126 125 621 129
ABC ABC1 Various PENDED 621 123 872 542 630
Right now if I were to link the parameter to the Pend1 field, I would get every line I wanted that had Pend "123", but it would not include any of the lines where Pend "123" was in Pend 2, Pend 3, Pend 4, so on.
How would I link the parameter to more than 1 column so it would return all rows with a specific code no matter which Pend column it was in?
View 9 Replies
View Related
Oct 16, 2014
I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths. eg..
Input 1:
Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH
Table BIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR--> 1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN10|10|4|10
Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234
Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif then after concat the value should be like below:
12345678904355325363a234 88390234
Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH
I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...
I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..
View 2 Replies
View Related
Jun 23, 2015
I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...
In this case the best solution is to create an index on each column individually or at least the most used?
View 7 Replies
View Related
Mar 6, 2008
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
View 4 Replies
View Related
Aug 21, 2007
All:
I am trying to code the following SQL into an OLEDB data source but it is not allowing me to do so because I think the variables are nested in multiple SQL statements. I have seen other posts that suggest using a variable to store the SQL but I am not sure how it will work.
I would also like to mention that the OLEDB source executes from within a For Each loop that is actually passing the values for the variables, which was one of the reasons I got stumped on how I could have a variable store the SQL.
Here is the SQL:
select b.ProgramID, b.ProductCode, b.BuyerID, b.Vendor,sum(a.Ordered) As Qty_Pruchased
From SXE..POLine a INNER JOIN
(SELECT VIR_Program.ProgramID, VIR_ActiveSKU.ProductCode, VIR_ActiveSKU.BuyerID, Vendor
FROM VIR_Program INNER JOIN
VIR_ActiveSKU ON VIR_Program.ProgramID = VIR_ActiveSKU.ProgramID
INNER JOIN Vendor ON VIR_Program.VendorID = Vendor.VendorID
WHERE ProgramFreq=?) b
ON a.ProductCode = b.ProductCode
WHERE a.TransDate >=? AND
a.TransDate ?
Group By b.ProgramID, b.ProductCode, b.BuyerID, b.Vendor
Thanks!
View 5 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Oct 19, 2015
I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.
Currently am using the below function and getting the parameters by each in select statement as mentioned below.
select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1,
[dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2
CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex]
(
@CustomProp varchar(max),
[code]....
View 8 Replies
View Related
Aug 5, 2015
declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
---------------------
Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)
select * from #tmp
I want to convert to @var to same like #tmp table ..
"@" - delimiter goes to rows
"~" - delimiter goes to columns
View 6 Replies
View Related
Dec 11, 2013
How do I write a query using the split function for the following requirement.I have a table in the following way
Identity Name Col1 Col2 Col3
1 Test1 1,2,3 200,300,400 3,4,6
2 Test2 3,4,5 300,455,600 2,3,8
I want an output in the following format
Identity Name Col1 Col2 Col3
1 Test1 1 200 3
1 Test1 2 300 4
1 Test1 3 400 6
2 Test2 3 300 2
2 Test2 4 455 3
2 Test2 5 600 8
If you see the data, first element in col1 is matched to first element in col2 and 3 after splitting the string.
View 2 Replies
View Related
May 7, 2008
I am doing report development against OLAP (Cube). I have a parameter which is a multi-value parameter, and I need to pass this as a parameter into a sub-report (pass all the values selected in this multi-value from the main report to sub report parameter).
Is this even possible? If yes how do I achieve this.
Currenty I sent like Fields!Region.Value(0), but I want to send actually Fields!Region.Value() (meaning all selected).
Any help ASAP is appreciated.
View 3 Replies
View Related
Apr 20, 2007
Hi,
I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.
View 1 Replies
View Related
Dec 28, 2007
I have a SQL query that goes like this
"select * from Product where ProductID in (1,2,3)"
How can i create a stored procedure where a single input parameter can take multiple values?
Can anyone help me with this?
View 4 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Mar 13, 2014
I can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.
tell me if they have been successful just using standard jobs and querying against multiple servers?
If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up.
I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.
View 1 Replies
View Related
Dec 9, 2007
hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e)
{
try
{foreach (ListItem item in authorsListBox4.Items)
{if (item.Selected)
{
AddConfSqlDataSource.Insert();
}
}saveStatusLabel.Text = "Save Successfull: The above publication has been saved";
}catch (Exception ex)
{saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message;
}
}
View 3 Replies
View Related
Dec 20, 2006
Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?
For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:
MyChildPackage(var1=1, var2="foo");
MyChildPackage(var1=2, var2="bar");
MyChildPackage(var1=3, var2="baz");
View 2 Replies
View Related
Oct 16, 2007
Hello,
I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.
Thanks a lot for any feedback!
View 6 Replies
View Related
Aug 9, 2007
What is the easiest way to pass a value into a variable...
TSQL...
declare @@test int
--select count(*) from authors
select count(*) into @@test from authors
View 1 Replies
View Related
Oct 24, 2007
Hi
I am extracting data from Oracle via SSIS. There are three smilar schemas from where the data has to be extracted.
Say My query is " Select * from abc.dept" where abc is the schema name. I want to pass this schema name through a variable and it should loop as there are total 3 schemas. There is a table which provided list of schemas.
Can somebody please guide me how to do this. There are multiple references of this table is SSIS package.
View 11 Replies
View Related
Jul 21, 2015
CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);
[Code] ....
Now I have to pass multiple param values to it. I was trying this but didnt get any success
exec
[SP_test]'LOC','LOP'
View 10 Replies
View Related
Nov 2, 2007
Hi all,
I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.
The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.
View 3 Replies
View Related
Aug 5, 2015
I want to know if it is possible to do the following;
I have patients that may have been transferred to different locations(see below)
location_name enter_time
4D04 2/9/15 2:35
4D14 2/9/15 8:44
RECOVERY 3 2/9/15 9:08
4D13 2/9/15 17:36
4D14 2/10/15 2:02
i know i can do a min max to get my first and last values. I want to label the columns something like
1st location, 2nd location, 3rd location, 4th location, discharge location.
there could be 1 location or 20.
is there a way to do this?
i can do a temporary table and then an update query to add the values to those columns.
just not sure how to get the next value and then the next etc.
View 9 Replies
View Related
Mar 28, 2007
Hi everyone I am new to this site I have a major issue I cant figure out seeing how im fairly new to asp, and vb, but i have 5 years php mysql experience.
Im pulling the correct data into a grid. Then i need to make a button or some sort of link that will take the value of one field in the record set and replace it with @transid in the where statement I can enter in the value of transid into form field with that name and it will run the rest of the script correctly, I just cant get past this hurdle. If anyone can help that would be great. I tried to get this to work with java script but then realized thats not possible to transfer varaibles to asp from it.
///javascript
function DisplayReciept(transactionnum)
{
recieptdis = transactionnum;
}
////field in grid
<asp:BoundField htmlEncode=false DataFormatString="<a href=javascript:DisplayReciept{0}>Display</a>" DataField="transid" HeaderText="Show Reciept" SortExpression="transid" />
//////////////query//////////////
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dbsgcConnectionString %>"
SelectCommand="SELECT [fulldata] FROM [data] WHERE ([transid] = @transid)">
<SelectParameters>
<asp:FormParameter FormField="transid" Name="transid" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
View 1 Replies
View Related
May 6, 2015
I am using a sql task to get all tablenames and then passing the output to another sql task inside a for each container.
So that the 2nd sql task will be executed for each table. My query looks like SELECT DISTINCT b.EmailAddress FROMÂ ? ......
Since I am passing the tablename as a variable (output from the 1st sql task), I get the following error:
[Task Execute SQL] Error:
Failed to execute the query 'SELECT DISTINCT b.EmailAddress FROM? AS a INNER... ':' Failed to extract
the result in a variable of type (DBTYPE_I4)'. Possible causes include the following: Problems with the query, not properly fixed ResultSet property, not properly set parameters or not properly established connection.
how to pass a tablename as a variable to a query?
View 5 Replies
View Related
May 23, 2006
Hi,
The values I need to store in the table are
Student ID
Student Name
Subjects
The "Student ID" is the primary key.
A student can take more than 1 subject.
For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math
How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?
My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.
Or I can create a new field called "RowNumber" and keep that the primary key..
For example:
Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math
If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?
View 5 Replies
View Related
May 26, 2015
Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
View 4 Replies
View Related
May 22, 2015
I have defined a variable Var_Query_SQL and passed the below query using expression but it is showing error. where am i going wrong.
"SELECT
      sample_id ,
      sample_time ,
      trans_date ,
      product = mh.[identity] ,
comments = s.m_smp_comment
[URL] ...
View 4 Replies
View Related
May 11, 2007
Hello,
I responded to a very old discussion thread & afraid I buried it too deep.
I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)
but I have not gotten all the ''''' + @variable syntax right.
Here is my raw openrowset with what I am aiming at.
Code Snippet
-- I want to use some kind of variable, like this to use in the file:
DECLARE @FIL VARCHAR(65)
SET @FIL = 'C:company foldersDocumentationINVENTORY.xls;'
--
SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:company foldersDocumentationINVENTORY.xls;', 'SELECT * FROM [Inventory$]')
AS DT
Anyone game? Many thank-yous, in advance.
Kind Regards,
Claudia.
View 1 Replies
View Related
Nov 6, 2014
I am trying to filter my columns on multiple values. I need them all to be OR because I want it to look through all of the columns and wherever the value matches to not include in the view. My WHERE clause that I thought would work looked like this..
WHERE (NOT (RTPL_VOLUME_DATA_1.SYMBOL LIKE '%spot%')) AND (NOT (RTPL_VOLUME_DATA_1.ISSPREAD = 'True')) AND
(NOT (RTPL_VOLUME_DATA_1.GMIPRODUCTCODE = 'Internal')) AND (RTPL_VOLUME_DATA_1.TAG_COMMENT IN ('[]', '[Gscalp]', '[TT]', '[GX2]', '[NA]', '[STELLAR]')) OR
(RTPL_VOLUME_DATA_1.TAG_COMMENT IS NULL)
However this does not work and provide the data needed. I then thought that if I replaced all the AND's with OR's that would work, but here it does not filter anything.. not sure where to go from here.
View 3 Replies
View Related
Jun 9, 2006
I am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:
Key
NotANormalizedCol
1
1324, 5124, 5435,5467
2
423, 23, 5345
3
52334, 53443, 1224
4
12, 4, 1243,66
is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.
I am pretty new to this, so I apologize if this is a trivial questions
View 3 Replies
View Related
