i have 2 fields name_last , name_first
when i use the query ,
Select name_last , name_first from members order by 1,2
i get the values..
Now I want to merge the fileds and show in a single column like Name_last , Name_first
ie ::
Select name_last , name_first from members order by 1,2 =
Col1 Col2
Aagersen Kevin
Select name_last " , " name_first from members order by 1,2 =
ColumnMerge1
Aagersen , Kevin
I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .
CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )
[code].....
Hi
I'm working with merge replication between Sql Server 2005 and Sql Server 2005 Mobile. I'm using dynamic filtering by function HOST_NAME (... where Table.FilteredColumn = HOST_NAME())
When I'm trying to chagne values from filtered column on the client side (Table.FileteredColumn) I get error message that the column is read-only.
How can I change data in filtered column? Is this possible?
How do I pass a single column of values from a successful merge join to an EXECUTE SQL statement so it can be used with an "IN" criteria of the WHERE clause? Here's an example of my update statement with two random key values:
UPDATE dbo.MyTable SET MyStatus = 1 WHERE MyPK IN ("XYZ123", "DEF890")
Is this even possible in SSIS, or am I better off using a loop and running the update EXECUTE SQL Statement for each individual key value, as in the following example?
UPDATE dbo.MyTable SET MyStatus = 1 WHERE MyPK = "XYZ123"
UPDATE dbo.MyTable SET MyStatus = 1 WHERE MyPK = "DEF890"
Hello all,
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Any suggestions?
thanks for your time...
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.
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.
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.
Hi,
I am new to SSIS.I am doing a left outer jon using the merge join.The output was wrong.I realised that may be we have to trim the key values.But when i do the trim in the derived column,the output of the derived columns shows that issorted is becoming false and the sort key position has become 0.So the merge join is showing error.How can i solve this error.
Please help.
I have this stored procedure which generates the following output.
The query which generates the output from the stored procedure is as follows:
SELECT tat.[agent-id],
tat.[agent-guid],
tat.[agent-name] AS [AgentName],
tw.TagWidgetID,
tw.TagWidgetName,
t.TagID,
core.fnInitialCapitalization(t.DisplayName) AS TagName,
[Code] ....
How can I join the output rows only if the TagWidegetIds are same but the TagValues, TagIndex and ValueIndex columns are different?
I'm trying to use merge data from a staging table to a production table. There are a lot of duplicate values for serverName and I only want to insert one instance where there are duplicates.
How I can adapt the code I have so far to achieve this?
MERGE tblServer AS TARGET
USING tblTemp AS SOURCE
ON (TARGET.serverName = SOURCE.serverName)
WHEN MATCHED THEN
UPDATE SET TARGET.serverName = SOURCE.serverName, TARGET.serverLocation = SOURCE.serverLocation
WHEN NOT MATCHED BY TARGET THEN
INSERT (serverName, serverLocation)
VALUES (SOURCE.serverName, SOURCE.serverLocation)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
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]
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).
I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.
In the long sql, here is a part of it,
When Matched and
((Student.SchoolID <> esis.SchoolID
OR
Student.GradeLevel <> esis.GradeLevel
OR
Student.LegalName <> esis.LegalName
OR
Student.WithdrawDate <> esis.WithdrawDate
Student.SPEDFlag <> esis.SPEDFlag
OR
Student.MailingAddress <> esis.MailingAddress)
Then update
Set Student.Schoolid=esis.schoolid,
.....
My question is how about if the column has null values in it.for example
if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.
I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?
Hello,
We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.
C# .Net Application as front end
Sql Server2000 as back end
I need to merge an external dataset from .Net app(in XML format) with the information in database with one column in database table as the merging criteria. A situation similar to Left Outer Join, wherein i need all records from external dataset and if matched in database the corresponding values from there too, the only difference here is that the join is not between two Tables its between a table and external dataset.
There is no need to store the external dataset in the database in persistent form, its just a query - merge - response operation.
So, can anyone suggest the best possible solution for this? A table variable / temporary table / some other schema, what and how?
Thanks in advance..
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 RelatedI 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] ....
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))
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
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
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!!!
Hi I have tried to add a new column to a publication table using sp_repladdcolumn and also directly add the column under Publication's properties screen.
I am able to add the column using both method. But changes to the newly added column either at the publisher or subscription databse will not get replicated.
Have I miss out something? or do I need to reinitialise the subscription to make changes in the newly added column to be replicated?
Hi
I have an old db I'm converting with SSIS.
In the old db I have an Log-table wich is in this format:
ID,CaseID,Note
each CaseId can have several rows.
Now I want to merge the Note Row into one row based on the CaseID.
So that I get One Row per CaseID with one bignote.
I have been looking into Pivot, but just dont seem to get it working.
Any Ideas?
Hi,
I have a table used in Merge Replication. If i add a new column in that table it gives me an error for tables in which either this table is a key or this table has key from other tables "Error validating the default for column 'rowguid'." Also for views i'm getting an error
"View 'dbo.vw_TestCaseBasic': indexes and schema binding will be removed."
If i proceed further i get an error and m not able to add the column.
"It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
The transaction ended in the trigger. The batch has been aborted."
Is there any solution for the same?
Regards,
Ruchir Jain
Is the maximum number columns per table still 246 for a Merge replication in SQL Server 2005?
Good afternoon,
I am trying to figure out a way to use a columns default value when using a stored procedure to insert a new row into a table. I know you are thinking "that is what the default value is for", but bare with me on this.
Take the following table and subsequent stored procedure. In the table below, I have four columns, one of which is NOT NULL and has a default value set for that column.
CREATE TABLE [dbo].[TestTable](
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[SSN] [nvarchar](15) NULL,
[IsGeek] [bit] NOT NULL CONSTRAINT [DF_TestTable_IsGeek] DEFAULT ((1))
) ON [PRIMARY]
I then created the following stored procedure:
CREATE PROCEDURE TestTable_Insert
@FirstName nvarchar(50),
@LastName nvarchar(50),
@SSN nvarchar(15),
@geek bit = NULL
AS
BEGIN
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, @geek)
END
GO
and executed it as follows (without passing the @geek parameter value)
EXEC TestTable_Insert 'scott', 'klein', '555-55-5555'
The error I got back (and somewhat expected) is the following:
Cannot insert the value NULL into column 'IsGeek', table 'ScottTest.dbo.TestTable'; column does not allow nulls. INSERT fails.
What I would like to happen is for the table to use the columns default value and not the NULL value if I don't pass a parameter for @geek. OR, it would be really cool to be able to do something like this:
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, ISNULL(@geek, DEFAULT))
Does this make sense?
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)
PartsNum
MiniPartsCL
NewMiniPartsCL
1
K
DK
1
K
K
1
Q
Q
0
L
L
0
L
LC
0
D
G
0
S
S
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.
The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,
Beli
I m extracting data from Excel-sheet and inserting into SQL Server 2005 Database using SSIS(SQL Server Integration services).
Having 2 columns in excel-sheet and want to insert those into 1 column of SQL Table.
Can anyone help me about, how can I insert data from 2 columns into 1 column in SSIS. Is Export/ Import column or Copy Column or Merge will work..
Plz reply soon..
Thanks...
Two issues,
1. Even if there is no text entered in the search box of the report viewer header, the next button is clickable and returns the message "the entire report is searched'. Is this a defect in the product?
2. When report is exported to excel, the excel object tries to keep the report formatting to the closest and hence merging the columns in the excel. What is the work around to fix this issue. The end users after exporting to excel would normally do a sort by ascending/descending on required columns and this behaviour is causing an issue.
Hello,
I have data coming in from two sources, one being SQL and the other being Oracle. The end result needs to be a CSV file with the columns in a specific order. I have a Data Flow task setup that takes both sources and does a Merge Join on them. I can add a Sort Transformation and manually set the sorting of all 156 columns that end up going to a CSV file destination. However, I have a table setup that holds the names of the 156 columns and the order that the CSV file expects them to be in. I would much rather do this step dynamically as the column names and order may change in the future. Anyone who has used the Sort Transformation for a large number of columns knows how tedious it can be and how adding a column in the middle will cause you to change the sort # for each of the columns that come after it.
So I added a Script Component between the Merge Join and the Flat File Destination hoping that I could alter the order of the columns there. However I added the following code and found that the SortKeyPosition is ReadOnly.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim column As IDTSInputColumn90
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
column.SortKeyPosition = 1
Next
End Sub
I was hoping to add some code to get the sort index from my table for each of the columns and set it to the SortKeyPosition. Has anyone out there done this before or seen an example that might point me in the right direction? I've searched for 2 days without coming up with much.
Thanks!
How can I merge two cells in the same column?
example
ColmAAAA
cell001
cell002
I want to merge cell001 and cell002