Loop?
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
> How can I combine all my xml files so it can be processed by SSIS in order
> that I may create a table containing the imported xml files?
>
What is Loop?
Could you please explain more in detail.
Thank you in advance for your assistance.
"Peter W. DeBetta" wrote:
> Loop?
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
>
>
|||SSIS now has a Control Flow Item called Foreach Loop Container. You use this
to loop through all the xml files in a specified directory. You will also
need to create a Data Flow Task in the Foreach Loop Container. The samples
that come with SQL Server 2005 have an example of using this container
control, and you can find more info in BOL.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5EAFCB63-AC58-4592-A7DF-786AF5CABBBF@.microsoft.com...[vbcol=seagreen]
> What is Loop?
> Could you please explain more in detail.
> Thank you in advance for your assistance.
> "Peter W. DeBetta" wrote:
Thursday, March 29, 2012
Combining XML Files
I have to combine two seperate fixed-length flat files into 1 xml file, however, the catch is the 1st flat file (header) can/will be converted to an xml file but if there is another flat file *detail) found, then we must transform that to xml and combine into the header into one xml file.
So, the standalone header xml file would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 1 info....</field2>
</row>
</rows>
</header>
</tables>
<document>
and if a detail file was found, then the xml would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 2 info....</field2>
</row>
</rows>
</header>
<detail>
<rows>
<row>
<detfield1>details file 1 info....</detfield1>
<detfield2>details file 2 info....</detfield2>
</row>
</rows>
</detail>
</tables>
<document>
Seeing how you can't easily perform any if logic in a data-flow, what would be the best way to achieve this?
JAson
Are you saying there will be more than one details file? In your example, is detfield2 really supposed to contain a value from the second detail file?
|||sorry, I meant that there would be more than 1 filed in the details file. So,
<detfield1>details file field_1 info....</detfield1>
<detfield2>details file field_2 info....</detfield2>
The tricky part is just to figure out how to manipulate an xml file (like removing just a few end tags, then inserting another xml portion, and replace the end tags that were removed) after it has been created and written to to a destination.
|||Given that there is no "XML Destination" adapter provided with SSIS, I think you're going to have to write one yourself if your goal is to end up with an XML document. If I were in your shoes, I think I'd write a custom, managed (not script) component. I'd give it two inputs: One for the header row, and the other for the detail rows. There's a good sample in BOL that you can use to get started.Combining XML
Assume I have the following, in T-SQL:
declare @.xml1 xml
declare @.xml2 xml
set @.xml1 = '<first></first>'
set @.xml2 = '<second></second>'
I want to combine these, into the following, and return the results typed as XML:
<results>
<first></first>
<second></second>
</results>
I can't seem to find any way to do this - other than converting them to a string, and concatinating them and then typing the results as XML. Any ideas?
Thanks,
Scott
One way to do this is to use 'FOR XML PATH'
declare @.xml1 xml
declare @.xml2 xml
set @.xml1 = '<first></first>'
set @.xml2 = '<second></second>'
select @.xml1 , @.xml2
FOR XML PATH ('results'), type
The section 'Constructing XML Using FOR XML ' in BOL has more information about the different FOR XML capabilities.
Thanks
Babu
|||Beautiful!!!
Thank you Babu!
Combining Values
I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:
Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.|||I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
>
>
>
>
>
Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -
>
- Show quoted text -|||If you cant do this in reports, refer this
>
>
>
>
>
>
>
- Show quoted text -|||imani_technology_spam@.yahoo.com wrote:
You have three options:
>
You have three options:
>
1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML
>
Option 1) is the safest method.
>
For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...
>
Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
>
--
Best regards,
Marcin Guzowskihttp://guzowski.info|||On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:
>
>
>
>
>
>
>
- Show quoted text -
You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.|||Sorry, I'm a little rusty on cursors. How would I pull this off? Can
I (or should I) use nested WHILE loops?
>
>
>
>
>
>
>
>
>
>
>
>
>
>
You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.- Hide quoted text -
>
- Show quoted text -sqlsql
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
And combine them into this:
John Doe | Address 1; Address 2; Address 3
Do I need a cursor for this?imani_technology_spam@.yahoo.com wrote:
Quote:
Originally Posted by
I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:
>
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
>
And combine them into this:
>
John Doe | Address 1; Address 2; Address 3
>
>
Do I need a cursor for this?
Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.|||I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
On Jun 29, 6:55 pm, Ed Murphy <emurph...@.socal.rr.comwrote:
Quote:
Originally Posted by
imani_technology_s...@.yahoo.com wrote:
Quote:
Originally Posted by
I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:
>
Quote:
Originally Posted by
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
>
Quote:
Originally Posted by
And combine them into this:
>
Quote:
Originally Posted by
John Doe | Address 1; Address 2; Address 3
>
Quote:
Originally Posted by
Do I need a cursor for this?
>
Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -
>
- Show quoted text -|||If you cant do this in reports, refer this
http://sqljunkies.com/WebLog/amacha...px?Pending=true
Madhivanan
On Jun 30, 10:47 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:
Quote:
Originally Posted by
I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
>
On Jun 29, 6:55 pm, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>
Quote:
Originally Posted by
imani_technology_s...@.yahoo.com wrote:
Quote:
Originally Posted by
I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:
>
Quote:
Originally Posted by
Quote:
Originally Posted by
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
>
Quote:
Originally Posted by
Quote:
Originally Posted by
And combine them into this:
>
Quote:
Originally Posted by
Quote:
Originally Posted by
John Doe | Address 1; Address 2; Address 3
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Do I need a cursor for this?
>
Quote:
Originally Posted by
Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -
>
Quote:
Originally Posted by
- Show quoted text -- Hide quoted text -
>
- Show quoted text -|||imani_technology_spam@.yahoo.com wrote:
Quote:
Originally Posted by
I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
You have three options:
1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML
Option 1) is the safest method.
For option 2) details refer to:
http://groups.google.pl/group/micro...cab9fecb969f34/
Third method:
http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
--
Best regards,
Marcin Guzowski
http://guzowski.info|||Here's where things get interesting:
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2
The results need to be
John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2
I have no idea how to pull this off.
On Jun 30, 6:39 am, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.infowrote:
Quote:
Originally Posted by
imani_technology_s...@.yahoo.com wrote:
Quote:
Originally Posted by
I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
>
You have three options:
>
1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML
>
Option 1) is the safest method.
>
For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...
>
Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
>
--
Best regards,
Marcin Guzowskihttp://guzowski.info|||On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:
Quote:
Originally Posted by
Here's where things get interesting:
>
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2
>
The results need to be
>
John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2
>
I have no idea how to pull this off.
>
On Jun 30, 6:39 am, "Marcin A. Guzowski"
>
>
>
<tu_wstaw_moje_i...@.guzowski.infowrote:
Quote:
Originally Posted by
imani_technology_s...@.yahoo.com wrote:
Quote:
Originally Posted by
I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
>
Quote:
Originally Posted by
You have three options:
>
Quote:
Originally Posted by
1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML
>
Quote:
Originally Posted by
Option 1) is the safest method.
>
Quote:
Originally Posted by
For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...
>
Quote:
Originally Posted by
Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
>
Quote:
Originally Posted by
--
Best regards,
Marcin Guzowskihttp://guzowski.info- Hide quoted text -
>
- Show quoted text -
You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.|||Sorry, I'm a little rusty on cursors. How would I pull this off? Can
I (or should I) use nested WHILE loops?
On Jul 2, 3:28 am, Stephen2 <Step...@.mailinator.comwrote:
Quote:
Originally Posted by
On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
>
>
>
>
>
<imani_technology_s...@.yahoo.comwrote:
Quote:
Originally Posted by
Here's where things get interesting:
>
Quote:
Originally Posted by
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2
>
Quote:
Originally Posted by
The results need to be
>
Quote:
Originally Posted by
John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2
>
Quote:
Originally Posted by
I have no idea how to pull this off.
>
Quote:
Originally Posted by
On Jun 30, 6:39 am, "Marcin A. Guzowski"
>
Quote:
Originally Posted by
<tu_wstaw_moje_i...@.guzowski.infowrote:
Quote:
Originally Posted by
imani_technology_s...@.yahoo.com wrote:
I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
>
Quote:
Originally Posted by
Quote:
Originally Posted by
You have three options:
>
Quote:
Originally Posted by
Quote:
Originally Posted by
1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Option 1) is the safest method.
>
Quote:
Originally Posted by
Quote:
Originally Posted by
For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
>
Quote:
Originally Posted by
Quote:
Originally Posted by
--
Best regards,
Marcin Guzowskihttp://guzowski.info-Hide quoted text -
>
Quote:
Originally Posted by
- Show quoted text -
>
You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.- Hide quoted text -
>
- Show quoted text -sqlsql
combining two tables with a full-text search
I had a table that was terribly in need of normalisation that I have now
split into two tables. The table contains three "similar" fields that I
were previously indexed using a full-text query. I now need to do the query
twice as subqueries, UNION the results, then order by the calculated rank.
Problem is - the rank appears to be independent between the two tables, to
the results are coming up with one query always being higher than the other.
Is there any way to pre-select or cap the rank value, or another way to
search these two tables so they're more "combined"?
Thanks in advance,
Duncan
Probably not as the rank is generated on a per table basis.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dunc" <dunc@.ntpcl.f9.co.uk> wrote in message
news:Ouh0hapaFHA.2496@.TK2MSFTNGP14.phx.gbl...
> I had a table that was terribly in need of normalisation that I have now
> split into two tables. The table contains three "similar" fields that I
> were previously indexed using a full-text query. I now need to do the
query
> twice as subqueries, UNION the results, then order by the calculated rank.
> Problem is - the rank appears to be independent between the two tables, to
> the results are coming up with one query always being higher than the
other.
> Is there any way to pre-select or cap the rank value, or another way to
> search these two tables so they're more "combined"?
> Thanks in advance,
> Duncan
>
|||One solution, that wouldn't be the best but would work, would be to
create a third table containing all the fields you wish to index with
the primary key associated with it, then index that table instead.
split into two tables. The table contains three "similar" fields that I
were previously indexed using a full-text query. I now need to do the query
twice as subqueries, UNION the results, then order by the calculated rank.
Problem is - the rank appears to be independent between the two tables, to
the results are coming up with one query always being higher than the other.
Is there any way to pre-select or cap the rank value, or another way to
search these two tables so they're more "combined"?
Thanks in advance,
Duncan
Probably not as the rank is generated on a per table basis.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dunc" <dunc@.ntpcl.f9.co.uk> wrote in message
news:Ouh0hapaFHA.2496@.TK2MSFTNGP14.phx.gbl...
> I had a table that was terribly in need of normalisation that I have now
> split into two tables. The table contains three "similar" fields that I
> were previously indexed using a full-text query. I now need to do the
query
> twice as subqueries, UNION the results, then order by the calculated rank.
> Problem is - the rank appears to be independent between the two tables, to
> the results are coming up with one query always being higher than the
other.
> Is there any way to pre-select or cap the rank value, or another way to
> search these two tables so they're more "combined"?
> Thanks in advance,
> Duncan
>
|||One solution, that wouldn't be the best but would work, would be to
create a third table containing all the fields you wish to index with
the primary key associated with it, then index that table instead.
Combining Two Tables Via T-SQL
Hello,
I have two tables that have different column names so I can not combine them using UNION statement. Is there a way to combine two tables and have all the columns from both tables.
Thank you for your help!
UNION does not require that the column names be the same, only that the datatypes are similar enough to combine. See this Example:
Code Snippet
USE Northwind
GO
SELECT CompanyName FROM Customers
UNION
SELECT FirstName + ' ' + LastName FROM Employees
-
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Andrew Fuller
Anne Dodsworth
Antonio Moreno TaquerÃa
Around the Horn
Berglunds snabbk?p
Bill Smith
...|||
Donnie:
The column names do not have to be the same for you to union together two tables. If you are trying to union together to tables column-for-column, it is sufficient to have:
The number of columns the same The datatypes of corresponding columns be the same|||Can you expand on what you are trying to accomplish?. As long as the data type of the columns be the same, including collation, then there is no problem using union or "union all".
declare @.t1 table(c1 int, c2 int)
declare @.t2 table(c3 int, c4 int)
insertinto @.t1 values(1, 2)
insertinto @.t2 values(3, 4)
select c1, c2 from @.t1
union all
select c3, c4 from @.t2
AMB
|||Are you certain that it is a UNION that you need to perform, and not a JOIN?
A JOIN will allow you to return all columns from both tables as individual columns within the same resultset (i.e. merge the data vertically), like so:
Code Snippet
Table 1 - Sample Data
Column1a Column2a Column3a
--
1 T1C2R1 T1C3R1
2 T1C2R2 T1C3R2
3 T1C2R3 T1C3R3
Table 2 - Sample Data
Column1b Column2b Column3b
--
1 T2C2R1 T2C3R1
2 T2C2R2 T2C3R2
3 T2C2R3 T2C3R3
Output
Column1a Column2a Column3a Column1b Column2b Column3b
--
1 T1C2R1 T1C3R1 1 T2C2R1 T2C3R1
2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2
3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3
SELECT t1.Column1a,
t1.Column2a,
t1.Column3a,
t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1a = t2.Column1b
A UNION will allow you to horizontally merge the data from both tables, like so:
Code Snippet
Table 1 - Sample Data
Column1a Column2a Column3a
--
1 T1C2R1 T1C3R1
2 T1C2R2 T1C3R2
3 T1C2R3 T1C3R3
Table 2 - Sample Data
Column1b Column2b Column3b
--
1 T2C2R1 T2C3R1
2 T2C2R2 T2C3R2
3 T2C2R3 T2C3R3
Output
Column1 Column2 Column3
-
1 T1C2R1 T1C3R1
2 T1C2R2 T1C3R2
3 T1C2R3 T1C3R3
1 T2C2R1 T2C3R1
2 T2C2R2 T2C3R2
3 T2C2R3 T2C3R3
SELECT t1.Column1a AS Column1,
t1.Column2a AS Column2,
t1.Column3a AS Column3
FROM Table1 t1
UNION ALL
SELECT t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table2 t2
Chris|||
Some kind of join is probably a good idea since I want to join matching rows as well as non matching rows from both tables. Maybe, a full join would be good but I don't want duplicates. Please see my example of the output. What do you think?
Thanks for your help!
Yes, a full join should work for you.
SELECT a.Column1a, a.Column2a, a.Column3a,
b.Column1b, b.Column2b, b.Column3b
FROM Table1 a FULL JOIN Table2 b ON (a.Column1a = b.Column1b)
There should not be any duplicates in the result set.
Combining two tables to make a third
Need help combining two tables into a third with corresponding fields
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:
>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns from e
ach table instead of using [*],
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mngong@.gmail.com> wrote in message news:1154006687.920219.20920@.75g2000cwc.googlegroups.com
..
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
>
> Your help is appreciated have been on this
>
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:
>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns from e
ach table instead of using [*],
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mngong@.gmail.com> wrote in message news:1154006687.920219.20920@.75g2000cwc.googlegroups.com
..
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
>
> Your help is appreciated have been on this
>
Combining two tables to make a third
Need help combining two tables into a third with corresponding fields
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:
>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||This is a multi-part message in MIME format.
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns =from each table instead of using [*],
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<mngong@.gmail.com> wrote in message =news:1154006687.920219.20920@.75g2000cwc.googlegroups.com...
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> > Your help is appreciated have been on this
>
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Based on this limited =information:
INSERT INTO Table3
SELECT =*
FROM =Table1
=CROSS JOIN Table2
If you must control the field order, =you may need to list the columns from each table instead of using [*],
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
wrote in message news:1154006687.920219.20920@.75g2000cwc.googlegroups.com...> =Need help combining two tables into a third with corresponding fields> of =both tables> table 1> time 12 mike work> =time 13 john sleep> times 24 George jump> table 2> 23 sam run> There is a table3 which has all the columns of the two =tables but i> cannot seem to come around to combine them> time =12 mike work 23 sam run> time 13 john sleep 23 =sam run> times 24 George jump 23 sam run> > Your help =is appreciated have been on this>
--=_NextPart_000_0C6D_01C6B148.4585B4F0--
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:
>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||This is a multi-part message in MIME format.
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns =from each table instead of using [*],
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<mngong@.gmail.com> wrote in message =news:1154006687.920219.20920@.75g2000cwc.googlegroups.com...
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> > Your help is appreciated have been on this
>
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Based on this limited =information:
INSERT INTO Table3
SELECT =*
FROM =Table1
=CROSS JOIN Table2
If you must control the field order, =you may need to list the columns from each table instead of using [*],
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
--=_NextPart_000_0C6D_01C6B148.4585B4F0--
Combining two Tables into one TempTable, with a condition.
Hi,
I'm very new to Sql, and need to combine information from two tables into one temp table. All tables have the same structure.
The problem is I want to set a condition that if Field 1 is already in the TempTable, dont include that field from the second table. Along the lines of
Table1 Description = Blue, if Table 2 Description = Blue, dont include this row.
I have been trying to use INSERT..SELECT and UNION. , the only problem I have is that I cannot come up with a working conditional statement to prevent both complete sets of data being written to the TempTable.
I'm using Sql Server 2005 deveoper Edition and VS2005Pro.
Any ideas on how to get around this would be appreciated.
Tailor
Hi Tailor,
I'm not 100% clear on what your problem is. Is it that you wanted to union two tables and insert this result into a temp table only if data for a particular column (pk I presume) doesn't already exist in the temp table, or is it that you want to union the result of two tables but only select (and thus insert) distinct rows?
Can you provide a bit more info?
Cheers
Rob
|||Hi Rob,
Thanks for your reply, I'll try to make this a little clearer with some code.
INSERT INTO tempTable (Description,Id,Reference)
SELECT Description,Id,Reference
From Table1
Where (Id = 'STR001')
UNION
SELECT Description,Id,Reference
FROM Table2
WHERE (Id = 'STR001') AND Description != Table1.Description.
Without the AND statement, all data from both tables is inserted in tempTable, however with the And statement, I get the error message.
'The multi-part identifier Table1.Description could not be bound'
If the description from Table2, matches the Description in Table1, I dont want to include the row in the TempTable.
Sorry if i'm not too clear. At 64, trying to learn VS2005, Sql2005, and intergrating excel in my code, and write a reasonable sized application, all in six months is not something I can recommend. There is just so much to learn, and I dont have a good grasp of a lot of the basics.
If you can suggest a workable solution to my problem, it would be much appreciated.
John
|||INSERT INTO tempTable (Description,Id,Reference)
SELECT Description,Id,Reference
From Table1
Where (Id = 'STR001')
INSERT INTO tempTable (Description,Id,Reference)
SELECT Description,Id,Reference
FROM Table2 t left outer join temptable tt on t.description = tt.description
WHERE (t.Id = 'STR001') AND tt.description is null
Regards
|||This is a bit of a messy solution
either
1. Insert the data from both tables with a UNION, but place the UNION statement into a subquery and alias the columns to be inserted.
2. Insert data from table1. Then do the same insert from table2 where NOT EXISTS in the temp table (or table1, either will work). This is a common task you will need to learn to do in SQL Server
|||Hi JMattias and SHughes,
Thank you both, very much, for your replies. It not only solved the problem, I learnt a lot more on the way through.
Your assistance is greatly appreciated.
John
sqlsqlCombining two tables from different databases
I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.
To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.
To better illustrate:
database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58
The overlapping cusno's are not the same customer.
How can I get all the cusno from the arcus file in database two to the arcus file in database one?
Is this even possible?not without creating all new PK values.
if you are OK with a brand new value for the PK, which is sounds like you are, i'd create a staging table with an identity on the front of it and insert all the data from both, and use the new identity as your new cusno, replacing table in database1 (after renaming it with a '_BACKMEUPFOO' suffix)
in any scenario you face one big hurdle:
you will be breaking all the relationships FK'd to cusno in database #1.
all those related tables will need updating too...and the app that creates this data may not like it non-too-much, you changing its' PK and all.|||You can use either DTS or BCP...OUT. If you use DTS you can easily skip the IDENTITY field values and append from one database table to the other. If you choose BCP you'll have to create a format file during OUT operation, edit it with a text editor to specify that you are going to skip the IDENTITY field, and then BCP...IN/BULK INSERT specifying that modified format file.|||...which is why I like to use GUIDs as surrogate keys rather than incrementing identities. :D|||sounds like your need is to retain the original cusno's in some derivable fashion, and to do that you're going to need to create a surrogate or change the PK in the target database entirely - maybe compound it by adding a 'source system' character column to it. or just tack an 'a' on the end off all the original cusnos from the 1st server and a 'b' to all the second.
the relationship breaking is still gonna hurt you, without updating all the rest of the tables FK'd to cusno in your target - no matter how you pump the data or change the cusno.
DTS would be my ETL tool of choice - if i had to pick btw BCP and DTS, for this job.
To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.
To better illustrate:
database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58
The overlapping cusno's are not the same customer.
How can I get all the cusno from the arcus file in database two to the arcus file in database one?
Is this even possible?not without creating all new PK values.
if you are OK with a brand new value for the PK, which is sounds like you are, i'd create a staging table with an identity on the front of it and insert all the data from both, and use the new identity as your new cusno, replacing table in database1 (after renaming it with a '_BACKMEUPFOO' suffix)
in any scenario you face one big hurdle:
you will be breaking all the relationships FK'd to cusno in database #1.
all those related tables will need updating too...and the app that creates this data may not like it non-too-much, you changing its' PK and all.|||You can use either DTS or BCP...OUT. If you use DTS you can easily skip the IDENTITY field values and append from one database table to the other. If you choose BCP you'll have to create a format file during OUT operation, edit it with a text editor to specify that you are going to skip the IDENTITY field, and then BCP...IN/BULK INSERT specifying that modified format file.|||...which is why I like to use GUIDs as surrogate keys rather than incrementing identities. :D|||sounds like your need is to retain the original cusno's in some derivable fashion, and to do that you're going to need to create a surrogate or change the PK in the target database entirely - maybe compound it by adding a 'source system' character column to it. or just tack an 'a' on the end off all the original cusnos from the 1st server and a 'b' to all the second.
the relationship breaking is still gonna hurt you, without updating all the rest of the tables FK'd to cusno in your target - no matter how you pump the data or change the cusno.
DTS would be my ETL tool of choice - if i had to pick btw BCP and DTS, for this job.
combining two tables
If I have two tables with the following data:
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3select A.col1,B.col1
FROM table_A A,table_B B
good luck with school.|||hahaha, been using only joins, didn't remember about that, thanks
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3select A.col1,B.col1
FROM table_A A,table_B B
good luck with school.|||hahaha, been using only joins, didn't remember about that, thanks
combining two tables
hi,
does anyone have any good insight to this problem? I will have two tables which contains the same number of columns for same data types, they are related together by a key book_id. I need to combine them together and create some extra totalling data in the new datatable for a report. Here is an example
table1:
book_id new_words cost_of_change
1 3000 2
1 4000 4
2 500 4
table2
book_id old_words cost_of_change
1 1500 1
3 2500 5
I need to combine them into a table like this:
book_id new_words cost_of_change old_words cost_of_change total_cost
1 7000 6 1500 1 7
2 500 4 0 0 4
3 2500 5 0 0 5
whats the best way to do this?
I have been trying to use full outer joins to do this but I find this a difficult way to create new rows in the new combined table, like what will be an easy way for me to say in SQL that only one row should be used for book_id 1, as it's is present in the two source tables 3 times? I think i will be able to find out from using left and right inner joins, before i make the new combined table but this seems like a very ineligant way of doing this, as it seems to require lots of temp tables.
thxIs table1 the only place where there can be duplicate book IDs? I'm going to assume so, but if table2 can have duplicates you'll need to modify this a bit. But the basic idea should work.
You can use an aggregate subquery for table1 that you then join on table2. The subquery looks something like this (all of this is untested code; you may need to tweak):
SELECT SUM(new_words), SUM(cost_of_change) FROM table1 GROUP BY book_id
That sums the two fields for each id and eliminates the dupes. That subquery becomes one of the derived tables in the outer select. Something like this:
SELECT B.book_id, A.new_words, A.new_cost, B.old_words, B.cost_of_change AS old_cost, total_cost FROM table2 AS B
INNER JOIN (SELECT SUM(new_words) AS new_words, SUM(cost_of_change) AS new_cost
FROM table1 GROUP BY book_id) AS A
ON A.book_id = B.book_id
This query doesn't yet aggregate the totals from the two tables, so that will be another outer query, but the idea is the same. And there are almost certainly ways to simplify this query.
One way is to use table variables in SS2K. Then you can do three more straightforward joins.
Is this helpful? Or have I confused things more?
Don|||Something like this should work:
|||Thanks Guys, that solved my problem. The second method is lot more readable, but which would be the most efficient method?|||Both methods are basically the same thing. The second method could be made clearer by using Table variables as mentioned in the first method. But I don't think that would affect efficiency. You could test this using the Sql Query Analyzer and compare the execution plans and execution times for each.
Select
IsNull(A.book_id,B.book_id) as book_id,
IsNull(A.new_words,0.0) as New_Words,
IsNull(A.Cost_of_change,0.0) ACost_of_Change,
IsNull(B.old_words,0.0) as Old_Words,
IsNull(B.Cost_of_change,0.0) BCost_of_Change,
IsNull(A.Cost_of_change,0.0)+IsNull(B.Cost_of_change,0.0) as Cost_of_change
From
(Select book_id, Sum(new_words) New_Words,Sum(Cost_of_change) Cost_of_change FROM Table1 Group By book_id) A
FULL OUTER JOIN
(Select book_id, Sum(old_words) Old_Words,Sum(Cost_of_change) Cost_of_change FROM Table2 Group By book_id) B
ON A.book_id=B.book_id
Combining two table into a single table
I have two tables with a 1 to many relationship:
I'm guessing it has to do with the for xml path('') conversion. I can simply do a replace(myOutput,'&','&') but I'm not sure if it'll affect any other characters.
tblCarCompanies
ID Company
1 Mazda
2 Nissan
tblCarModels
ID Company_fk Model
1 1 Miata
2 1 Mazda3
3 2 Sentra
4 2 Pathfinder
5 2 Maxima
What's the best way to query these two table into one result set like:
tblCars
Company Models
Mazda Miata, Mazda3
Nissan Sentra, Pathfinder, Maxima
Maybe something like this:
|||One thing I noticed is that if my Models field contains an "&" it will return "&".declare @.carCompany table
( ID integer,
Company varchar(10)
)
insert into @.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'declare @.carModel table
( ID integer,
Company_fk integer,
Model varchar(12)
)
insert into @.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'select company,
reverse(substring(reverse(
( select model + ', ' as [text()]
from @.carModel b
where a.id = b.company_Fk
order by model
for xml path('')
)), 3, 200)) as Models
from @.carCompany a/*
company Models
- --
Mazda Mazda3, Miata
Nissan Maxima, Pathfinder, Sentra
*/
I'm guessing it has to do with the for xml path('') conversion. I can simply do a replace(myOutput,'&','&') but I'm not sure if it'll affect any other characters.
What exactly does the for xml path do and is there a way to convert it back without the replace?
|||Yes, I have seen aberations before because of the path(''); you definitely need to look out for it and you might even need to choose a different solution if it becomes a significant problem. Another alternative is to use a function -- preferably an inline function -- in conjunction with the CROSS APPLY operator. Would you like to see an example of such an alternative?|||Sure, an example would be great. Thanks!
|||
I didn't come up with a good way to create an INLINE function for this. Maybe somebody else sees a straight-forward way to do this. I mocked up this test with these tables:
create table dbo.carCompany
( ID integer,
Company varchar(10)
)
go
insert into dbo.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'
gocreate table dbo.carModel
( ID integer,
Company_fk integer,
Model varchar(12)
)
go
insert into dbo.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'
go
An example of a scalar function is like this:
alter function dbo.listModels
( @.prm_companyID integer
)
returns varchar(300)
as
begindeclare @.modelList varchar(300)
if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
return @.modelListset @.modelList = ''
select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyIDset @.modelList = reverse(substring(reverse(@.modelList), 3, 300))
return @.modelList
end
go
select id,
dbo.listModels (id) as Models
from carCompany/*
id Models
--
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/
An example with a table function and cross apply is like:
alter function dbo.companyModels
( @.prm_companyID integer
)
returns @.companyModels table
( modelList varchar(300)
)
as
begindeclare @.modelList varchar(300)
if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
returnset @.modelList = ''
select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyIDinsert into @.companyModels
select reverse(substring(reverse(@.modelList), 3, 300))return
end
go
select id,
m.modelList as Models
from carCompany
cross apply dbo.companyModels (id) m/*
id Models
-- --
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/
There are a couple of additional things to note:
It is critical to these functions that you have an index on the MODEL table based on COMPANY_FK; otherwise, you will table scan You might be able to get away with a NOLOCK optimizer hint in these functions; if you are not sure, do NOT add the NOLOCK hint.Combining two seperate tables into one
Hello!
I don`t know how to do some query. I have two tables which looks like
it:
First table:
MRPC 200504 200505 200506
C01 1 2 3
C02 2 3 4
C03 3 3 2
Second table:
MRPC 200504 200505 2000506
C01 20% 20% 50%
C02 10% 30% 70%
C03 30% 40% 15%
I would like to combine these two tables into one table, which would
look like it:
MRPC 200504 200504 PRC 200505 200505 PRC 200506 200506 PRC
C01 1 20% 2 20% 3 50%
C02 2 10% 3 30% 4 70%
C03 3 30% 3 40% 2 15%
The number of columns is changeable, because once a w
there is extracolumn added. Is it possible to link these two tables and create one
score table? As you can see the second table has the same columns as
first one and don`t have a string "PRC" in the name of column.
Thank you for your help
Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***>> Is it possible to link these two tables and create one score table?
Yes it is possible, but unless you are working towards achieving some
performance benefits (for instance, by materializing data) for specific
queries, such an attempt is of little use. You can always derive the
resultset using a simple JOIN -- in many cases a view should be the
solution.
Keeping them separate, on the other hand, allows you to manipulate data in
each table separately without affecting the other.
It is not a show-stopper, just the matter of aliasing the column names.
Anith|||On Fri, 05 Aug 2005 09:05:05 -0700, Marcin Zmyslowski wrote:
>
>Hello!
>I don`t know how to do some query. I have two tables which looks like
>it:
>First table:
>MRPC 200504 200505 200506
>C01 1 2 3
>C02 2 3 4
>C03 3 3 2
>Second table:
>MRPC 200504 200505 2000506
>C01 20% 20% 50%
>C02 10% 30% 70%
>C03 30% 40% 15%
>I would like to combine these two tables into one table, which would
>look like it:
>MRPC 200504 200504 PRC 200505 200505 PRC 200506 200506 PRC
>C01 1 20% 2 20% 3 50%
>C02 2 10% 3 30% 4 70%
>C03 3 30% 3 40% 2 15%
>
>The number of columns is changeable, because once a w
there is extra>column added. Is it possible to link these two tables and create one
>score table? As you can see the second table has the same columns as
>first one and don`t have a string "PRC" in the name of column.
Hi Marcin,
Instead of adding columns to your tables for each w
, why not add onecolumn to the table to hold the w
, then just add rows?The first table would look like this:
MRPC W
DataC01 200504 1
C01 200505 2
C01 200506 3
C02 200504 2
C02 200505 3
C02 200506 4
C03 200504 3
C03 200505 3
C03 200506 2
The second table would be similar. Depending on actual business
requirements, it might also be possible to combine these two tables:
MRPC W
Data PRCC01 200504 1 20%
C01 200505 2 20%
C01 200506 3 50%
C02 200504 2 10%
C02 200505 3 30%
C02 200506 4 70%
C03 200504 3 30%
C03 200505 3 40%
C03 200506 2 15%
(BTW, what datatype do you use for the percentages?)
Tables with a seperate column for each w
/month/year/whatever tend tobring lots of probles and no gain.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi!
I could have data in rows, but I can only do a crosstab query which let
me create one columns (wk), not two columns at the same time: "wk" and
"wk prc", that`s, why I really need data (WK and WK prc) in columns. I
still don`t know how to combine these two tables into one. Could you
give me a code example' I cannot find it in archieve. I would be very
grateful for help.
Thanx, Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***|||On Mon, 08 Aug 2005 00:54:37 -0700, Marcin Zmyslowski wrote:
>Hi!
>I could have data in rows, but I can only do a crosstab query which let
>me create one columns (wk), not two columns at the same time: "wk" and
>"wk prc", that`s, why I really need data (WK and WK prc) in columns. I
>still don`t know how to combine these two tables into one. Could you
>give me a code example' I cannot find it in archieve. I would be very
>grateful for help.
>Thanx, Marcin from Poland
Hi Marcin,
Doing a cross tab is actually better handled by the presentation tier.
But if there is no way that the client can handle this and you must do
it server side, use something like this untested code:
SELECT MRPC,
MAX(CASE WHEN W
= '200504' THEN Data END) AS '200504',MAX(CASE WHEN W
= '200504' THEN PRC END) AS '200504 PRC',MAX(CASE WHEN W
= '200505' THEN Data END) AS '200505',MAX(CASE WHEN W
= '200505' THEN PRC END) AS '200505 PRC'FROM YourTable
GROUP BY MRPC
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql
Combining two select statements
I have a SP returning the following result
The select statement for this is
Code:
Code: Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name]
order by count(test1.[office name]) DESC
The select statement for this is
SELECT dbo.TEST1.[OFFICE NAME],COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCountFROM dbo.Test2INNERJOIN dbo.test3INNERJOIN dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryIDWHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)GROUP BY dbo.TEST1.[OFFICE NAME]ORDER BYCOUNT(dbo.TEST1.[ACCOUNT ID])DESC
name id count
case1 226 320
case2 219 288
case3 203 163
case4 223 90
case5 224 73
i have another select stnat which returns like this
The select statement is
Group by test1.[Office Name]
order by count(test1.[office name]) DESC
name count
case6 10
case2 56
case4 66
case1 74
case3 88
case7 100
case5 177
How can i combine this select stament with the SP, so that, i get a fourth column with
case1 226 320 74
case2 219 288 56
....................
.....................
Hope i am not confusing you all
Please help me, if someone knows how to combine this?
Thanks
Use an alias for the Office Name column for both statements and add the id column to your first select( you need to add this column to your the GROUP BY list). Then you can use an INNER JOIN on this name column and retrieve all three columns.
Something like:
SELECT t1.name, t1.id, t1.AccountCount, t2.AccountCount2 FROM (SELECT dbo.TEST1.[OFFICE NAME] as name, [ACCOUNT ID] as id,COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCount
FROM dbo.Test2INNERJOIN
dbo.test3INNERJOIN
dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN
dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN
dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)
GROUP BY name, id ) t1 INNER JOIN (Select test1.[office name] as name, count(test1.[office name]) as AccountCount2 From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name] ) t2 ON t1.name=t2.name
ORDER BY t1.AccountCount DESC
|||
I think you've forgotten a column in your first select statement. Your first select statement selects only two columns while the output shows three columns, name, id and count. Please check and repost.
combining two select staements
Folks
I have two select statements which gives two counts ie two numbers.
select 'count' = count(*) from account
select 'count1' = count(*) from employee
I want to combine these two select statements and write one select statement where I can get two columns 'count' and 'count1' with the respective values.
The result should be EX:
count count1
3 5
Thanksselect (select count(*) from account) as 'Count',
(select count(*) from employee) as 'Count1'
I have two select statements which gives two counts ie two numbers.
select 'count' = count(*) from account
select 'count1' = count(*) from employee
I want to combine these two select statements and write one select statement where I can get two columns 'count' and 'count1' with the respective values.
The result should be EX:
count count1
3 5
Thanksselect (select count(*) from account) as 'Count',
(select count(*) from employee) as 'Count1'
Combining two rows into one
I am trying to combine two rows of data into one row for comparison
purposes and I can do it using a number of steps but thought that there
had to be a way to do it in one SQL statement. Any help would be
appreciated...
Instead of having two rows with the different period end dates, I would
like the query to return the results as follows:
Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
OpCashFlowLastYear... All in one row.
Table:
CREATE TABLE [dbo].[CompanyRatios_3Y] (
[Name] [varchar] (160),
[Ticker] [varchar] (10),
[CIK] [varchar] (10),
[PeriodEndDate] [datetime],
[DurationType] [varchar] (3),
[NetIncome] [decimal](38, 6) NULL ,
[OperatingCashFlow] [decimal](38, 6) NULL ,
[TotalAssets] [decimal](38, 6) NULL ,
[TotalRevenue] [decimal](38, 6) NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
*** Sent via Developersdex http://www.examnotes.net ***try this.
select
a.Name, a.Ticker, a.CIK, a.PeriodEndDate, b.PeriodEndDate as
PeriodEndDateLastYear,
a.Netincome as NetIncomeCurrentYear, b.Netincome as NetIncomeLastYear,
a.operatingcashflow as OpCashFlowCurrentYear,
b.operatingcashflow as OpCashFlowLastYear
from CompanyRatios_3 a
,CompanyRatios_3 b
where a.name = b.name
and a.ticker = b.ticker
and a.cik = b.cik
and year(a.PeriodEndDate) = year(b.periodEndDate) + 1|||Here is one approach, but it makes some assumptions about that data
that I am not comforatble with:
SELECT C.Name,
C.Ticker,
C.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM CompanyRatios_3 as C -- as in Current
JOIN CompanyRatios_3 as P -- as in Prior
ON C.Ticker = P.Ticker
AND C.CIK = P.CIK
WHERE C.PeriodEndDate = '20051231'
AND P.PeriodEndDate = '20041231'
The problem is that there must be EXACTLY the same Ticker and CIK
values for both years, or you don't get any data for either year.
This can be allowed for, at the price of some complication:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy Harvey
Beacon Falls, CT
On Tue, 02 May 2006 13:25:39 -0700, Jason . <jrp210@.yahoo.com> wrote:
>I am trying to combine two rows of data into one row for comparison
>purposes and I can do it using a number of steps but thought that there
>had to be a way to do it in one SQL statement. Any help would be
>appreciated...
>Instead of having two rows with the different period end dates, I would
>like the query to return the results as follows:
>Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
>NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
>OpCashFlowLastYear... All in one row.
>Table:
>CREATE TABLE [dbo].[CompanyRatios_3Y] (
> [Name] [varchar] (160),
> [Ticker] [varchar] (10),
> [CIK] [varchar] (10),
> [PeriodEndDate] [datetime],
> [DurationType] [varchar] (3),
> [NetIncome] [decimal](38, 6) NULL ,
> [OperatingCashFlow] [decimal](38, 6) NULL ,
> [TotalAssets] [decimal](38, 6) NULL ,
> [TotalRevenue] [decimal](38, 6) NULL
> ) ON [PRIMARY]
>GO
>Sample Data:
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
>
>
>
>*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 16:59:16 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:
>SELECT K.Name,
> K.Ticker,
> K.CIK,
> C.PeriodEndDate,
> PeriodEndDateLastYear = P.PeriodEndDate,
> NetIncomeCurrentYear = C.NetIncome,
> NetIncomeLastYear = P.NetIncome,
> OpCashFlowCurrentYear = C.OperatingCashFlow,
> OpCashFlowLastYear = P.OperatingCashFlow
> FROM (select distinct Name, Ticker, CIK
> from CompanyRatios_3) as K -- for Key
> LEFT OUTER
> JOIN CompanyRatios_3 as C -- as in Current
> ON K.Ticker = C.Ticker
> AND K.CIK = C.CIK
> AND C.PeriodEndDate = '20051231'
> JOIN CompanyRatios_3 as P -- as in Prior
> ON K.Ticker = P.Ticker
> AND K.CIK = P.CIK
> AND P.PeriodEndDate = '20041231'
I missed the second LEFT OUTER:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
LEFT OUTER
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy|||Thanks for both solutions. There will always be a CIK, Ticker, and name
but the dates will not always be 12/31/2005 and 12/31/2004. I was using
those dates as examples.
*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 18:29:13 -0700, Jason . <jrp210@.yahoo.com> wrote:
>Thanks for both solutions. There will always be a CIK, Ticker, and name
>but the dates will not always be 12/31/2005 and 12/31/2004. I was using
>those dates as examples.
That should be easily corrected. Just change the date tests:
AND datepart(year, C.PeriodEndDate) = datepart(year,getdate())
AND datepart(year, P.PeriodEndDate) = datepart(year,getdate()) - 1
Roy|||Thanks! There could be more than two dates per CIK so I am guessing I
would have to add the following to get the latest two dates:
WHERE (a.PeriodEndDate =
(SELECT MAX(c.PeriodEndDate)
FROM CompanyRatios_3 c
WHERE a.CIK = c.CIK))
*** Sent via Developersdex http://www.examnotes.net ***|||yeah.. I guess that should do the trick.
You will have to find the max of both this year and the previous year
--
"Jason ." wrote:
> Thanks! There could be more than two dates per CIK so I am guessing I
> would have to add the following to get the latest two dates:
> WHERE (a.PeriodEndDate =
> (SELECT MAX(c.PeriodEndDate)
> FROM CompanyRatios_3 c
> WHERE a.CIK = c.CIK))
>
> *** Sent via Developersdex http://www.developersdex
purposes and I can do it using a number of steps but thought that there
had to be a way to do it in one SQL statement. Any help would be
appreciated...
Instead of having two rows with the different period end dates, I would
like the query to return the results as follows:
Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
OpCashFlowLastYear... All in one row.
Table:
CREATE TABLE [dbo].[CompanyRatios_3Y] (
[Name] [varchar] (160),
[Ticker] [varchar] (10),
[CIK] [varchar] (10),
[PeriodEndDate] [datetime],
[DurationType] [varchar] (3),
[NetIncome] [decimal](38, 6) NULL ,
[OperatingCashFlow] [decimal](38, 6) NULL ,
[TotalAssets] [decimal](38, 6) NULL ,
[TotalRevenue] [decimal](38, 6) NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
*** Sent via Developersdex http://www.examnotes.net ***try this.
select
a.Name, a.Ticker, a.CIK, a.PeriodEndDate, b.PeriodEndDate as
PeriodEndDateLastYear,
a.Netincome as NetIncomeCurrentYear, b.Netincome as NetIncomeLastYear,
a.operatingcashflow as OpCashFlowCurrentYear,
b.operatingcashflow as OpCashFlowLastYear
from CompanyRatios_3 a
,CompanyRatios_3 b
where a.name = b.name
and a.ticker = b.ticker
and a.cik = b.cik
and year(a.PeriodEndDate) = year(b.periodEndDate) + 1|||Here is one approach, but it makes some assumptions about that data
that I am not comforatble with:
SELECT C.Name,
C.Ticker,
C.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM CompanyRatios_3 as C -- as in Current
JOIN CompanyRatios_3 as P -- as in Prior
ON C.Ticker = P.Ticker
AND C.CIK = P.CIK
WHERE C.PeriodEndDate = '20051231'
AND P.PeriodEndDate = '20041231'
The problem is that there must be EXACTLY the same Ticker and CIK
values for both years, or you don't get any data for either year.
This can be allowed for, at the price of some complication:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy Harvey
Beacon Falls, CT
On Tue, 02 May 2006 13:25:39 -0700, Jason . <jrp210@.yahoo.com> wrote:
>I am trying to combine two rows of data into one row for comparison
>purposes and I can do it using a number of steps but thought that there
>had to be a way to do it in one SQL statement. Any help would be
>appreciated...
>Instead of having two rows with the different period end dates, I would
>like the query to return the results as follows:
>Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
>NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
>OpCashFlowLastYear... All in one row.
>Table:
>CREATE TABLE [dbo].[CompanyRatios_3Y] (
> [Name] [varchar] (160),
> [Ticker] [varchar] (10),
> [CIK] [varchar] (10),
> [PeriodEndDate] [datetime],
> [DurationType] [varchar] (3),
> [NetIncome] [decimal](38, 6) NULL ,
> [OperatingCashFlow] [decimal](38, 6) NULL ,
> [TotalAssets] [decimal](38, 6) NULL ,
> [TotalRevenue] [decimal](38, 6) NULL
> ) ON [PRIMARY]
>GO
>Sample Data:
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
>
>
>
>*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 16:59:16 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:
>SELECT K.Name,
> K.Ticker,
> K.CIK,
> C.PeriodEndDate,
> PeriodEndDateLastYear = P.PeriodEndDate,
> NetIncomeCurrentYear = C.NetIncome,
> NetIncomeLastYear = P.NetIncome,
> OpCashFlowCurrentYear = C.OperatingCashFlow,
> OpCashFlowLastYear = P.OperatingCashFlow
> FROM (select distinct Name, Ticker, CIK
> from CompanyRatios_3) as K -- for Key
> LEFT OUTER
> JOIN CompanyRatios_3 as C -- as in Current
> ON K.Ticker = C.Ticker
> AND K.CIK = C.CIK
> AND C.PeriodEndDate = '20051231'
> JOIN CompanyRatios_3 as P -- as in Prior
> ON K.Ticker = P.Ticker
> AND K.CIK = P.CIK
> AND P.PeriodEndDate = '20041231'
I missed the second LEFT OUTER:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
LEFT OUTER
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy|||Thanks for both solutions. There will always be a CIK, Ticker, and name
but the dates will not always be 12/31/2005 and 12/31/2004. I was using
those dates as examples.
*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 18:29:13 -0700, Jason . <jrp210@.yahoo.com> wrote:
>Thanks for both solutions. There will always be a CIK, Ticker, and name
>but the dates will not always be 12/31/2005 and 12/31/2004. I was using
>those dates as examples.
That should be easily corrected. Just change the date tests:
AND datepart(year, C.PeriodEndDate) = datepart(year,getdate())
AND datepart(year, P.PeriodEndDate) = datepart(year,getdate()) - 1
Roy|||Thanks! There could be more than two dates per CIK so I am guessing I
would have to add the following to get the latest two dates:
WHERE (a.PeriodEndDate =
(SELECT MAX(c.PeriodEndDate)
FROM CompanyRatios_3 c
WHERE a.CIK = c.CIK))
*** Sent via Developersdex http://www.examnotes.net ***|||yeah.. I guess that should do the trick.
You will have to find the max of both this year and the previous year
--
"Jason ." wrote:
> Thanks! There could be more than two dates per CIK so I am guessing I
> would have to add the following to get the latest two dates:
> WHERE (a.PeriodEndDate =
> (SELECT MAX(c.PeriodEndDate)
> FROM CompanyRatios_3 c
> WHERE a.CIK = c.CIK))
>
> *** Sent via Developersdex http://www.developersdex
Combining two rows in a view
I have created a view for reporting. Im basically just joining a few
tables. It is for a University so the results shows students names and
the credits they are currently taking and the school code (There is 3
Colleges under one ownership)
The problem is some students attend two colleges and appear twice,
one for each enrollment. For example
FName LName Credits SchoolCode
John Smith 12 1468
John Smith 4 1469
I need to combine these results so it would look like this
John Smith 16 1468
This is not for all students just certain ones. I would like to do
this in the view if possible. Any help is appreciated.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...50.h
tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=904750Looks like you want to return just one of the school codes? In that case,
just group the data by student, and aggregate the measures:
SELECT StudentID, FName, LName, SUM(Credits) AS TotalCredits,
MIN(ScheelCode) AS MinSchoolCode
FROM ViewName
GROUP BY StudentID, FName, LName;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"TheCount" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_904750_a05cfa9ea57158f694c614723c
ee26e9@.dbforumz.com...
>I have created a view for reporting. I'm basically just joining a few
> tables. It is for a University so the results shows students names and
> the credits they are currently taking and the school code (There is 3
> Colleges under one ownership)
> The problem is some students attend two colleges and appear twice,
> one for each enrollment. For example
> FName LName Credits SchoolCode
> John Smith 12 1468
> John Smith 4 1469
> I need to combine these results so it would look like this
> John Smith 16 1468
> This is not for all students just certain ones. I would like to do
> this in the view if possible. Any help is appreciated.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Programming...pict262850.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=904750|||Take a look at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
tables. It is for a University so the results shows students names and
the credits they are currently taking and the school code (There is 3
Colleges under one ownership)
The problem is some students attend two colleges and appear twice,
one for each enrollment. For example
FName LName Credits SchoolCode
John Smith 12 1468
John Smith 4 1469
I need to combine these results so it would look like this
John Smith 16 1468
This is not for all students just certain ones. I would like to do
this in the view if possible. Any help is appreciated.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...50.h
tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=904750Looks like you want to return just one of the school codes? In that case,
just group the data by student, and aggregate the measures:
SELECT StudentID, FName, LName, SUM(Credits) AS TotalCredits,
MIN(ScheelCode) AS MinSchoolCode
FROM ViewName
GROUP BY StudentID, FName, LName;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel![url]http://www.microsoft.com/israel/sql/sqlw
/default.mspx[/url]"TheCount" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_904750_a05cfa9ea57158f694c614723c
ee26e9@.dbforumz.com...
>I have created a view for reporting. I'm basically just joining a few
> tables. It is for a University so the results shows students names and
> the credits they are currently taking and the school code (There is 3
> Colleges under one ownership)
> The problem is some students attend two colleges and appear twice,
> one for each enrollment. For example
> FName LName Credits SchoolCode
> John Smith 12 1468
> John Smith 4 1469
> I need to combine these results so it would look like this
> John Smith 16 1468
> This is not for all students just certain ones. I would like to do
> this in the view if possible. Any help is appreciated.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Programming...pict262850.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=904750|||Take a look at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
Subscribe to:
Posts (Atom)