I have a column that has text delimited by a percent sign that I wish
to turn into rows.
Example:
A column contains ROBERT%CAMARDA, I want to turn that into two rows,
one row with ROBERT and antoher row with CAMARDA.
I will have source rows that have zero, one, or many percent sign
delimiters that will correspond to that many rows (One percent sign
will create 2 rows, 2 percent signs will create 3 rows and so forth).
Any thoughts?
TIA
Rob
Does anyone have a routine that takes a row of data from database, duplicates/triplicates it, appends some information to it and writes it out as 2/3 CSV rows.
Basically I need to do the following.
Selected from database:
Row1 Col1 Col2 Col3
Output:
Row1 Col1, Col2, Col3, abc
Row1 Col1, Col2, Col3, def
Row1 Col1, Col2, Col3, ghi
Thank you
I currently have a Name column in one of my tables that is in the following format:
Name
Doe, John P
and I need to break this down into three separate fields in a different table:
Last_Name First_Name Middle_Name
Doe John P
Could someone please help me with this issue?
Thanks,
Peter
I’m okay with simple queries but as I’m no expert and have failed to find perhaps the correct wording to describe this method, if at all possible to do, so I have come to ask here.
What I would like to do is take a column from a query and then break down that column into separate results.
So the full query results:
36,18/09/2007 10:00:00,NULL,000102000304,NULL
The column I would like to brake down is (Unique Reference Number):
000102000304
And I would like to break it down to get the last 2 parts (0003 and 04):
0001 | 02 | 0003 | 04
Is this possible to do?
If so where should I be looking or what should I be looking at?
Many Thanks
Hi DBA's,
I need to break up a 34 byte column into several columns. Here is my problem.
Table A:
Col1..Col2..Col3..Col4..Col5..Col6..Col7..Col8..Co l9..Col10
Col 9 is char (2) and Col10 is char (34). It is this column that needs to be broken up into several columns depending on the value in Col 9.
Col1 to Col3 is the key to the record.
So say if record 1 has Col 9 value 'AA' then Col10 ( 34 bytes) is to be spilt into 10+10+10+4 (four columns).The value 'AA' can repeat for several records and the value in Col 10 can change for the same value 'AA'.
Now say record 27 has Col 9 value 'BB' then Col 10 is to be split as 5+25+4 (3 columns).
There are 15 such unique values of Col 9. I have the file layouts for Col 10 for each distinct value of Col 9. So using the file layouts and Table A which exists in my database how do I proceed.
Need I make 15 tables ( one each for the 15 unique Col 9 values). These structure
Col1..Col2..Col3...Col9 (the key fields and Col 9) will be common to every table. Plus the file layouts will serve as additional columns specific to each of these tables.
Kindly help.
Vivek
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies View Relatedsuppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. Â To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.Â
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1Â
Hello,
I need to concatenate a column from multiple rows into a single column in a new table.
How can I do this?
SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR
Need to have
SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR
THANK YOU
Hi I'm having a bit troubble by creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:
Pricecalcid Date Price Itemid
2000 2006-12-12 3000 100
2488 2007-10-11 2800 100
3100 2008-08-07 2500 100
What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.
How do I do that?
Thanks
Morten
I've got a Table that has over 500,000 row in it. Now I need to convert the whole thing into Excel to import into another application. So I need to break the table into 10 different tables. How can I do that?
Thanks
I hope I can get this across clearly.I have a table that needs to be broken into 3 tables.Col1 Col2 Col3 Col4 Col5 Col6 Col7Col1 and Col2 need to go into LookupTable1Col3 and Col4 into LookupTable2If Col5 is twice the width.... haha just kidding...so Col5 and Col6 go into LookupTable3There is a 4th table which is made up of foreign keys which are the PK ofLookupTable1,2,3My questions is, how to get the data from the columns of each row and add itto its respective lookuptableand sequentially step throw the table to repeat the above step until I'veprocessed each rowthanks folksT.B
View 3 Replies View RelatedWhat is the best way to page break and I am having trouble. I am building a form based report and need to always break after the last field.
View 3 Replies View Relatedright now we have 2 node cluster . so if i break a cluster it will hamper or not for SQL server. Can you please give me suggestion
View 2 Replies View RelatedIs it possible by any kind of workaround to break the 8kb limit on user-defines datatypes?
My datatype can contain an arbitrary number of double-precision points meaning that I in best case only can store 512 points (2 x 8 x 512). there's a few extra bytes used for something else, but this is roughly the maximum, which is far from what I in many cases need. I serialize the object myself to ensure that I only store what I really need.
I have been working on a pretty ugly stored procedure recently, while debugging I added a char(10) to the end of each line of the SQL query so I could copy it to query analyzer(QA) and debug the SQL syntax output from of the stored procedure.
It had no effect on the stored procedure working, but when I copied the query to QA it got the error below, so I removed them all and added them in one line at a time to find the problem.
--Server: Msg 170, Level 15, State 1, Line 3
--Line 3: Incorrect syntax near ','.
Below are the 2 querys, the only difference is the Char(10) between Amt6 and Amt7!
http://www.rakbiz.com/download/broken.txt
http://www.rakbiz.com/download/working.txt
I also tried char(13) with same results.
any ideas why this is happening or how to add line breaks for readability without this problem?
Thanks for your help!
Hi, I have a report that is frustrating me. I've built this report, and it is not yet used in production. What it does is page break in places that I don't understand.
The FIRST area it would break after a line that had wrapped text to the line below. Even though there was plenty of room to fit it, and the entire rest of the group on the page.
The second area I honestly have NO idea why it breaks.
On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far. SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3], [column4], [column5] FROM [table] WHERE [column4] = 'condition4' AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate ", Conn) ; cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text)); cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text)); cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text)); I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help.
View 2 Replies View RelatedI want to show a third-level overview of how many to-do items are in various statuses (pending declined and completed) for the third level individuals.
In this example there's 2 regional leaders (level3), each with local leaders (level2) and individual team members (level1)
Status "1" is a new or pending to-do item
Status "2" is a declined item
Status "3" is a completed item
Here's what I want the output to look like (I may need to add columns for the final report but it should look as follows):
LEVEL3 | Status 1 | Status 1 | Status 2 | Status 3 |
LEADER | TOTAL | 2012 | TOTAL | TOTAL |
-------|----------|----------|----------|----------|
paul | 3 | 1 | 1 | 0 |
roger | 1 | 1 | 1 | 0 |
Here's some sample data similar to my live DB:
CREATE TABLE #items (id int, datestamp datetime, userid int, status int)
INSERT INTO #items (id,datestamp,userid,status) VALUES (1,'12/26/2011',1,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (2,'12/26/2011',2,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (3,'1/2/2012',4,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (4,'1/3/2012',1,2)
[Code] ....
I have a view called hierarchy that shows me the hierarchy for each level 1 user. Here's the source for the view:
SELECT
a.id 'level1',
b.id 'level2',
c.id 'level3'
FROM #users a
INNER JOIN #users b ON a.reportsto = b.id
INNER JOIN #users c ON b.reportsto = c.id
I've run into table/view limits when using a straight query and timeouts when trying to create a cursor that looks through all level3 users and populates a temporary table with an INSERT and multiple UPDATE statements to get the individual counts for each column for each user.
(Just a note on the actual database in case it's relevant: It has about 1000 LEVEL1 users, 50 Level2, and 15 Level 3 users. It will generate about 1400 to-do items per week.)
Need query to expand the count from a month to the entire year in 2013 .Below is my query where I got the values for a month in 2013 ,How do I expand on this query so that it generates for the entire 2013
Query
SELECT RD.RPTDESC,Count(SR.RPTDESC) AS ReportCount,sum(SR.Hrs) as ProdHours,Sum(SR.Mins) as ProdMins, (sum(SR.Hrs)*60+ Sum(SR.Mins)) as TotalProdTime,
(sum(SR.Hrs)*60+ Sum(SR.Mins)/Count(SR.RPTDESC)) as AverageProdTime,sum(SR.TriageHrs) as TriageHours,Sum(SR.TriageMins) as TriageMins,
(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)) as TotalTriageTime,(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)/Count(SR.RPTDESC)) as AverageTriageTime
[Code] ....
Results
Jan-13Feb-13
RPTDESCReportCountProdHoursProdMinsTotalProdTimeAverageProdTimeTriageHoursTriageMinsTotalTriageTimeAverageTriage
TimeReportCountProdHoursProdMinsTotalProdTimeAverageProdTimeTriageHoursTriageMinsTotalTriageTimeAverageTriageTime
20506060120252558014014017039394
[Code] ...
Also when I am running for months individually there are certain months where for certain reportids there is no data returned,I would like to populate all zeroes for that row for example report id 306 in February and how to get decimal values in average fields.
Hi GuysI don't want to keep asking for your help all the time on each individualquery, so could you please help me to break the myths on the following:1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.ACCOUNTS includes all of the usual details for customers and suppliers, egthe account code (as ACCOUNTID), the account name (as NAME), etc.ACCOUNTBUDGET basically holds a transaction line for each month and it'scorresponding year to store the turnover for that month, eg one row containsACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.Now a lot of the SQL 6.5 tables that I deal with are in this vein and theusual query is that I want to list all of the ACCOUNTIDs and NAMEs thatexist in the ACCOUNTS table and then show for example what their TURNOVER isfor a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGETtable.Now if I do a quick query using MS Query all I get are rows that haverelated values in both the ACCOUNTS and ACCOUNTSBUDGET table when I havespecified say a certain PERIOD and YEAR.The main point of my current reporting problem is that I want to show allthe ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for aparticular PERIOD and YEAR.I'm positive that I have to create a 2 step query/join, but I don't know howto do it. What is the method? People in this NG, can rattle one up inseconds, but I just don't see the logic. Can you help me with this queryand let me know how you manage to fathom it.2) Are there any good web sites that explain in kiddie form how to do thissort of thing?I really appreciate your help on this.RegardsLaphan
View 1 Replies View RelatedHello,
I'm using SQLServer 2005 and I build a report in the visual studio.
I need a way to pass as parameter whether to break at start of a specific group or not.
Thanks, Talia.
Hi all,
I'm having a problem and I cannot seem to find how to fix this anywhere on the web.
I have a stacked bar chart with my data labels showing down the x axis. My client wants me to put a bit of white space between the label and the actual chart. So I tried adding spaces to my data label but of course RS thinks they don't exist since they are not non breaking spaces.
How can I do the following in my chart label
=Fields!Myfield.Value & " "
As you can see I want to add a couple of spaces to my data label. I can't use , all the ampersands get confused. I tried & char(32) but RS doesn't like char(n). I tried ChrW(160) [I saw this in the web somewhere] but this didn't work either.
Is there anything I can do?
thanks in advance, I'm at my wits end (again)
Annabel
Hey guys,
I'm wondering how most people manage very very large backups. What is the best approach to breaking up the backup files if you're restricted to a drive size (450gig in my case). I unix, you can pipe the backup to gzip and split, I'm not sure how the same thing could be accomplished in windows.
Thanks,
-Kilka
Hi,
I have a text file (5 MB). It appears as a single line in a text editor. But actually it has records of 1500 byte length each.
I want to strip it down to 1500 byte records. So 1500*3500 = 5 MB (approx). The record size is always 1500 bytes.
Does anyone have a script that I can run on this file to achieve this break.
Thanks
In my database there is a text field type that is used to enter streetaddress. This address could be a few lines long, each line with acarriage return at the end.Is there a way to search for these carriage returns and break out whatis in each line seperately?Thanks.Mike
View 1 Replies View RelatedScenario:
We run a multiple database environment, with two of the databases receiving most of the user activity. (both write and read). These databases are roughly 25gb each and receive roughly the same amount of activity. Currently both of the .mdf files sit on the same drive shelf. Their log files are located on a separate drive shelf.
Debate: We have an extra fiber channel shelf available for us to use. We are not having too many problems related to performance, but we are always seeking for different ways to increase application/server performance. The debate centers on what to do with the extra shelf. There are two different suggestions on how best to use the shelf. They are:
1)Separate the .mdf files for two most utilized databases. This would separate the databases and the I/O associated with each across two different shelves
2)Break off the indexes for all 5 databases on to the extra shelf. This would leave all the .mdf files on the same shelf, but it would move the I/O associated with the indexes to a different shelf.
Can anyone provide the pros and cons of either suggestion?
I would like to see arguements for either side.
Dave
We are running SQL Server 2012 on Windows Server 2008. Just wondering what type of actions would break the backup-chain or backup sequence? For instance, if you have tlog backups being taken every 10 minutes and you stop the SQL Server Service for say 30 minutes. Would this action break the backup chain? Or would everything return to normal once the SQL Server Service is restarted?
View 7 Replies View RelatedCreated a SP that uses system function XP_SENDMAIL. I wantto be able to send a HYPERLINK in the email. TheHYPERLINK is created dynamically and generally long in length (exceedsdefault width of 80 characters) and when rendered in the email is splitacross 2 lines. The problem is that when you click on the link itdisregards the part of the link that has been split onto the linebelow.Does anyone know a solution to this - how to extend the width of theemail to wider that 80 characters so that the link is not split over 2lines? I know that you can use the @width parameter when placing themessage in an attatchment, however I want the link to be placed in thebody of the email and not in an attachment.Any help is much appreciated..
View 2 Replies View RelatedWhenever I make a breaking change to a custom SSIS component/tasks and update the Assembly Version, it seems to break my packages beyond repair, telling me it can't load the task:
Error loading Package1.dtsx: Error loading a task. The contact information for the task is "". This happens when loading a task fails.
All of the properties of said task now show:
Could not get value for property 'c-155-designer-name'. Specified cast is not valid.
Typically, a "breaking" change when it comes to code just means that you need to update your components to adhere to the new contract of the updated signatures. But with SSIS, it seems the only solution to this is to completely remove the component, and re-add the new version, and re-enter all of the property values/expressions. If I have a package containing 10 instances of a task that only had one property removed, for example, this results in a very time-consuming process of fixing my package.
So my questions:
1) Am I doing something wrong in my versioning/deployment that is causing my packages to unnecessarily break?
2) If this is just "by design" and the way it's meant to behave, what is the best practice for making breaking changes to custom tasks/components used by many packages? Should I just never change the assembly version, even when it is a breaking change (this seems to be less disastrous)?
3) As a last resort, if I'm stuck with having to fix the broken tasks, is there a better way to fix them rather than having to completely remove them, re-add them, and re-set all of their properties/expressions?
Thanks in advance.
Jerad
I'm performing SQL Server native backups and Comvault Backups are breaking the backup chain?I can work around them if they only perfume 1 backup per day.We have a 3rd party hosting our servers so I do not have much control as to what they do.I have concerns about being able to get my databases Comvault being restored.
View 2 Replies View Relatedhi
i have a query that returns 1 column 5 rows which i want as 1 row 5 columns
SELECT columna
FROM atable
would become??
thanx
Liju
How to put two rows of data in one column?
from
dealID dealNO
1 888
1 999
to
dealID dealNO
1 888,999
you can't use create permission, so can't create storedprocedure.
Pls help.