Hi
I have a column say “Col_1� in which “=>� is used as separator.
Col_1 data is as follows
House => Street => Area => City => Country =>
I want to create separate columns for House, Street, Area etc from Col_1 using “=>’ separator.
Please advise how?
Thanks
Jawad
Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text                           QueryPlan       Plan_handle Â
     Name     Value
select id,name,Address from person   <showPlznXML...  010101         Table       Person
select id,name,Address from person   <showPlznXML...  010101         column     id
select id,name,Address from person   <showPlznXML...  010101         Table       Person
[code]....
I have a column with the data as below :-
<Items>
 <Item Value="Value1" />
 <Item Value="Value2" />
<Items>
How to get this data into seperate columns asÂ
Items
value1
value2
How to separate column FullName to three column LastName, FirstName, and MI? Sample of FullName - Smith, John P.
View 4 Replies View RelatedThis seems to happen when the report is not 8.5 x 11 portrait. Any other size or orientation will cause it to put the column on the last page.
Does anyone know why?
I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0
-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0
It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!
Caeanis
any tricks to separate this column from:
"testlname,testfname comment section > comment section testlname,testfname 6/30/2015 9:05:58 AM > testlname,testfname 6/30/2015 9:06:18 AM > comment section testlname,testfname 6/30/2015 9:10:05 AM > comment section"
to:
col1 Col2 col3
testlname,testfname 6/30/2015 9:05:58 AM comment section
testlname,testfname 6/30/2015 9:06:18 AM comment section
testlname,testfname 6/30/2015 9:10:05 AM comment section
Hi There,
Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?
for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?
Is Derived column transformation the best way to do it?
Thanks.
Sam.
IN SSIS...
1.---->I have a sales table country wise regions  like (india, usa, srilanka) ....
india usa
srilanka
a b
c
d e
f
So I want output like in
flat file1.txt has india         flat file2.txt has usa        flat file3.txt has srilanka
   a b
c
   d e
