SQL View To Split Rows In Single Table...
May 24, 2007
I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for
I have a table is MSSQL 2000 that looks as follows:
Code:
id custnum b1_email b2_email b1_sub b2_sub
------------------------------------------------------------------------
1 123456 b1@host1.com b2@host1.com 0 0
2 654321 b1@host2.com b2@host2.com 1 0
3 321654 b1@host3.com b2@host3.com 0 1
Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows:
Code:
custnum email sub
----------------------------------
123456 b1@host1.com 0
123456 b2@host1.com 0
654321 b1@host2.com 1
654321 b2@host2.com 0
321654 b1@host3.com 0
321654 b2@host3.com 1
Any help would be great! I imagine some sort of join command can be constructed using a single table?
View 2 Replies
ADVERTISEMENT
Feb 27, 2012
I have a table which looks like
Low High
-------------------------
cx01 cx04
sn05 sn08
I need output like
Result
-------------
cx01
cx02
cx03
cx04
sn05
sn06
sn07
sn08
How to get this output using sql query?
View 8 Replies
View Related
Jan 30, 2015
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
View 1 Replies
View Related
Mar 14, 2006
I imported all rows of my txt file using SSIS 2005 into a table. I am now trying to figure out how to split out the header, payment rows, and maintenance rows. First, some information.
An example of table results is here:
http://www.webfound.net/split.txt
The table has just one field of type varcha(100) because the incoming file is a fixed length file at 100 bytes per row
The header rows are the rows with HD in them...then followed by detail rows for that header (see here http://www.webfound.net/rows.jpg).
I need to
1) Split out the header into a header table
2) Split out the maintenance rows (related to the header) into a maint table
3) Split out the payment rows (related to the header) into a payment table
I'll need to maintain a PK/FK relationship between each Header and it's corresponding maint and payment rows in the other 2 tables.
To determine if it's a payment vs. maintenance row, I need to compare chars 30 - 31. If it contains 'MT' then you know it's a maintenance row, else it's a payment row.
How in the hell do I do this???
View 4 Replies
View Related
Apr 17, 2014
I have a string which is delimited by commas:
Green,Red,Blue,Orange,Black,White
I need to create a SQL statement to read the string, split it at the "," character, and insert it into individual rows. I also need to insert an ID (the same for all split values).
For instance:
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (1, 999, Green)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (2, 999, Red)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (3, 999, Blue)
etc...
I would like to be able to do this all in one statement so I don't have to break it out into individual SQL statements.
View 4 Replies
View Related
Mar 20, 2008
What's the answer for this and how ? If it is yes then How ?
View 4 Replies
View Related
May 24, 2007
Hi All,
I was wondering is there any way by which i can split a single row in my table into two rows in my table in design of my report. In my table there are 16 rows i cant design table with 16 rows as it will increase size of my report body & it will give problem while exporting. Instead what i want to do is , design a table with two header rows first header with first 8 columns & second with remaining 8 columns.
The table should look like this
//column 1 2
ID NAME
AGE SEX
-----------------------------------------------------------------------------------
2 abc
10 M
-----------------------------------------------------------------
3 def
20 M
Is it possible
Thanks
-- Darshan
View 4 Replies
View Related
Dec 15, 2007
DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2
View 1 Replies
View Related
May 14, 2004
I was wondering if anyone had a suggestion as to how to delete duplicate rows from a table. I have been doing this:
SELECT * INTO TempUsersNoRepeats
FROM TempUsers2
UNION
SELECT * FROM TempUsers3
This way I end up with a total of four tables (the fourth table being the original Users table) and I was hoping that there was a way that I could do this all within the the original Users table and not have to create the three TempUsers tables.
Thanks,
Ron
View 5 Replies
View Related
Aug 1, 2014
I have a table named LEDGER
LEDGER has two columns named PATID and CODE
Example data:
PATID CODE
1 Z1110
1 D3330
1 Z0330
2 Z1298
2 Z0987
2 Z0330
2 D1092
I need a query that returns PATID if they have CODE Z0330 but not Z1110. I only want one PATID to return if this condition exists.
View 5 Replies
View Related
Sep 19, 2007
Hi again,
I'd appreciate advice on the following. Thanks very much!
Given this Table:
family|product|type
fam1 |abc |XX
fam1 |def |YY
What query would return this?
Family|Type1|Type2
fam1 |xx |yy
--PhB
View 8 Replies
View Related
Jul 20, 2005
To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you
View 3 Replies
View Related
Jul 20, 2005
I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.
View 22 Replies
View Related
Sep 20, 2007
Hi,
I have 3 tables with the follwing schema
Table <Category>
{
UniqueID,
LastDate DateTime
}
Assume the follwing tables with data following the above schema
Table Cat1
{
1, D1
2, D2
3, D3
}
Table Cat2
{
2, D4
3,D5
4, D6
}
Table Cat3
{
1, D7
3,D8
5,D9
}
I have a Master and the schema is as follows
Table master
{
UniqueId,
Cat1 DateTime, -- This is same as the Table name
Cat2 DateTime, -- This is same as the Table name
Cat3 DateTime -- This is same as the Table name
}
After inserting the data from all these 3 tables, I want the my master table to look like this
Table Master
{
UniqueId cat1 cat2 Cat3
------------ --------- ------- -----------
1 D1 NULL D7
2 D2 D4 NULL
3 D3 D5 D8
4 NULL D6 NULL
5 NULL NULL D9
}
Please remember the column names will be same as that of table names
can any one pelase let me know the query t o acheive this
Thanks for your quick response
~Mohan Babu
View 8 Replies
View Related
Oct 25, 2007
I am using SQL 2000
I am getting a syntax error when I parse this sql script:
insert into Elec_Sub_Test1
values ('10-20-2007',35),
('10-21-2007',24)
What is the correct syntax to insert mutlipe rows in a single table.
View 4 Replies
View Related
Nov 17, 2014
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
View 4 Replies
View Related
Jul 16, 2015
We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.
Below is the Table Structure.
Table Name IN_USE_FG
CUST_D 0
PROD_D 0
GEO_D 0
DATE_D 0
Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.
Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.
View 6 Replies
View Related
Mar 11, 2008
Hi, my name's John and this is my first post here at SQL team. I've learnt a lot from the forums here, never needed to post though. I'm pretty good at sql, but not that good and I think for the first time in couple of years I now have a query that I'm completely unable to create.
Let me explain the setup.
I've got three tables e.g.
Products (productid, Name)
100 Computer
200 Printer
300 PDA
ProductActions (ID, Name)
6 changed casing
8 Changed motherboard
7 replaced cd-drive
ProductPerformedActions (productid, actionid)
100,7
100,9
200,8
This basically shows a simple setup showing that product (computer) has been done the action "repaired" and product (printer) has been done the action "changed motherboard". The query i'd like to have is where I can select all products that have not been performed on cerrtain actions. e.g. a list of products that have not been performed on all actions, such as
UnPerformedActionsList(product,actionid)
PDA,6
PDA,7
PDA,8
Printer,7
Printer,6
Computer,8
I know its foundation is something like this:
select productid from products where productid not in
(select productid from productperformedactions....
which elimiantes products that have been performed an action on, but it also removes it for ALL actions,
which i'm trying to avoid. All i want is kind of a cross join, listing all actions, then inverting it, to show
all products that have NOT had it performed on them.
I hope this makes sense.
Thanks
John
View 5 Replies
View Related
Jul 30, 2000
Help, is something wrong with my SL Server? I am unable to return any rows from all tables in all databases (user and system)on My SQL 7.0 SP2 machine. Whne i right click on the table in E.M and select design or open table i get no results. Does anyone know why this is happening? It did not always happen either.
Thanks
View 1 Replies
View Related
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Jan 19, 2015
I have a simple query that joins a largeish fact table (3 million rows) to a view that returns 120 rows. The SKEY in the view is returned via a scalar function. The view returns instantly if queried on it's own however when joined to the fact table in the simple query below results in a query execution plan that runs forever. Interestingly if I change the INNER JOIN to a LEFT OUTER JOIN the query returns the matched results almost instantly.
Select
Dimension.Age_Band.[10_Year_Age_Band],
Count(*)
From
Fact.APC_Episodes
Inner Join Dimension.Age_Band ON
Fact.APC_Episodes.AGE_BAND_SKEY = Age_Band.AGE_BAND_SKEY
Group By
Dimension.Age_Band.[10_Year_Age_Band]
I know joining to a view using a column generated by a scalar function is not a good recipe for performance. I also know that I could fix this by populating a physical table with the view first as I have already tested this though I hoping not to have to go down that route.
Why a LEFT OUTER JOIN works and not an INNER JOIN or anyway I can get the query optimizer to generate an execution plan that works?
View 9 Replies
View Related
Jul 18, 2006
I have an input file with fixed-width columns that I want to import into two tables.. 5 of the input columns go to 1 table and the remaining 15 go to another table. What's a good way to do this in SSIS?
TIA,
Barkingdog
View 3 Replies
View Related
Jan 13, 2005
Hi
This is probably a very basic question for most people in this group.
How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure.
Here is an example of what i want to acheive
FName
John?Doe
FName LName
John Doe
thanks for the help
prit
View 7 Replies
View Related
Oct 21, 2015
I have an input parameter of an SP which value will be passed with different combinations with 2 seperators (comma and pipe)
Value to the parameter is like this : '10|22|microsoft,20|25|sql,30|27|server,40|29|product'
I want output like this
Column1 Column2 Column3
10 22 microsoft
20 25 sql
30 27 server
40 29 product
Pipe separator is for column and comma separator is for row.
I know if its a single separator, it can be done with function but how to do if its 2 separators?
View 6 Replies
View Related
Aug 31, 2000
I am using DTS and VBScript in DataPump tasks in order to transfer large amounts of data from text files to an SQL database.
As the database uses a normalized schema, there is often the case of inserting multiple records in a destination table from various fields of the same record of the source text file.
For example, if the source record contains information about goods sold like date, customer, item code, item name and total amount, and does so for a maximum of 3 goods per sale (row), therefore has the structure:
[date], [custid], [code1], [name1], [amount1], [code2], [name2], [amount2], [code3], [name3], [amount3]
trying to transfer that record to a [SALES] target table (in a normalized database), we would have to split each source record as follows:
[date], [custid], [code1], [name1], [amount1]
[date], [custid], [code2], [name2], [amount2]
[date], [custid], [code3], [name3], [amount3]
What is the best way to do this using DTS?
I have tried using a datapump task and VBScript, and I guess it has to do with the DTSTransformStat_**** constants, but none of those I used seems to work
Vasilis Siatravanis,
siatravanisv@interamerican.gr , vasilliss@hotmail.com
View 6 Replies
View Related
Jul 8, 2015
I got a table with organisation codes with postcal code ranges, from-to.
Example:
Organisationcode, startpostalcode, endpostalcode
001 52005249
I would like to generate rows for this range like this:
001 5200
001 5201
001 5202
.....
001 5249
The table looks like this:
SELECT OrganisationCode, PostcalCodeStart, PostalCodeEnd
FROM dbo.DimOrganisation
View 4 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Aug 21, 2007
not sure if this is possible...
but lets say i make a select like
select products, stock from table
and my rs is
chair | 1
couch | 3
lamp | 2
is there anyway in the select to make any row that has stock of more than 1 to make a new row... so my rs would come back as
chair
couch
couch
couch
lamp
lamp
Any info would be helpful...
Thanks,
~ moe
View 4 Replies
View Related
Sep 30, 2015
I have a delimited text file with 650+ columns. The sum of the column lengths of a single row, if fully populated, exceeds 30K bytes. The "killer" fields lengthwise are the "Description" fields. If they were removed from the input file, the remainig columns would occupy about 5000 bytes, which is within SQL max row length.
Can SSIS be used to created these two tables? (one without description fields, the other with those field but arranged vertically in the table rows).
The fundamental issue is I can not import a single file row into a sql table because that row length could exceed the max byte count for a row.
View 8 Replies
View Related
Feb 26, 2014
I am trying to split a row that currently has information like this:
area: north,south,west
however I want the area column to display the information like this:
Area:
North
South
West
I have wrote a split function and it worked fine in other work I did...
View 5 Replies
View Related
Nov 28, 2007
Hi All
I have a matrix with single row. The no. of column varies and sometimes goes to 10-15. So it goes to next page and while exporting it inserts blank pages when exported to PDF. I need the column width at least 2.5cm. I need to break the matrix to next row instead of it going to next page say after 6th or 8th column. I tried to work with the example given in the site http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx by Chris Hays. But it is showing matrix for each Row Group, which doesn't meet my requirement.
I had a work around which worked by putting two matrix one below the other and filtering the columns to be shown in each matrix.
If anybody faced this issue or anybody solved the issue kindy reply which will be very helpful for me.
One more doubt, Can I get the Column number of the matrix?
Thanks in advance
Dileep
View 3 Replies
View Related
Jun 22, 2007
I have a query that returns a table similar to:
State Status Count
CA Complete 10
CA Incomplete 200
NC Complete 20
NC Incomplete 205
SC Incomplete 50
What sort of query will allow me to reformat the table into:
State Complete Incomplete
CA 10 200
NC 20 205
SC NULL 50
View 5 Replies
View Related
Jun 7, 2008
SOURCE TABLE
ID DESCRIPTION
1 I am a programmer
2 I am a doctor
Destination Table
ID LINE DESCRIPTION(Varchar10)
1 1 I am a pro
1 2 grammer
2 1 i am a doc
2 2 tor
Please someone help me on this.
View 4 Replies
View Related
