I know this is possible in DB2 and Oracle, but what about for SQL-server 2005
1) select X number of rows from table1
2) I need colums for each row of table1 in a new table
3) As such, Select (select * from X where x.id = @ID), a,b,c from table Y where y.Id = @ID
And I dont want to use IfExists.
Thanks
DK
I am getting this from my client
TableA
StorID LineNumber Text
30000 1 ClientName
30000 2 ClientSurname
30000 3 3333333
30000 4 20-05-2004
I actually want this to look like
TableB
StoryID Name Surname Policy Date
30000 Name Surname 3333333 20-05-2004
Can you help with the script to convert this to one record with many fields (as in TableB) if this is
Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
Thanks
Shabassa
Hello all.
I have a column named 'RELATED' of type text in a table. I want to update the contents of this colums...........but still keep what is currently stored in it. Basically i want to add some text onto the end of the string it currently contains.
Any help would be much appreciated.
I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:
Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91
I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%
This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.
But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.
I can restructure the database, if needed.
I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.
It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?
Can someonte tell me that if i
bcp bda..mytable out c:discounts.xls -c -p , how can I put the first row as my column names since I get only data
Hello All, first time post here and really stuck on this issue.
I have 4 Tables that i am Joining Across. Each one of the tables has around 800,000 Rows in it. I am Joining them all on a field Called MDRReport Key.
my query looks like this
SELECT Mdrfoit.MDRReportKey, Mdrfoit.DtReport, Mdrfoit.DateReceived, Mdrfoit.EventType, Mdrfoit.AdverseEvent, Mdrfoit.ProductProb, Mdrfoit.ReportOccup,DevTable.BrandName, DevTable.ManName, DevTable.ModelNumber, DevTable.ProductCode, DevTable.DeviceFamily, TextTable.Text
FROM Mdrfoit
INNER JOIN DevTable ON Mdrfoit.MDRReportKey = DevTable.MDRReportKey
INNER JOIN TextTable ON Mdrfoit.MDRReportKey = TextTable.MDRReportKey LEFT OUTER JOIN probLink ON Mdrfoit.MDRReportKey probLink.MDRReportKey
WHERE Mdrfoit.DateReceived BETWEEN '1/1/1995' AND '1/1/2007') AND
AND (DevTable.BrandName LIKE '&PLV%')
My problom is that when i do this i get back 3 rows in my result set for every 1 result that i want So ill get like
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
So my result set is on average 3 time larger then its suppose to be, This is also causing my query time to be very very slow.
There are no Duplicate entries in the database where all of the fields are the same. I am confused on if this is a problom in my query. Any advice would be great
"When you have everything to loose and nothing to gain, All-In is not an option"
Hi all,I am a newbie to sql and I need your help.I want to update column (email) from one table to another validating theCustomerid column in both table. Update the email address in productiontable with the email address in temp table if the customerid is same in bothtables.What would be the query?Thanks,
View 3 Replies View RelatedHello,
I have this problem that I do not now how to solve it:
I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).
If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.
Nr. Of Orders Nr. of Lines Average Delivery Time
Open Order 10 20 3.2
Closed Order 15 30 5
Total 25 50 8.2 (it should be, for example, 4.5)
Is there a way in which I can tell the system to not calculate the total for the third column?
If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.
Thanks to all!
Andrea
The joined view is named "dbo.viewExecView" and is like:
SELECT Bank_No, data_center
FROM [ALPHASQL2000].ev_db.dbo.Bank
The new view that joins to the above view is like:
SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)
Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.
I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.
Regards,
Joe
Hello everyone,
I'm stuck on something here. Any help would be great. This is a relational database question.
I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.
Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_SubCategories.SubCategoryName,
_SubCategories.SubCategoryName,
LinkName
FROM
_Links
INNER JOIN
_SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId
INNER JOIN
_SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable
Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:
Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_Links_Categories.CategoryName,
_Links_StatusCategories.StatusName,
LinkName
FROM
_Links
INNER JOIN
_Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId
INNER JOIN
_Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable
I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.
Table_Categories: CategoryId (Primary Key), CategoryName
Table_SubCategories: CategoryId, SubCategoryId (Primary Key), SubCategoryName
So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.
If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.
Alec
Hello, and thanks for taking the time to read this.
NOOB question:
In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:
CREATE TABLE [dbo].[T_Catalog](
[StlyeID] [int]
) ON [PRIMARY]
with data:
1
2
the SIZES table (filled in by the customer with all the size ranges they carry) is:
CREATE TABLE [dbo].[T_Sizes](
[SizeID] [int],
[SizeName] [nchar](10)
) ON [PRIMARY]
with data:
1,Small
2,Medium
3,Large
4,Xtra-Large
and the AVAILSIZES table would be:
CREATE TABLE [dbo].[T_AvailSizes](
[StyleID] [int],
[SizeID] [int]
) ON [PRIMARY]
1,1
1,2
1,3
2,1
2,3
2,4
Basically, then, we know that:
style 1 comes in Small, Medium, and Large
style 2 xomes in Small, Large, and Xtra large
WE know that, but getting SQL to tell us that is a major PIA!!
Now,
SELECT
t_Catalog.StyleId, t_AvailSizes.SizeID
FROM
t_Catalog
INNER JOIN t_AvailSizes ON
t_Catalog.StyleId = t_AvailSizes.StyleId
will give me a nice list of each item number with a separate row for each size number.
My questions are:
How do I get the size NAMES?
How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?
Is this the right way of doing this?
In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.
Thanks for any help and information you can provide.
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
Hello
Iam usng sql server 2005 database
I had this table
CREATE TABLE [dbo].[Companies](
[CompanyID] [smallint] IDENTITY(1,1) NOT NULL,
[Company] [nvarchar](40) NOT NULL,
I entered Arabic Names
so I write this query
select * from companies order by company asc
--and this one
select * from companies order by company
and I try to order desc
but the row didnt appear out ordered
and the big prtoblem happened when I try to update a company Its go to the last row when it appears
please help me
thanks
My problem is simple: i want to dynamic specified the columns in the ContainsTable, this is possible? Please see the example.
Declare @Test1 int
Declare @Test2 int
Declare @Query varchar(50)
Declare @Temp varcahr(50)
--Test
Set @Test1=1
Set @Test1=0
Set @Query='something'
--Add the column to put in containstable
IF (@Test1=1)
Begin
Set @Temp='ID'
End
IF (@Test2=1)
Begin
Set @Temp= @Temp + ',Name'
End
SELECT *
FROM
<table>
INNER JOIN
CONTAINSTABLE (<table>, @Temp, @Query) AS KEY_TBL
ON <table>.ID = KEY_TBL.[KEY]
Thanks
Okay I have a problem and I have no idea to do it. I need the SQL syntax to come up with this:
the formatting is messed up though
a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.
DNAMELOCNumber of PeopleSalary
-------------------------------------------------------------------
ACCOUNTINGNEW YORK3 2916.67
RESEARCHDALLAS52175
SALESCHICAGO61566.67
Hi,
I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
Can someone help me with this?
Cheers Wim
What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg
View 1 Replies View RelatedHi
I'm trying to add columns to an existing table using ADOX. I'm using C++/CLI. My code is as follow
Table->default->Append("new_column", ADOX:: DataTypeEnum::adWChar, 255);
Table->default["prev_instance_id"]->Properties["Jet OLEDB:Compressed UNICODE Strings"]->default = true;
The first line of code works fine but when I try to set the value of the property I get the following excpetion:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll
Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Any idea?
Marco
How do I copy a column(or colums) from a table in one database to another table in a different database
View 1 Replies View RelatedCannot see the Colums in the "design view" of Queries. All i see when i want to design a new query is *columns
This happens in only one database, in other databases using same server i can see the colums and can tick them to view then in the query.
In enterprise manager i see all the columns.
Using SQL 2000 and MSAccess 2000
I have 3 tables...
JobRequirements (A)
JobID int
QualificationTypeID int
EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int
Employee (C)
EmployeeID int
EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
Any sort of help would be highly appreciated.
Thanks,
I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.
How I can use this variable to email fellow users. Â For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
Any sort of help would be highly appreciated.
Thanks,
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID )
and the newly inserted row with a insert time stamp
so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.
example of Data would be like
exisiting data
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
New row to insert would be
12 DDS 15 M DFG ST 4/5/07
the data should reflect
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 4/5/07
12 DDS 15 M DFG ST 4/5/07 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
Please provide your input as much as you can even though it might not be a 100% solution.
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
View 3 Replies View Related I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
I am facing an issue that Data flow task failing after loading 29000 rows out of 2lakhs rows.
I am loading data from .csv file to OLE DB Destination.
This data flow task is placed inside For each loop container.
is this issue because of any performance issue in SSIS packages such as buffer size.
find the error below:
DFT Load Data from FlatFile:Error: The conditional operation failed.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.Â
The "DER Add Calc Columns" failed because error code 0xC0049063 occurred, and the error row disposition on "DER Add Calc Columns.Outputs[Derived Column Output].Columns[M_VALUE_NUM]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "DER Add Calc Columns" (48) failed with error code 0xC0209029 while processing input "Derived Column Input" (49). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[code]....
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW