Hello,
I am loading data from our MS Active Directory into our data
warehouse. (check out Mircosofts's Logparser, it can pull data from
ADS, server event logs and more. It can also create text files or load
directly to SQL. Its free and a pretty useful tool)
There is a field that contains the direct reports of a manager. The
direct report users are delimited by a pipe symbol.
I want to breakup the field into multple rows. There can be none, one
or many direct report users in this field.
<disclaimer>
This is a snippet of an example. This is only an example. I know that
I have not defined PK nor indexes. My focus is how to solve a problem
of parsing a field that has multple values into multple rows.
</disclaimer>
Thanks for any help in advance.
Rob
I want to end up with 5 rows, 1 row for each user that is seprated by
the PIPE symbol.
CN=Marilette CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Robert
Camarda,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Lee K,OU=TechnologyGroup
I have a SQL statement that fetches book information via a TITLE_ID which is fine if we only have one edition (hardback), but if there are two editions (hardback and paperback) it will return two rows like:
Title - Author - Edition The Amazing Pixies - A. N. Author - Hdbk The Amazing Pixies - A. N. Author - Pbk
Is there any way to concatenate the Edition field so the two lines become one? I have searched for ways to do this but have had no luck.
Hi I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this..
CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner ( @Crime_No varchar(20), @Unit_ID int ) as begin DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int DECLARE @Defect_ID varchar(100)
select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID
(select TBL_TR_Person_OutsideDetails.OutSide_state from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state],
(select TBL_TR_Person_OutsideDetails.OutSide_City from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City]
---here I faced the problem- /*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/
(SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5)) FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number) select @Defect_ID
FROM tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE where tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID AND TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No AND TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner
Hi,I hope someone here can help me.We have a product table which has a many-to-many relationto a category table (joined through a third "ProductCategory" table):[product] ---< [productCategory] >--- [category]--------- ---------------- ----------productID productCategoryID categoryIDproductName productID categoryNamecategoryIDWe want to get a view where each product occupies just one row, andany multiple category values are combined into a single value, eg(concatenating with commas):Product Category-------------------cheese dairycheese solidmilk dairymilk liquidbeer liquidwill become:Product Category-------------------cheese dairy, solidmilk dairy, liquidbeer liquidWhat is the best way to do it in SQL?Thanks and regards,Dmitri
I am needing to combine the Notes field where Number and date are the same...For example
for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.
I have a table, tblstudents that has the fields strfirstname and strlastname. The table was imported from MS Acess where they were storing the first name and the lastname in the same field. Sucks doesn't it? Anyway, I need to write a script to extract the first name from the lastname and insert it into the first name field which is blank. So far I figure I can use select into, but need to find a way to tell sql server to take the fart of the field after the comma (the first name) and to delete the comma becuase I won't need it anymore.
Help!
Select '%,' into tblclients as strfirstname from tblclients
I know this won't work, but I want to show you all I'm at least trying!
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
Hellow Folks. Here is the Original Data in my single SQL 2005 Table: Department: Sells: 1 Meat 1 Rice 1 Orange 2 Orange 2 Apple 3 Pears The Data I would like read separated by Semi-colon: Department: Sells: 1 Meat;Rice;Orange 2 Orange;Apple 3 Pears I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
Hello, I need some help being pointed in the right direction. I have a field in my Customer table called Name that is "LastName,FirstName". I need to be able to return a result set with "FirstName,LastName". Any ideas?
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
I am trying to build a query on a SQL2000 text field which contains XMLstring. The query is like "select requestnumber from history whererequestnumber is like '%re1%'". As you can see in the following samplerecords, the xml string has database structure and the requestnumber isa node of the XML. I wonder if it is possible to have SQL server parsethis field and allow me to do the query. If not, any suggestion wouldbe appreciated as to how to store XML data in SQL2000. I am not sureif I misused the SQL2000 XML feature correctly. So far I pass the rawquery result to ADO and manipulate it in XMLDOM.The table is to capture history of changes in any record in mydatabase. So I need to keep it simple so any record from any table canbe stored in here. The structure of the table is like this:sysObjectNumber(int, not null)recordKeyValues(char(30), not null)archiveTime(datetime, not null)history(text, null)A sample record would be like the following:sysObjectNumber recordKeyValues arvhiveTime History=============== =============== =========== =======1728725211 ABC 2005-03-25 8:09:56.700<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1</usedby></history>1728725211 ABC 2005-03-28 11:01:14.407<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</usedby></history>1728725211 ABC 2005-03-28 11:46:12.723<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</usedby></history>1728725211 ABC 2005-03-28 11:46:35.273<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</usedby></history>
how to separate names but i cannot make work in this case. The name field might contain anywhere from only one name with no delimeters to five names with four delimeters. I want to replace the delimeter with a space and reorder the names.
Original data format: Name2/Name1/Name3/Name4/Name5. Desired data format: Name1 Name2 Name3 Name4 Name5. Examples of source data
Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally Bush/Jim/Sr/Etal/Trustee
I have a third party application with a ntext field that I need to parse the data out of. The data looks like this: <xmlF><FNumber type="int">2421</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">John Smith</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate</xmlF> The fieldname is Data and the tablename is ProcessData Again, this looks like xml, but the field type is ntext. I would like to create a view displaying the parsed data in fields. How would I go about parsing the data? Thanks.
We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.
Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.
I'm working on a sales commission report that will show commissions for up to 5 sales reps for each invoice. The invoice detail table contains separate columns for the commission rates payable to each rep, but for some reason the sale srep IDs are combined into one column. The salesrep column may contain null, a single sales rep id, or up to five slaes rep IDs separated by the '~' character.
So I'd like to parse the rep IDs from a single column (salesreplist) in my invoice detail table (below) to multiple columns (RepID1, RepID2, RepID3, RepID4,RepID5) in a temp table so I can more easily calculate the commission amounts for each invoice and sales rep.
Here is my table:
CREATE TABLE invcdtl( invoicenum int, salesreplist [text] NULL, reprate1 int NULL, reprate2 int NULL, reprate3 int NULL, reprate4 int NULL, reprate5 int NULL, )
As you can see, some records have trailing delimiters but some don't. This may be a result of the application's behavior when multiple reps are entered then removed from an invoice. One thing for sure is that when there are multiple reps, the IDs are always separated by '~'
We are using a table that may give 1 to and unknown number of data elements (ie. years) .  How can we break this to show only three years in each row.  Since we don't know the number years we really won't know the number of rows needed.  Years are stored in their own table by line.   car make year1 year2 year3 A  volare 1995 1996 1997 a  volare 1997  1998  1999 b toyat  1965   1966  1968
We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.
Hi,I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it. I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns) For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal. <ProductMergeAudit> <before> <table name="table1" description="Test Desc"> <product id="OldVal"> </table> </before> <after> <table name="table1" description="Test Desc"> <product id="NewVal"> </table> </after></ProductMergeAudit>
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
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.
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')
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part | 1 | RAM 2 | Disk Drive 10| CPU 11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive| ---------------------------------------------- A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item SELECT SYSTEM PART FROM System Info WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
