SQL Server 2012 :: Return Query With Concatenating Values
Nov 22, 2013
I have two tables I am working with, they are "Institutions" and "InstitutionOversights". The relationship is one-to-many.
The sample data is below.
Table one:
InstitutionID, InstName
------------------------
1 School Alpha
2 School Beta
3 School Charlie
4 School Delta
Table two:
InstitutionOversightID, InstitutionID, Type
------------------------------------------------
1 1 Accreditation
2 1 Verifcation
3 1 Old System
I would like a query to return the results in the following format:
InstitutionID, InstName, TypeList
-----------------------------------------------
1 School Alpha Accreditation, Verification, Old System
2 School Beta null
3 School Charlie null
4 School Delta null
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
( select min(cast(f.valuestr as float)) from bvfindings f where f.ObjectName = 'Hematocrit' and f.sessionid = s.sessionid and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)), f.valuetime from bvfindings f where f.ObjectName = 'Hb'
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.
SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME) ,d.fmc_prodlnk, d. fmc_cost INTO #TestTable FROM ( SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year], T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run) , (SELECT SUM(Stores) FROM #ABC WHERE [Year] = T.[Year] AND [Month] = T.[Month]) AS [Sum_Stores], (SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
I want the following query to return a row even when table 'X' is empty. How would I do this? SELECT TOP 1 @Var1, @Var2, @Var3 from X The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included. When table is empty, it reurn nothing. It only return a row when table is not empty.
I have a table. In the table I have different types of mailingaddresses. The addresses are linked by and ID. ex,addresses per ID 1ID address1 add21 add31 add4distinct Addresses in tableID address1 add21 add31 add4What I am trying to figure out is if this is possible. Is it possibleto figure out some how with a query that in the above example ID 1is missing add1. I am trying to populate a dropdown list in my codewith just the addresses that are left from the distinct list. I trieddoing an intersect something like this but that did not seem to work.SELECT typeFROM address_demoINTERSECTSELECT typeFROM address_demo AS address_demo_1WHERE progid = '12954892'I am wondering if I am moving in the right direction or if this is evenpossible......Thanks
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing( DepDate datetime, val int) insert into testing values ('2014-01-10 00:00:00.000', 1) insert into testing values ('2014-05-19 00:00:00.000', 10) insert into testing values ('2014-08-15 00:00:00.000', 20) insert into testing values ('2014-11-20 00:00:00.000', 30)
This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.
Here is my current SELECT statement:
SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name, m.JobTitle, m.Company, m.Department, m.Address, m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ, m.WorkPhone, m.FAX, m.Email, c.Chapter, m.Active, s.Sector, i.Industry FROM tblMembers m LEFT OUTER JOIN tblChapters c ON m.ChapterID = c.ChapterID LEFT OUTER JOIN tblSectors s ON m.SectorID = s.SectorID LEFT OUTER JOIN tblIndustries i ON m.IndustryID = i.IndustryID WHERE m.DRGInclude = 1
My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.
The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.
Can someone tell me what I am missing? Is there a function that I can use for this?
Hi, I need a sql that returns the query result as comma seperated list of values, instead of several rows. Below is the scenario... Table Name - Customer Columns - CustomerID, Join DateSay below is the data of Customer table ...CustomerID JoinDate1 04/01/20052 01/03/20033 06/02/20044 01/05/20025 09/07/2005Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()This gives the below result as two rows.CustomerID15But i need to get the result as '1,5' (comma seperated list of resulting values).Any help is highly appreciatedThanks in AdvanceRamesh
I'm puzzled as to how to express what I want in a stored procedure. Assume two columns, Surname and GivenName. The surname might be missing. When I originally wrote this app in Access, I used the following expression:
SELECT Iif( IsNull(Surname), GivenName, Surname + ", " + GivenName ) AS Agent FROM Agents;
I've looked at the syntax for CASE but I can't figure out exactly how to say what I intend, particularly the AS Agent column aliasing.
Any help greatly appreciated. Please cc me privately so I receive your assistance at once!
I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!
I have row group created here on Due Month & Sales Region & added total after Due Month. When a user clicks on particular amount I would like to send the Sales Region value to the new report as Parameter. So Instead of Total text , I would like to have all the sales regions concatenated and sent to the new report. writing an expression to get the report parameter? I have added the picture of how I want the values to be concatenated , instead of Total . Is this not possible in SSRS?
Hello. I'm trying to reduce some code in my stored procedure and I'm running into lots of errors. I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial. I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw). I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this. So far I've tried the following with many errors.WITH Users AS ( SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC') WHEN @OrderBy='FirstName' THEN FirstName WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC') WHEN @OrderBy='LastName' THEN LastName END ) AS Row, UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM vRF_Users )
SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM Users WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex
I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc. I also tried: DECLARE @OrderByDirection varchar(32) DECLARE @DESC varchar(4) SET @DESC = ' DESC'
IF @Direction = 'DESC' BEGIN SET @OrderByDirection = (@OrderBy + @DESC) END And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection ELSE @OrderBy ENDObviously this didn't work either. Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |... Orders -> id | idCustomer | date | ... Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total FROM Products INNER JOIN Orders ON Orders.id = Products.idOrder INNER JOIN Customers ON Customers.id = Orders.idCustomer WHERE year(date)=2014 GROUP BY customers.name ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015) -------- customer A | 1000$ | 2000$ | 3000$ customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
I'm wanting to concatenate something that doesnt exist to an already existing field.
What I need to do is add a generic email address to every record in a data view So, what it would do is take the column that does exist and add @domain.com to another column that I would call username So rather than it just appearing as 911234 it would be a derived column saying 911234@domain.com
i've tried using +''+ but I get a space after the number.
hi, please check this query and reply back with the appropriate solution. len(ltrim(rtrim(exec('select' ' ' + 'pay' +convert(substring(@y1,3,2), varchar 2)))))<>0 here the concept is concatenating two string then that result is used as column and retreiveing data.but this is considering it as string instead of column. can anyone give an appropriate solution.
Some lots may not have any transactions for some of the periods between the start and end dates but I need to report every period between the start and end period for each lot. I have a period table that I thought I could use but haven't come up with a way to get the results I'm after.
select count (*) from MEMBERS,dbo.MEMBER_PROFILE where MEMBER_PROFILE.member_no = members.member_no AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59' and email <> 'selfbuy_customer@cafepress.com' and ROOT_FOLDER_NO is not null and email not like '%bvt.bvt'
This returns the count for the month but I want to see what the total each day was.
I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function
GO Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[code]...
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.
In a t-sql 2012 select statement, I have a query that looks like the following:
SELECT CAST(ROUND(SUM([ABSCNT]), 1) AS NUMERIC(24,1)) from table1. The field called [ABSCNT] is declared as a double. I would like to know how to return a number like 009.99 from the query. I would basically like to have the following:
1. 2 leading zeroes (basically I want 3 numbers displayed before the decimal point)
2. the number before the decimal point to always display even if the value is 0, and
I have a procedure where after processing, i am required to send multiple message to calling application.
For ex:
create procedure test as (@a as int, @b as int @c as int ) /* some transformations */ print 'variable a is' + @a; print 'variable b is' + @b; print 'variable c is' + @c;
I am only providing a sample of return messages, but in reality there a lot more messages where a string and a parameter value need to concatenated.Unfortunately print is not allowing to concatenate parameter value.I can use RaiseError, but these messages are not really any error messages.
i tried to concatenate all message and output it using OUT type parameter, but the length of all messages combined exceeds 10000 characters.
Is there any other alternate to send these messages out to application?
