Problem Concatenating Column Values Into String...
Apr 5, 2006
I have a customer who has recently migrated their SQL server to a new
server. In doing so, a portion of a stored procedure has stopped
working. The code snippet is below:
declare @Prefixes varchar(8000),
declare @StationID int
-- ...
select @Prefixes = ''
select @Prefixes = @Prefixes + Prefix + '|||'
from Device
where Station_ID = @StationID
Essentially, we are trying to triple-pipe delimit all the device
prefixes located at a specified station. This code has worked
flawlessly for the last 10 months, but when the database was restored
on the new server, @Prefixes only contains the prefix for the last
device.
Is there a server, database, or connection option that permits this to
work that I am not aware of? Why would this work on the old server and
not on the new? (BTW - both servers are running SQL 2000 Standard
SP4).
Thanks!
View 6 Replies
ADVERTISEMENT
Aug 13, 2003
Hi All,
I am trying to write a select statement which will concatenate all values of a string column and provide me with a result set containing just one row of data containing a concatenation of all values.
For eg:
column1
abc
def
hij
klm
nop
is it possible to write a select statement which would return
result
abcdefghijklmnop
as a result?
TIA
Ketan
View 6 Replies
View Related
Aug 31, 2000
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!
TIA,
Arthur
View 1 Replies
View Related
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
Mar 12, 2008
Hey guys, I 'm coding my very first stored procedure as accessed by a
.NET application. My input parameter is a dynamically built string. I need to concatenate to a sql query within the SP. I've tried using '+' as the concat. character but it doesn't work.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[tmptable_query] (@condition_cl varchar(100)) as select * from temp_table + @condition_cl
Help would be appreciated. Thank you.
View 5 Replies
View Related
Jul 20, 2005
I am trying to create a job that, as one of its steps, will kick off aDTS package. As part of the command parameter, I need to concat asystem variable (@@SERVERNAME) to a constant string. I am receiving anerror about incorrect syntax near the +.Here is the code for the job step.-- Add the job stepsEXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep@job_id = @JobID,@step_id = 1,@step_name = N'Import OCC Series Data',@command = N'DTSRun /FD:DatabasesScriptsDTSImportOCCSeriesData.dts /A DbName:8=' +@@SERVERNAME,@database_name = N'',@server = N'',@database_user_name = N'',@subsystem = N'CmdExec',@cmdexec_success_code = 0,@flags = 2,@retry_attempts = 0,@retry_interval = 1,@output_file_name = N'',@on_success_step_id = 0,@on_success_action = 3,@on_fail_step_id = 0,@on_fail_action = 3IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackIf I just try SELECT N'DTSRun /FD:DatabasesScriptsDTSImportOCCSeriesData.dts /A DbName:8=' +@@SERVERNAME, everything works fine. I even tried declaring a localvariable named @command and setting it in the select statement, but nodice.
View 2 Replies
View Related
Mar 27, 2015
Each patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?
I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right.
View 8 Replies
View Related
Mar 31, 2008
I'm trying to write a stored procedure in SQL that will make a copy of a file and then append the current date to the end of the copy's filename. I'm storing the date in a variable called @currentdate which is defined as:
declare
@currentdate varchar(10)
set @currentdate='' + 'select datepart(month,getdate())' + '-' + 'select datepart(day,getdate())' + '-' + 'select datepart(year,getdate())'
Here is the SQL code I'm using:
exec xp_cmdshell 'copy "C:DevelopmentParticipant Limits ReportParticipant Limits Report template.xls" "C:DevelopmentParticipant Limits ReportParticipant Limits Report ' + @currentdate + '.xls"'
GO
The resulting file should have a filename something like "Participant Limits Report 3-31-2008". I get an "Incorrect syntax near '+'." error.
View 20 Replies
View Related
Jul 11, 2006
Hey everyone,
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?
Thanks,
View 8 Replies
View Related
Sep 20, 2007
Hello,
I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.
--code within cursor
SELECT '
DECLARE @Result INT
EXEC @Result = DELETE_DOCUMENT
@DocumentID = ' + STR(DocumentID) + ',
@TimeStamp =' + CAST([Timestamp] as varchar) + ',
-- CHECK RESULT AND STATUS
-- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL
FROM Document
The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values
T
T€‘
T
xnÞ
T!
T"
T#
T$
T%
T&
T'
T(
T)
T*
T+
T,
instead of
0x0000000013540F1C
0x0000000013540F1E
0x0000000013540F1F
0x0000000013786EDE
0x0000000013540F21
0x0000000013540F22
0x0000000013540F23
0x0000000013540F24
0x0000000013540F25
0x0000000013540F26
0x0000000013540F27
0x0000000013540F28
0x0000000013540F29
0x0000000013540F2A
0x0000000013540F2B
0x0000000013540F2C
which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.
Thanks
Sam
View 3 Replies
View Related
Dec 13, 2007
I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.
I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.
The code that is not working is listed below. It does not work. It returns Null. I suck.
DECLARE @column_names varchar(8000)
SET @column_names = ''
SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'
SELECT @column_names
little help?
View 6 Replies
View Related
May 20, 2015
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?
View 5 Replies
View Related
Dec 1, 2007
i have a report with contains preview of percentage columns example of percentage of student marks in perticular subject like 95%. and if suppose any student not attend any test i have to dispaly like not attended statement.
so i have display two fields like 95% and not attended statement in same column, i given Cstr(Fields!Data.Value), it gives two fields with contains not attended statement of perticular query and it dispalys 0.95 % . but i need 95% and not attended statement for perticular query in same column.
is there any solution for dispalying string and percentage values in single column for given perticular query and those two values are disply same result compare with preview at the time of export to excel sheet
plese send solutions ASAP
Thanks
James vs
View 1 Replies
View Related
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
View 3 Replies
View Related
Sep 27, 2007
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???
Thanks in advance,
Jen
View 26 Replies
View Related
Sep 7, 2006
Hi,
I need to concatenate a field from certain number of rows. I created a function to return the concatenated value which will be a part of another view/procedure to be used for reporting purposes.
Here's sample data:
iIncidentID iWorkNoteID iseqNum workNoteAll
1 1 1 notes1(1275 chars)
2 1 1 notes2(1275 chars)
2 1 2 notes3(1275 chars)
3 1 1 notes4(1275 chars)
3 1 2 notes5(1275 chars)
3 1 3 notes6(1275 chars)
Final output
iIncidentID workNoteALL
1 notes1(1275 chars)
2 notes2 notes3
3 notes4 notes5 notes6
final woorkNoteAll will be a part of a query in another view which contains many other fields.
Here's the function. I'm passing an ID and based on that ID, the function returns a string. However, when I tested the function it's giving me a null.
/*
--Calling syntax:: Select dbo.getIncidentNotes(187714) as 'Notes'
--Function to get all the latest notes for an incident
*/
CREATE FUNCTION dbo.getIncidentNotes(@iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @allnotes varchar(8000)
DECLARE @seqnotes varchar(255)
DECLARE @seqnum int
DECLARE @counter int
SELECT @counter=1
SELECT @seqnum = max(iseqnum) from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID
WHILE (@COUNTER <=@seqnum)
BEGIN
SELECT @seqnotes = workNoteALL from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID and iSeqNum=@counter
SELECT @allnotes = @allnotes + @seqnotes
SELECT @COUNTER = @COUNTER + 1
END --While Begin
RETURN @allnotes
END
Can someone please tell me what's wrong with the code?
I really appreciate it.
Thanks in advance.
View 5 Replies
View Related
May 5, 2008
I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have 2 database table3 as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4] , WeeksSinceDr0, WeeksSinceDr1, WeeksSinceDr2
2) myGameFrequency which contains columns : AllBallsFrequency , WeeksSinceDrawnAllBalls
Using the myGameRecencyAllBalls table ---
I wish to insert a 0 into a column corresponding to a ball that has been drawn, eg if a 4 has been drawn, then a 0 will be inserted into that column. If a ball has not be drawn, then the value in that column will be a running total, signifying the number of draws since it was last drawn ( ie since a 0 was inserted into that column).
I place a 1 in the column corresponding to the number of weeks since a number has been drawn. The name of the column is therefore the concatenation of the string literal 'WeeksSinceDrawn' and the value held by the variable, @lastRowCount obtained by the lastrow_CURSOR.
I have declared a variable @colName to hold the concatenation / Set @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) and then tried to use it as follows: SET [@colName] = 1
however, I receive an error message advising me that I have an invalid column name. Is there any means of setting a column name by concatenating two variables or , a string literal and a variable ?
Thank you
lpbcorp
sqlCmd.CommandText = "DECLARE @colName nvarchar(max) " +
"DECLARE @lastRowCount int " +
"DECLARE lastrow_cursor CURSOR SCROLL FOR " +
"(SELECT [" + i.ToString() + "] FROM " + DBGameName.ToString() + "RecencyAllBalls) " +
"OPEN lastrow_cursor " +
"FETCH LAST FROM lastrow_cursor INTO @lastRowCount " +
"SET @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) " +
"IF @lastRowCount <= 175 " +
"BEGIN UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET [@colName] = 1 WHERE Date = '" + Date + "' " +
"END " +
"ELSE " +
"UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET WeeksSinceDrOver175 = 1 WHERE Date = '" + Date + "' " +
"CLOSE lastrow_cursor " +
"DEALLOCATE lastrow_cursor";
sqlCmd.ExecuteScalar();
View 3 Replies
View Related
Jan 5, 2005
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.
Regards,
Uma.
View 2 Replies
View Related
Jan 17, 2008
Hi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
Regards
View 2 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Sep 3, 2015
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.Â
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.Â
View 3 Replies
View Related
Feb 12, 2014
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
View 2 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
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...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
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.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
Jun 18, 2015
Bitmask fields! I am capturing row changes manually via a high frequency ETL task. Â It works effectively however i am capturing the movement of multiple fields. Â A simple example, for Order lines, i have a price, a discount and a date. Â I am capturing a 001, 010, 100 respectively for each change. Â
I would like my users to be able to select from a dimension which has the 3 members in it and they can select one, multiples, or all values (i.e. only want to see rows that have had the date and price changed).Â
Obviously if i only had 3 columns i would use bit's and be done with it, i have many different values (currently around 24 and growing).
View 2 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Jul 9, 2015
I have a table with 2 columns and my source data looks like this..
PolicyNum  InsCode   Â
1ABC12Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum  InsCode1  InsCode2   Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â 1006Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC20Â Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005Â Â Â
          InsCode value 1002 should always mapped to 1006
           InsCode value 1003 should always mapped to 1004Â
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')Â Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')Â Â Â Â Â Â
[Code] ....
View 14 Replies
View Related
Mar 5, 2007
Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
View 7 Replies
View Related
Sep 14, 2007
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
View 1 Replies
View Related
Sep 16, 2004
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
View 2 Replies
View Related
Dec 21, 2007
Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies
View Related
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
Dec 10, 2004
Hello,
I am working with ASP.NET/VB and Microsoft SQL 2000 database.
I have a search form where keywords are submitted.
Consider I write the the keywords 'asp' and 'book'. The results page is called as follows: results.aspx?search=asp%20book
Then I use this script in results.aspx to put the keywords in a string:
Sub Page_Load(sender As Object, e As System.EventArgs)
Dim keywords() As String = Request.QueryString("search").Split(CChar(""))
End Sub
My table is set for FULL TEXT SEARCH.
Consider the SQL when I look for records containing 'asp' and 'book':
SELECT *
FROM dbo.documents
WHERE CONTAINS (*, 'ASP or BOOK')
This SQL looks only for these words. What I need is to look for records that contain the Keywords included in the string keywords().
Can you tell me how to access the string values in the SQL and use it?
Thanks,
Miguel
View 2 Replies
View Related
