I need to put multiple results in one field, but not sure how. Here's
some sample code:
select a.name,
a.accountnum,
a.ordernum,
(select itemid from items where items_ordernum = a.ordernum) as
Items[color=blue]
>From clients a[/color]
There's multiple instances of 'itemid' in 'items' with the criteria I
gave, so this generates an error. I want to instead have the item
numbers listed as just a list seperated by commas, so the results might
be this:
Name AccountNum OrderNum Items
John Smith 12345 0000234 1233, 1333, 4322
Mike Jones 43223 0000023 4322, 543
And so forth. Is there someway to loop through this sub-query and make
the results become a list?
I am trying to create an SSRS report to display a single row of Household information (from CRM Dynamics) Household being the Parent Entity, with 1:N relationship with Contact Entity.
How would I go about showing all the Firstnames (from Contact) in the same cell like the below?
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail @subject = 'Job Summary', @profile_name = 'SQL SMTP',    [code]....
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
HAX603 JULY 1 OCTOBER 31 HAX317 DECEMBER 1 DECEMBER 31 HAX317 MARCH 1 MARCH 31 HAX317 July 1 July 28
[Code] ...
Final OutputÂ
NUM STATUS ACTIVITYCODE <SEASONS>
HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August HAX523 Completed 0x124c69 1 November - 30 November
I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.
select num, (CAST(startday AS VARCHAR(3)) + ' ' + Â startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + Â endmonth)AS Season from seasons;
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
Hi, I've got what is probably a simple question, but for the life of me I cannot figure it out and it is starting to do my head in . I have the following query:- Select e.intEmployeeID AS ID, e.txtFirstName AS [First Name], e.txtLastName As [Last Name], e.txtMobile As [Mobile], i.txtWorkEmail as from EMPLOYEE AS e INNER JOIN EMPLOYEE_INFORMATION AS i ON i.intEmployeeID = e.intEmployeeID WHERE (((e.blnActive = -1) AND (e.txtFirstName Like '%b%')) OR ((e.blnActive = -1) AND (e.txtLastName Like '%b%')) ) ORDER By e.txtFirstName In my database, the field blnActive is a BIT field. When I try to run this query, I get no results returned. If I remove the (e.blnActive = -1) WHERE statement segment, I get the correct results. The field blnActive contains -1 for every record but I cannot work out why it will not return these records .... aaarrrrggghhhh Thanks to those who feel my pain !!
I am trying to make a query from a sql server 2000 database i have a table as follows:
Operatinngsystem FQDomain ============= ======== Windows server1.gnet.net Windows server2.gnet.net Windows server3.gnet.net
what i want to be able to do is to return the result so that the server1-3 asre in a seperate colunm and the gnet.net seperate i want the out put as follows:
Operatinngsystem Server Domain ============= ====== ====== Windows server1 gnet.net Windows server2 gnet.net Windows server3 gnet.net
therefore three colunms output. I would be greatful if any one got any ideas how i can do this? (Please note im new to sql server)
Hello there I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms. I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking. Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query. The table structures are as follow: bookingrequestion - bookingrequestid, startdate, enddate roomsbooked-id, bookingrequestid, roomname i'm basically trying to use the following query select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms i'd like the results of the subquery to return the room names as A,B,C. I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me. thanks
I am trying to pull results from an SQL Server DB into an dataset where a particular field (SMALLDATETIME) is within a particular date range. The code I was using worked fine when the database was in Access. I have made several changes already but am still getting 0 results returned when there is data that should be returned.
I was using in Access: Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>=#" & startDate & "# AND myDateField<=#" & stopDate & "# ORDER BY ID" I have changed this for SQL Server to: Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>='01/01/2003 00:00:01' AND myDateField<='01/01/2004 23:59:59' ORDER BY ID" But I am always returned 0 results even if the date range should return plenty of data. I have also tried using the BETWEEN structure with the same result.
Is there a particular format for the date I am comparing with? Am I missing something else in my query?
The connection / permissions and everything else are correct as I can read and write data to the database in numerous other pages. It is just this date comparison that is not working.
Many thanks for any help or comments you can provide.
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field. Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below: SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2], Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2], TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName FROM Ticket INNER JOIN TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID WHERE (Ticket.CreatedDate >= @StartDate) ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
SELECT DSNew, DTTM, RQDT FROM dbo.Feb INNER JOIN DMSEFL ON ACTR = DSNew where cast(DSNew as varchar(20)) = cast(ACTR As varchar(20)) If I run the above query I get zero recs back.
If I substitute a Value then I get the desired results (ie. where DSNew = '93235500') or if I enter (ACTR = '93235500') or if I put (where DSNew = '93235500' AND ACTR = '93235500')
Can anyone suggest a reason why this is happening. I know the records exist on both tables I ran the query in Acess and got the desired resutls.
Hi everyone, I have a DTS package on my SQL Server database that returns several different results. Does anybody know how I would access any one particular part of the return. I am trying to make it so that there is only one run to the database for the information, but am not sure how to get just the parts I want out of the DTS package. for Example, here is my DTS Package: Select @InvoiceNo as InvoiceNo, '02/01/08' as PaymentDate, 1000.00 as PaymentAmt, 500.00 as RemainingAmt, 'X31235X' as ReceiptNumber Declare @Results Table ( InvoiceNo char(10), Amt float) Insert Into @Results Values('SNJ0100001',100.00)Insert Into @Results Values('SNJ0100002',200.00)Insert Into @Results Values('SNJ0100003',300.00) Select * from @Results How would I ask for just the results from the @Results section and then somewhere else on my page I will want to return just the results from the @InvoiceNo section?? I would normally call the package from my code-behind and I know how to do that, just not to pull a particular section. Here is what I have so far on that section: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="hsp_rpt_AR_ReceiptStatus" SelectCommandType="StoredProcedure"><SelectParameters><asp:ControlParameter ControlID="TextBox1" Name="InvoiceNo" PropertyName="Text" Type="String" /></SelectParameters></asp:SqlDataSource> Thanks for any helpRandy
As you can tell, the 'custom_address' column is going to house the results of the combination.
I am trying to INSERT (combine) using this statement:
INSERT #Custom_Address select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id WHEREcc.coverage_plan_add1 > 0 OR cc.coverage_plan_add2 > 0 OR cc.coverage_plan_city > 0 OR cc.coverage_plan_st > 0 OR cc.coverage_plan_zip > 0 GROUP BY cm.patient_id, cm.episode_id
I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.
Then I do my update:
UPDATE#ClaimMaster SET #ClaimMaster.custom_address = ca.custom_address FROM #ClaimMaster JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id
However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.
If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?
As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.
When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results.
The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query. The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there. How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set? Thank you, Mike
Is there a way to get multiple rows of data to show in 1 row of results? I have a Data Table (ID, Name, Date, etc), Facility Table (ID, Name) and FacilityKey Table (Data ID and Facility ID). The Data table has a 1 to many relationship with the FacilityKey table.
I want to return the Data ID and each Facility Name associated with that Data ID to display in a DataGrid (preferably each Facility Name in the same cell on the grid too), but am stumped on how to get teh query to work (or where to look in the SQL documentation for something like this).
I'm totally new and been reading the forums to help me and I think I've done ok so far getting the results I need from basic SQL selects and stuff but now I seem to have hit a wall and I realize I need help from someone who really knows their SQL.
I'm using Microsoft SQL 2000.
I have a database with columns
Product_ID Product_Name Product_Association
The Product_ID column is the unique ID key. Product_Name is the name of the product. And Product_Assocation represents search terms.
The column Product_Assocation has for example the values
1. How do I select the product based on ProductID and display each word independently in a recordset?
For example how can I Select from Table where Associate = 'each word and list them' for Product_ID = '1'
2. I have to allow the user to "search" for a word in the column so I need to create an SQL statement that goes someting like
Select * from Table where searchresult = 'search word for example tree which is a word seperated by one of the * in the Product_Association column' and that the recordset returns the table row.
I am sure these both probably sound like silly and simple questions and if they are I am sorry guys. I am trying my best. I am a newbie and no point pretending otherwise. Hopefully someone with greater SQL knowledge out there can assist me because I have tried all I know but can't seem to make any headway.
I am using ASP not .net or php if that makes any difference? I'm not sure that it does as I think it is about the SQL Select statement and how to construct them?
I have a query running without issue on around 100,000 results however i have a couple where my MAX aggregate is returning two results for a MAX(date time) and i cannot figure out why. I have paired down the query to try and work it out but still don't know.
My query now looks like this (i have remove most columns)
SELECT STATUS, RunOn, sort, T_ID FROM dbo.Results WHERE (RunOn IN (SELECT MAX(RunOn) FROM dbo.Results group BY T_ID) AND T_ID = 21405)
ORDER BY sort
I added the T_ID = 21405 to restrict the results to the problematic results, if i set T_ID to any other test result i get one value based on the latest RunOn, but for some reason with this T_ID i get two values, the latest one and one slightly older... The date time looks ok, i just can't work out why the latest and one older result are returned !
The original query was grouping by more items but i removed those to try and work out what is going one, as mentioned for 99.9% of results it works perfectly, but for this one i don't know.
I have created a new reports application project in VS 2008. I have a dataset with 2 tables: Customer and CustomerAddress with one to many relationship. I want to have a simple table in my report which displays data in the following format: Customer Name Address ---------------------------------------------------------- ABC Add1 ABC Add2
XYZ Add1 XYZ Add2
Here Address is obviously from CustomerAddress table. I have tried few options but it's mainly disgusting to work with Reports Application project when there was an amazing Busines Intelligence Reports project available in VS 2005 and equally good designer interface (Dataset, Design and Preview tabs for each report). Please give me a solution to this.
I want to display the top 5 count of areacodes for each name I want to combine all the results for the areacodes into one column for each user. like a csv. I tried my code below but the results just return the same 5 of areacodes for all names on each area code row with each callername. like joe blow 123,456,755,312,465,567,555 bill jones 123,456,755,312,465,567,555
I just want the top 5 for each particular name. I tried reading a few articles on putting multiple colums in one row and i could not figure out what i am missing! Thanks
) --This is where the area code data comes from I can get it to display in multiple colums, but -- I want the area codes to be on one line for each name SELECT CallerName, SUBSTRING(TargetNum, 2, 3) AS AreaCode, COUNT(*) AS AreaCodeCount, DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0) as myDate FROM CDRMAIN WHERE LEN(TargetNum) >= 11 AND TargetNum NOT LIKE '_800%' AND GtCalcDate >= '2006-11-06' AND GtCalcDate < '2006-11-07' GROUP BY CallerName, SUBSTRING(TargetNum, 2, 3), DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0) ORDER BY CallerName, COUNT(*) DESC
-- Get Calls SELECT s.CallerName, s.AreaCode, s.Calls, s.theDate--,myareacodes FROM @Stage s INNER JOIN ( SELECT CallerName, MIN(RowID) mirw, 4 + MIN(RowID) marw FROM @Stage GROUP BY CallerName
HAVING (CallerName = 'name1') OR (CallerName = 'name2') ) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw ORDER BY callername,Calls desc -- set @MyAreaCodes ='' -- SELECT top 5 @MyAreaCodes = @MyAreaCodes + ISNULL(AreaCode,'') + ',' from @Stage
-- SELECT CallerNAme,@MyAreaCodes AS MyAreaCodes from @stage Group By CallerName
The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like: userid, name, groupids 12344, 'Bob', '123,234,345'
If I try to just select groupid from groupadmins: select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users
then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?
Here is my sample table creation and insertion script. I want represent as a summary-result using just one record. How can I ? Please note the below red color text.
create table policy ( id int not null, name nvarchar(50) not null, constraint pk_policy primary key(id) );
create table localhost ( policy_id int not null, id int not null, ip_begin binary(4) not null, ip_end binary(4) not null, prefix tinyint not null default 0, constraint pk_localhost primary key(policy_id,id) );
create table remotehost ( policy_id int not null, id int not null, ip_begin binary(4) not null, ip_end binary(4) not null, prefix tinyint not null default 0, constraint pk_remotehost primary key(policy_id,id) );
create table rate ( policy_id int not null, inbound int not null, outbound int not null, constraint pk_rate primary key(policy_id) );
------------- insert into policy values(0,N'policy0');
insert into localhost values(0,0,0xC0A80101,0xC0A80101,24); insert into localhost values(0,1,0xC0A80A01,0xC0A80B01,0);
insert into remotehost values(0,0,0xACA80101,0xACA80101,0); insert into remotehost values(0,1,0xACA80A01,0xACA80B01,0); insert into remotehost values(0,2,0xACA80C01,0xACA80C01,24);
insert into rate values(0,1000,2000);
------------- select * from policy; select * from localhost; select * from remotehost; select * from rate;
-- result of policy table 0 policy0
-- result of localhost table 0 0 0xC0A80101 0xC0A80101 24 0 1 0xC0A80A01 0xC0A80B01 0
-- result of remotehost table 0 0 0xACA80101 0xACA80101 0 0 2 0xACA80C01 0xACA80C01 24
-- result of rate table 0 1000 2000
------------------------------------------------------ desired result set id name localhost remotehost rate -- ---------- ---------------------------------------------------------------- ------------------------------------------- ----------------- 0 policy0 (192.168.1.1/24),(192.168.10.1~192.168.11.1) (172.168.1.1),(172.168.12.1/24) 0,1000,2000
descriptin of desired result set : 0. key value is id column and is referenced by each policy_id column. 1. id and name column is the same as policy table's. 2. localhost and remotehost columns are intigration of ip_begin,ip_end,prefix. 3. ip_begin and ip_end should be converted dotted presention from numeric format. 4. if prefix greater than 0, it should be displayed using '/'. 5. if ip_begin and ip_end are equal, show just one ip. 6. if the two ips are different from each other, they separated by '~'. 7. rate field is packed divided by ','.
If I have multiple selects statements in one stored procedure. How do I access the different results in a report in Reporting Services? Where the first select is the detail lines of my report and the second select is just a field for a my header? Or am I going about this wrong with putting it all the results I want into one stored procedure for one report?
Example stored procedure:
ALTER PROCEDURE [dbo].[proc_DepositsByOfficer]
As
SELECT MASTER_DSC.APP
, MASTER_DSC.BRANCH
, qlkpOfficer.strName
, MASTER_DSC.DSC_OFFICER_49
, qlkpBranchName.strDescrS
, MASTER_DSC.DSC_CUR_BAL_21
, Case MASTER_DSC.APP WHEN 1 Then DSC_CUR_BAL_21 End AS DDA_BAL
, Case MASTER_DSC.APP WHEN 2 Then DSC_CUR_BAL_21 End AS SAV_BAL
, Case MASTER_DSC.APP WHEN 3 Then DSC_CUR_BAL_21 End AS CD_BAL
, MASTER_DSC.DSC_INT_RATE_45
, Case When [DSC_CUR_BAL_21]>0 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End As ANN_EXP
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=1 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_DDA
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=2 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_SAV
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=3 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_CD
, Case MASTER_DSC.APP WHEN 1 Then 1 End AS DDA_Count
, Case MASTER_DSC.APP WHEN 2 Then 1 End AS SAV_Count
, Case MASTER_DSC.APP WHEN 3 Then 1 End AS CD_Count
, qlkpApplicationCode.strDescrS AS strApplCode
FROM
MASTER_DSC
INNER JOIN qlkpApplicationCode ON MASTER_DSC.APP=qlkpApplicationCode.dblReference
LEFT JOIN qlkpOfficer ON MASTER_DSC.DSC_OFFICER_49=qlkpOfficer.intID
LEFT JOIN qlkpBranchName ON MASTER_DSC.BRANCH=qlkpBranchName.dblReference
itemID | part 1Â Â Â Â Â Â Â Â | A 1Â Â Â Â Â Â Â Â | B 2Â Â Â Â Â Â Â Â | A 2Â Â Â Â Â Â Â Â | A 2Â Â Â Â Â Â Â Â | A 3Â Â Â Â Â Â Â Â | C
I need the table to look like the following:
itemID | part1 | part2 | part 3 1        | A       | B     | null 2        | A       | A     | A 3        | C       | null   | null
There will _never_ be more than three parts to an item, and it does not matter what order they are in.
I'm writing a FTS query which needs to search on two different columns.
E.g. Table contains "Location" and "LocationDescription" columns. Both columns are FT indexed. The query also uses AND/OR operators to filter out the results.
I found the following article which gives the solution to the same problem. Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787 Is this problem associated with SQL Server 2005 also?
Making a third column which hold data from first two column is the only solution or is there any other way to acheive better results?
Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....
I am currently working on an application that requires, insertion of the results of a SQL Query in to the field of another table, in the form of a comma separated values.
For example, to explain things in detail:
create table dbo.phone_details
(country varchar(20), state varchar(30), list_of_toll_free_numbers text)
insert into dbo,phone_details values ( 'USA', 'CA', 'select Phone from phone_table where substring(phone, 1, 3) in ('800', '866', '877', '888')' )
The final output I desire is:
country state list_of_toll_free_numbers
---------- ------- ----------------------------------------- USA CA 8009877654, 8665764398, 8776543219
The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates...
But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render.
In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do...
/*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/
SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection); sda.Fill(dt);
... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned).
So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment?
