XML To Cols
Oct 5, 2006I have a XML column in my source DB. Is there any way possible using SSIS to convert the XML to ordinaty cols (varchar) in the destination db.
I am using Sql 2k5.
thanks in advance
ADVERTISEMENT
Query From Two Cols !?
Jun 27, 2007my table
id; part_name ; part_desc
01; xpto ; descr
02; qwerty ; azerty
03; azerty ; descr xpto
I want to search from two columns
something similar to :
SELECT * FROM tools WHERE (part_name LIKE @parameter_search or part_desc LIKE @parameter_search);
If i search for "xpto" only the first line would show.
How can i solve/go around this?
Distinct Cols Of More Than One
Aug 30, 2005Hi
There is a table which has repeated values in more than one col.
I want to view all the distinct values for both the cols
for eg
col1 col2 col3
A B D
A B E
B C E
B C F
I want the output as
col1 col2
A B
B C
cheers
Vic
Vicky
How To: Find Cols In A Table
Jan 12, 2002I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine what cols exist in the table?
AND, what is the syntax for such a query?
I will be receiving data back where I will receive the accountID and only the deltas will have values all other columns will be null.
My proc will update an existing record, updating the specific col when an accountID exists, it will create a new record if the accountID does not exist.
I'd like to search all the cols getting their names and values when not null.
I can construct a proc for each table searching each col by known name.
However, I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine the cols?
All my tables start with cols AccountID and end with RecID with any number of cols inbetween.
TIA
JeffP...
cross posted to ms.pub.mssqlserver.programming
Two Identity Cols In Table
Jul 14, 2006Hi,
How can we use two identity column (with identity property) in table ?
GK
gk
How To Simply Join Cols Together?
Jun 5, 2007I have about 30 tabs with same struture.
I want to simple put column 2 in all 30 table in a new table without joining on anything. Is there a way to do that?
Thanks
JP
Display Row Data As Cols
Jun 6, 2006I have data in rows that I need to aggregate and display in a columnar fashion and I haven't been able to figure out how to do it. A simplified version of the data as it is stored in the table:
Station Month Day Reading
1 1 1 100
1 1 2 200
1 1 3 300
1 2 1 400
1 2 2 500
And I would like to create a query that returns:
Station Month Day 1 Day 2 Day 3
1 1 100 200 300
1 2 400 500
Any help you can provide or tips to steer me in the right direction are much appreciated.
Dianne Siebold
Query Single Col Into Several Cols Result Set
Oct 24, 2004Hello,
I have a table with 1 column containing numeric values, and I need to create a query which displays the count of values for each set of conditions. For example, if the table contains the values 1,2,3,4,5 and 6, the output of the query should look like this:
L M H
-----------------------
3 2 1
(L are values below 4, M between 4 and 5, H is 6 and above)
Any suggestions?
TIA
How Can I Get Primary Key Col And Other Two Cols In The Same Table Become One To One Relationship.
Jun 12, 2006For ex.
Table Match_List ( MatchID, UserID_A, UserID_B)
constraints like
MatchID primary key
UserID_A <> UserID_B
unqiue index (UserID_A,UserID_B)
but I wish to exclude duplicated rows like 1,1,2 & 2,2,1,
cause UserA to UserB, and UserB to UserA are the same thing.
How can I get this?
SQl Query... Multiple Cols To Single XML.
Oct 12, 2006Hi I have a table which has couple of name cols and 10 varchar columns. The requirement was to retain the first two cols but convert the 10 cols into a single XMl.
the table has this schema;
ManagerName varchar
UserName varchar
TextField1 Varchar
TextField2 Varchar
TextField3 Varchar
TextField4 Varchar
TextField5 Varchar
TextField6 Varchar
TextField7 Varchar
TextField8 Varchar
TextField9 Varchar
TextField10 Varchar
I wrote a query like;
SELECT
TPD.[NameId]
,CrtdUser.[UserId]
from [Olddb_DB] TPD
join
[NewDB]..[Manager]CrtdUser
on
Crtduser.managerName = TPD.ManagerName
join
[NewDB]..[Users]Users
on
Users.[loginDetail] = TPD.[UserName]
to get the cols other than the XML
/*********/
SELECT
XmlDat.[textField1],
XmlDat.[textField2],
XmlDat.[textField3],
XmlDat.[textField4],
XmlDat.[textField5],
XmlDat.[textField6],
XmlDat.[textField7],
XmlDat.[textField8],
XmlDat.[textField9],
XmlDat.[textField10],
XmlDat.[textField11],
XmlDat.[textField12]
from
[olddb_db]..[DETAIL_DEF] XmlDat,[NEWDB]..[Detail_Def]TPD
where TPD.Name = XmlDat.Name
for XML AUTO, ELEMENTS
and this query to convert all the varchar cols to a XML. Now the problem is I am not sure of how to combine both these queries to Insert into the new table.
Can someone guide me in this
the destination table looks like
Name (Varchar)
UserId (int)
UserVal_XMl (XML)
Thanks in advance
Joe Celko Nested Set Model: How To Compute The Lft And Rgt Cols
Jul 3, 2006Hi,
With reference to http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427
I want the "sql stmt" which wud give the lft and rgt col values..
i am reading his book but cant understand :eek: where he explains
wat lft and rgt cols are..
"The root is always (lft,rgt) (1, 2*(Select count(*)from table) and leaft nodes are (lft+1=rgt)" :S
How To Widen Subtotal Col Without Widening Data Cols?
Jul 28, 2007
Hello ,
I need to make the automatic subtotal column in most right position in a matrix wider than the data columns , i faild so far and discovered that its width is proportional to the data column width , its logic for the total column to be wider since its value is sum(all left data columns)
any way around this ?
thanks
Bassam
Finding Mismatched Rows Between Identical Tables Based On 2 Or More Cols
Jun 8, 2007CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
View 6 Replies View Related