f
2.----->I dont know how many regions in my table....dynamically split into separate flat files ....
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
View 14 Replies View RelatedHow can i format my query so that each piece of data appears on a new separate line? Is there a command for a new line feed? does not work.
thanks.
For example:
a: data
b: data
c: data
a: data
b: data
c: data
I have the following result in my query:
PlanName1 :08/22/2006 - 11/01/2006; PlanName2:03/13/2006 - 08/21/2006;
How do I separate 1 column into 2 or more column?
Hi to all of you,
I am new to SQL I have a problem that I can’t solve.
I have a column with Surname and name (SMITH, James) in one table with data I just need to separate in two columns in one Surname in the other one Name I know how to unite two columns using substrings but not to separate in two columns.
Help is highly appreciated
Hi guys,
How can I separate the data that is alpha numeric in type?
FloorNumber
8A
8B
8C
11A
11B
12
13
14
15A
I need to separate the 8 from A in 2 columns just like ....
UnitLevel UnitCode
8 A
8 B
And if the floor numbers do not have "letters" then let it be.
Thanks.
I want a user to be able to search for names in a table of clientsusing a single parameter, but be eable to use "and" or "or" IN theparameter.Like this:@NameSearch nvarchar(100)ASDECLARE @PartA nvarchar(100), @PartB nvarchar(100)CASE When @nameSearch Like '%' + ' OR ' + '%'SELECT @PartA =(how do I get what's to the left of "OR" or "AND")SELECT @PartB (how do I get what's to the right of "OR" or "AND")SELECTc.UniqueID,c.LastNameFROMdbo.tblClients cWHEREc.LastName = @PartAORc.LastName = @PartBCASE ELSESELECTc.UniqueID,c.LastNameFROMdbo.tblClients cWHEREc.LastName = @NameSearch
View 14 Replies View RelatedI want to know if 64-bit SQL Server 2005 is a separate install. I have been provided with SQL Server 2005 enterprise CD. I tried browsing through the folder but could not find 64-bit install.
I am assuming the CD I have is 32-bit because the system configuration check report had this message "64-bit ASP.NET is registered. Required 32-bit ASP.NET to install Microsoft Reporting service 2005(32 bit)"
The windows server I am trying to install is Windows Server 2003 Standard x64 Edition. I uninstalled the all the client and SQL Server 2005 install. Since my windows server 2003 hardware requirements are compatible for 64-bit SQL server 2005, I want to go ahead and install 64-bit SQL server 2005.
Any help will be greatly appreciated.
I need to do a inner join on tables from two separate databases. I understand that you can do this by using this type of syntax:
select a.col1, b.col2from db1.dbo.tab1 a, db2.dbo.tab2 bwhere a.col1 = b.col2however, how do I reference the two databases in the following code?
Thanks,
Tim
Function GetConnectionString() As StringDim ConnectionString As String = ConfigurationManager.ConnectionStrings("MainWeb").ConnectionString
Return ConnectionString
End Function
Using conn As New SqlConnection(GetConnectionString())
conn.Open()
Dim sql As String
sql = "SELECT CaskInfo.CaskID, CoCInfo.CoCName, AmendmentInfo.AmendmentName FROM CaskInfo INNER JOIN CoCInfo ON CoCInfo.CoCID = CaskInfo.CoC INNER JOIN AmendmentInfo ON AmendmentInfo.AmendmentID = CaskInfo.Amendment WHERE "For i = 0 To UBound(words)
If i > 0 Then sql = sql + " OR "
sql = sql + "(CoCInfo.CoCName + ' ' + AmendmentInfo.AmendmentName) LIKE '%" + words(i) + "%'"
Next
' lblResults.text = sql' Exit Sub
Dim com As SqlCommand = New SqlCommand(sql, conn)
Dim result As SqlDataReader = com.ExecuteReader()
Dim SearchResults As StringWhile result.Read()
SearchResults = SearchResults + result.GetInt32(0).ToString + " " + result.GetString(1) + " " + result.GetString(2) + "<br>"
End While
result.Close()
lblResults.text = SearchResults
conn.Close()
End Using
I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?
View 1 Replies View RelatedHow do you select data from 2 separate databases residing on 2 servers?
Is there a way?
Angel
Has anyone implemented SQL 7 on a cluster(NT 4 Enterprise). It has been running for about 1.5 years with no problems. However, I now wish to manage the transaction log on a separate disk to the data, to increase performance.
I think I would need to create a new Disk resource on the cluster for the transaction log. But am not sure of the consequences during failover.
Has anyone done this, so that the disks containing the transaction log also failover.
There are some articles at the microsoft site, but none deal with managing the transaction log separately on a Cluster.
I want to keep Win98 and my files intact on my C drive then install another hard drive and install MS Back Office (NT 4.0, SQL 7.0) on the new drive. When I want to switch operating systems, I want to restart in dos, go to the new drive and launch NT. I can't dual boot because I have FAT32 file system on my C drive. Has anybody tried this? Do it work?
View 1 Replies View RelatedI am new to T-SQL and triggers
Any help will be appreciated
I am trying to change this code to insert firstname, surname (taken from employee table on db A) to firstname, surname on customer table of DB B but also create cust_id on customer table and DB B. currently I am getting all rows of customer.cust_id filled with the same data whenever a new data is inserted into (firstname,surmname of employee table)
Create trigger gen_cust_id ON employee for insert
AS
Update customer SET cust_id =( SELECT
Replicate('0',(4-DATALENGTH(CONVERT(varchar(10),i.id))))
+ Convert (varchar(10),i.id)
+ Substring(i.lastname,1,3)
+ Substring(i.firstname,1,1)
from employee C INNER JOIN inserted i on i.id=c.id)
from employee C INNER JOIN inserted i on i.id=c.id
Saad
I have a table with accountid and labtype
However inside of the labtype is data separated by semicolons like below
GIV;APS;LSL. What would be the best way to return this data as multiple rows such as
acctid lab
100 GIV
100 APS
100 LSL
Thee may be from 1-10 values in the labtype field
I recieved a SQL Server table that was supposed to have just the firstname in a field, but actually has firstname and middle name.
Example David Michael
Carol Anne
Is there a way in a query to look for the blank space and separate the names?
I simply need to separate the string for each row by the & character and then I'm assuming i ll be able to COUNT and GROUP BY the occurrences of each separate value in order to find the most commonly used inputs.I have a column Variables in the table Functions, that contains a string of values separated by the & character that shows the inputs each student inserted into a function.
How would I go about splitting that string without the use of a function or stored procedure and the find the most commonly used variables? (I was thinking the latter part could be easily solved with a COUNT(*) and appropriate GROUP BY.)
Example of data:
StudentID FunctionName Variables
1 Example1 Var1=10&Var2=xy&Sign=True&Role=False
Hi,
i have a web application (iis 6) and a databasae. web application is using windows authentication. when iis and the database are on the same server the application is running fine and can be accessed from clients. but when i moved the database to another server it stated giving the error([DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied ) only from clients from IIS server its working.
- when using in iis windows basic authentication its working but integrated Authentication giving the error.
- web application pool is using a domain/user that has access to the database
- All servers are win 2003
<add key="ConnectionString" value="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=EFILING;Data Source=RPDHO07;"/> i tried also to use the username and password in connection string same problem
Please help this puzzled me aloooot :@
I have two tables
Books
-BookID
-CustomerID
Magazines
-MagazineID
-CustomerID
How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:
CustomerID, BookCount, MagazineCount
In Enterprise Manager, is there a way to group Databases into Separate folders?
View 6 Replies View Relateddeclare @filter varchar(100)
set @filter = '10,''firststring''||10,''secondstring'''
declare @tbl table
(id decimal,
name varchar(20))
insert into @tbl values (substring(@filter,0,patindex('%||%',@filter)))
hai in the above exmaple, i recieve input value (@filter) as concated string . pipeline(||) is my delimiter..
i want to split the string based on this delimater and need to insert into @tbl..
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
What is the error in this. i believe i can do this way to insert to concatinated values.
Help pls
I have two databases with multiple tables. Same tables same fileds.Both databases contain records, but they should not match each other.(I will run a report for matches before this and delete any data thatis not current to ensure this is the case)How do I go about moving data from one of the databases to the other inorder to create a single database with all the data? Unfortunately mySQL is limited and I cannot image how I would work around the uniqueids. Append or update maybe?Thanks in advanceAndrew
View 1 Replies View RelatedHi,
can anyone tell me, how does Reporting Services work in the following scenario:
There is a report with multiple pages (eg. 50 pages). The user runs the report.
Question: What is transferred from the Reporting Services server to the client desktop ? all pages or just the first one ?
Background> I don't want to let the user wait until all pages are received (because he may be interested only in the first one and the jump to the last one and that is all he wants to see)
Any ideas are appreciated.
I'm fairly new to RS and have a problem.
I have a query that pulls information on people, courses they have taken, their score and so on. I have a date parameter setup so I can run it by year. Everything works ok on the query side, I get all the information I need on all the people and the courses they have taken. However, when I run the report, I do not get a separate page for each person and their relative information. The first page shows the first name and the rest of the 700 pages list all the courses and other information, with no break. How do I render the report so that I can get a separate page(s) for each person and their specific info? I can glady provide more info/code if need be.
Bill
I'm writing an application where I have a class which for example represents a User and I have a UserDAO class which holds all the calls to the database. When I'm trying to create a user, I call my User class pass in the username and password from the login box. Then in my UserDAO I pass these details to a stored procedure to query the database. In this method in the UserDAO I open a SqlConnection call the stored proc and set the results to a SqlDataReader. Then I return this SqlDataReader object which I've created.In my User class the method I populate the user class properties with create a SqlDataReader based on the method on the UserDAO method.My problem is first how can I close the connection I originally created in my UserDAO, as I'm returning a SqlDataReader object I can't close my SqlConnection object before returning in the method. And I can't close the SqlConnection in my User class.Is there another way to separate all my DAO methods in a separate (UserDAO, SecurityDAO etc), but call them in my main classes (User, Security etc).ThanksStephen
View 1 Replies View Related