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

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:

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

|||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.

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
George
Assuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
David Portas
SQL Server MVP
|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
GeorgeAssuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
David Portas
SQL Server MVP
--|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
GeorgeAssuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
--
David Portas
SQL Server MVP
--|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining the dates

Hi Guys

I have got two columns- one is the year and the other is the month number. I need to combine these two columns so that they form a date

For Ex

Year Month CombinedColumn

2000 11 2000/11

2003 01 2003/01

I am using SQL Server 2005

Thanks

I'll assume that Year and Month are integers and CombinedColumn is of data type DATETIME. If other data types are used, this solution might not apply.

Code Block

CREATE TABLE #Temp

(

[Year] INT NOT NULL,

[Month] INT NOT NULL,

[CombinedCol] DATETIME NULL

)

INSERT INTO #Temp VALUES (2000, 11, NULL)

INSERT INTO #Temp VALUES (2003, 01, NULL)

UPDATE #Temp

SET [CombinedCol] = DATEADD(month, [Month] - 1, DATEADD(year, [Year] - 1900, '1900-01-01 00:00:00.000'))

SELECT * FROM #Temp

|||

you can try this..

select cast( (cast(yearValue as varchar(4))+cast(monthValue as varchar(2)) + '01') as datetime)

you will get the date as the first day of your year and month values...

|||

Harish has the right idea, but it should be noted that 2000/11 is not a valid date. You can just concatenate them for display, but as for making them a date, can you explain more what you will do with the dates once you have them concatenated? (unless just making them the first day of the month suffices, of course

|||

My query would be like select cast('20001101' as datetime) which sql server implicitly converts to datetime value from varchar if it is in yyyymmdd format. The user needs to decide which value he needs for the dd value in the string

|||

Here it is,

Code Block

Create Table #sampledata (

[Year] varchar(4),

[Month] varchar(2)

);

Insert Into #sampledata Values('2000','11');

Insert Into #sampledata Values('2003','01');

DECLARE @.UserDay varchar(2);

Set @.UserDay = '5'

Select Convert(Datetime, Year + Month + substring(cast((cast(@.UserDay as int) + 100) as varchar),2,2),112) [Output] from #sampledata

|||

Another method with fewer keystrokes

Code Block

SELECT CAST(LTRIM([Year] * 10000 + [Month] * 100 + @.UserDay) AS DATETIME)

FROM #sampledata

|||Is it bad to use implicit conversion? I believe it will be faster than explicitly handling the conversion.|||

Well, I think it is better to use explicit conversion. That way you leave no room for anyone for interpretation and avoid any ambiguities.

|||

Just a note: "Partial Dates" like "January, 1968" are not really supported. Basically in SQL, if you really want to work with dates, you must store a full date...."January 1, 1968" for example. SQL will infer the time component as being Midnight and store it as "01 Jan 1968 00:00" (if you're using smalldatetime) and "01 Jan 1968 00:00:00.000" using DateTime.

So, for your query, let's add a day-of-the-month component:

Code Block

select convert(smalldatetime,'01' + '/' + Month + '/' + Year) as ThisIsTheDate from

or...if the values are stored as numbers (which is not suggested in your example of "01" which would probably be "1" if it were stored as a number:

Code Block

select convert(smalldatetime,"01/" + right('00',convert(varchar,month),2) + '/' + convert(varchar,year))

from

|||

Thanks .It works fine

Cheers

|||

Actually, here's one that I like better:

Note that DateAdd(year,50,0) = 'January 1, 1950' and DateAdd(month, 5, 0) = 'June 1, 1900' so....

Code Block

CREATE TABLE #Temp

(

[Year] INT NOT NULL,

[Month] INT NOT NULL,

[CombinedCol] DATETIME NULL

)

INSERT INTO #Temp VALUES (2000, 11, NULL)

INSERT INTO #Temp VALUES (2003, 01, NULL)

select dateadd(month, [Month]-1,dateadd(Year,[year]-1900,0))

from #Temp

|||

I came up with the same approach, but after you. One little thing...rather than specify the "base date" with a string of "1900-01-01 00:00:00.000") you can simply use the numeric 0

Code Block

select DateAdd(month, 10, 0)

select DateAdd(month, 10, "1900-01-01 00:00:00.000")

Combining Table and Matrix format in one Report in RS2005

Hello,

I am using RS 2005 trying to create the following report. My report consists of the following columns: Question, Sub Question, N as Number of Responses, All as Average for all responses per given question and sub question, and Ethnicity column which is presented here in a Matrix format with ethnic group as columns and average response as Data values. It looks like my challenge is to combine Matrix format report (Ethnicity column) with a data such as N and All columns which are more like a table format. Any input how I could tackle this is greatly appreciated.

Thank you!

--

1.How often have you done each of the following?

N All F M Asian Multi-cultural a. Worked on a paper or project that required integrating ideas or information from various sources 1134 3.96 3.95 3.99 3.54 4.50 b. Used library resources 1132 4.21 4.26 4.09 4.12 4.33 c. Prepared multiple drafts of a paper or assignment before turning it in 1130 3.90 3.97 3.76 3.80 4.50

-How the source data looks like?sqlsql

Combining multiple columns into one column.

Combing multiple columns like [LastName],[FirstName] and
[MiddleName]into one column named as [Name] is very simple in Access,
but how will i do that in SQL? Any suggestions? PLease?
Thanks in advance,
Geri
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You have to add another column, update with existing data,
and then drop existing columns.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Geri Gavertz" <gerific@.yahoo.com> wrote in message
news:ezPCuV8GFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Combing multiple columns like [LastName],[FirstName] and
> [MiddleName]into one column named as [Name] is very simple in Access,
> but how will i do that in SQL? Any suggestions? PLease?
> Thanks in advance,
> Geri
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||same as in Access
Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
Madhivanan|||same as in Access
Select LastName+' ' + FirstName + ' ' + MiddleName as Name from Table
Madhivanan|||<madhivanan2001@.gmail.com> wrote in message
news:1109400766.869281.200560@.o13g2000cwo.googlegroups.com...
> same as in Access
> Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
> Madhivanan
>
You might want to wrap them in IsNull so that a NULL in one of the columns
doesn't NULL out the entire result:
Select IsNull(FirstName, '') + ' ' + IsNull(MiddleName, '') + ' ' +
IsNull(LastName, '') As FullName from MyTable
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company

Combining multiple columns into one column.

Dear all,
I am having a problem on how to merge 3 columns into one column. I have a
columns named [LastName], [FirstName] and [MiddleName], I want those columns
to be as one and name it as [Name]. How will I do that? I already tried the
trick like what I did in Access but it does'nt work in SQL. Please help me
with this. Any suggestions will be much appreciated.
Thanks in advance,
Jir
Try:
alter table dbo.MyTable
add
MyComputedCOlumn as LastName + ', ' + FirstName + ' ' + MiddleName
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Jir" <Jir@.discussions.microsoft.com> wrote in message
news:F64E0FD3-3E0E-4B9A-AB49-0CFFE29B5D8F@.microsoft.com...
Dear all,
I am having a problem on how to merge 3 columns into one column. I have a
columns named [LastName], [FirstName] and [MiddleName], I want those columns
to be as one and name it as [Name]. How will I do that? I already tried the
trick like what I did in Access but it does'nt work in SQL. Please help me
with this. Any suggestions will be much appreciated.
Thanks in advance,
Jir

Combining information in two columns

I may be missing something obvious here (in fact it's probably a really
simple problem, and it's probably me being silly - it is Friday after all),
but I can't work out how to do this.
I have a query that performs a full join on two tables, and pulls out four
columns - two containing years, and the others two containing months for
those year. I want to create a master view/query that gives me all the
years, and months in those years once only.
So for example say I have:
Topic_Years, School_Years, Topic_Months, School_Months
Null 1992 Null 10
2004 Null 6
Null
2004 Null 9
Null
2004 Null 10 Null
2004 2004 1 1
2004 2004 2 2
I want to get:
Years, Months
1992 10
2004 1
2004 2
2004 6
2004 9
2004 10
Can somebody point me in the right direction on how to do this?
Cheers!
SarahHi Sara, try this:
SELECT
COALESCE(Topic_Years, School_Years) AS y,
COALESCE(Topic_Months, School_Months) AS m
FROM ...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sarah Clough" <sarah_c_clough@.hotmail.com> wrote in message
news:OpZaUU7KFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I may be missing something obvious here (in fact it's probably a really
>simple problem, and it's probably me being silly - it is Friday after all),
>but I can't work out how to do this.
> I have a query that performs a full join on two tables, and pulls out four
> columns - two containing years, and the others two containing months for
> those year. I want to create a master view/query that gives me all the
> years, and months in those years once only.
> So for example say I have:
> Topic_Years, School_Years, Topic_Months, School_Months
> Null 1992 Null 10
> 2004 Null 6 Null
> 2004 Null 9 Null
> 2004 Null 10
> Null
> 2004 2004 1 1
> 2004 2004 2 2
> I want to get:
> Years, Months
> 1992 10
> 2004 1
> 2004 2
> 2004 6
> 2004 9
> 2004 10
> Can somebody point me in the right direction on how to do this?
> Cheers!
> Sarah
>|||Hi
If you mean first of two year which is not null and first of two month which
is not null then
please try this
select coalesce(topic_years,school_years) as years ,
coalesce(topic_months,school_months) as months from Table
Thanks
AM
"Sarah Clough" <sarah_c_clough@.hotmail.com> wrote in message
news:OpZaUU7KFHA.1308@.TK2MSFTNGP15.phx.gbl...
> I may be missing something obvious here (in fact it's probably a really
> simple problem, and it's probably me being silly - it is Friday after
all),
> but I can't work out how to do this.
> I have a query that performs a full join on two tables, and pulls out four
> columns - two containing years, and the others two containing months for
> those year. I want to create a master view/query that gives me all the
> years, and months in those years once only.
> So for example say I have:
> Topic_Years, School_Years, Topic_Months, School_Months
> Null 1992 Null 10
> 2004 Null 6
> Null
> 2004 Null 9
> Null
> 2004 Null 10
Null
> 2004 2004 1 1
> 2004 2004 2 2
> I want to get:
> Years, Months
> 1992 10
> 2004 1
> 2004 2
> 2004 6
> 2004 9
> 2004 10
> Can somebody point me in the right direction on how to do this?
> Cheers!
> Sarah
>|||Brilliant, cheers! A little play with my syntax, and it fits into my
existing query.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OsvHNa7KFHA.3132@.TK2MSFTNGP12.phx.gbl...
> Hi Sara, try this:
> SELECT
> COALESCE(Topic_Years, School_Years) AS y,
> COALESCE(Topic_Months, School_Months) AS m
> FROM ...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Sarah Clough" <sarah_c_clough@.hotmail.com> wrote in message
> news:OpZaUU7KFHA.1308@.TK2MSFTNGP15.phx.gbl...
>|||"Sarah Clough" <sarah_c_clough@.hotmail.com> wrote in message
news:uz2Ank7KFHA.2804@.TK2MSFTNGP10.phx.gbl...
> Brilliant, cheers! A little play with my syntax, and it fits into my
> existing query.
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
message
> news:OsvHNa7KFHA.3132@.TK2MSFTNGP12.phx.gbl...
If the Years and Months in each Row are not necessarily the Same or Null
then you need
Select Topic_Years, Topic_Months From ...
UNION
Select School_Years, School_Months From ...
Regards,
Jim

Combining established columns into one

I have a table whose schema is already defined and populated with data. I would like to create a column named Name that combines the first and last name columns in the following format "last name, first name". I tried to create a formula that concatenated these two columns, but it kept spitting up on me. Any ideas?Could you please post your syntax?|||It is best to do the formatting for display purposes on the client. What if you want to change the formatting later? You hav e to make schema changes even if you use computed columns or views or queries in SPs.|||

you may wish to use a calculated column

use northwind
select * from employees
go
alter table employees
add
fullname as rtrim(lastname)+','+rtrim(firstname)
go

select fullname,lastname,firstname from employees

|||I realize that it would be best to do all the formatting on the client. The problem is that I have about 50 stored procedures that were developed on another database that was "supposed to" have the same table schemas. Unfortunately, the developer decided to split apart the names into first name and last name fields. It would be easier to just created a computed column.|||Actually, splitting the name into it's constituent parts and storing it is the correct way. You can use a computed column or view with the computed expression or modify your SP to include the computed expression. With all these methods, you can get the required column for display purposes. But if you want to search on this concatenated string then it is a different deal. Performance depends on lot of factors like index on the computed column, whether optimizer matches the computed column expression and uses the index and so on.

Combining data from multiple columns or views

I am trying to combine/report on data that is in one table. Due to system
limitations I have 3 columns that store the same type of business data.
Salesperson A, B and C are separate columns but need to be combined for
reporting. Can I create another table where I can use a select into to put
Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
into Column 1? If so, how?
Here is a sample data line:
Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
SalesAmount
I thought I could combine it into the following layout:
Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.Hi Dan,
Do you want to concatenate Salespersons A, B, and C all into one column, or
do you want to create a new row for Salesperson A, Salesperson B, and
salesperson C?
To concatenate, you can do the following:
select SalespersonA+SalespersonB+salespersonC, CommissionRate, DocNumber,
SalesAmount from tbl_name
If you want to create a new table and then insert into it from the other
table where each salesperson has their own row, then it would look something
like this:
create the table with columns for Salesperson, CommissionRate, DocNumber,
SalesAmount
Insert into NewTable
select SalespersonA, commissionRate, DocNumber, SalesAmount
Insert into NewTable
select SalespersonB, CommissionRate, DocNumber, SalesAmount
ect.
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Thanks for the help... I tried the following syntax and it was not working:
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS, THRDPRTYNAME,
COMMRATE, REFERRATE) VALUES (SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW)
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Change it to
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME,
COMMRATE, REFERRATE) SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW
Regards
Amish Shah|||you can also do it with unions and renaming the columns.

Combining data from multiple columns or views

I am trying to combine/report on data that is in one table. Due to system
limitations I have 3 columns that store the same type of business data.
Salesperson A, B and C are separate columns but need to be combined for
reporting. Can I create another table where I can use a select into to put
Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
into Column 1? If so, how?
Here is a sample data line:
Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
SalesAmount
I thought I could combine it into the following layout:
Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.Hi Dan,
Do you want to concatenate Salespersons A, B, and C all into one column, or
do you want to create a new row for Salesperson A, Salesperson B, and
salesperson C?
To concatenate, you can do the following:
select SalespersonA+SalespersonB+salespersonC, CommissionRate, DocNumber,
SalesAmount from tbl_name
If you want to create a new table and then insert into it from the other
table where each salesperson has their own row, then it would look something
like this:
create the table with columns for Salesperson, CommissionRate, DocNumber,
SalesAmount
Insert into NewTable
select SalespersonA, commissionRate, DocNumber, SalesAmount
Insert into NewTable
select SalespersonB, CommissionRate, DocNumber, SalesAmount
ect.
"Dan Shepherd" wrote:

> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to pu
t
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Thanks for the help... I tried the following syntax and it was not working:
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS, THRDPRTYNAME,
COMMRATE, REFERRATE) VALUES (SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW)
"Dan Shepherd" wrote:

> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to pu
t
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Change it to
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME,
COMMRATE, REFERRATE) SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW
Regards
Amish Shah|||you can also do it with unions and renaming the columns.sqlsql

Combining Columns of Same Name

I have 4 tables all with an accountingDate [DateTime] and an amount
[money]. I also have an AccountRegister that acts as a Ledger and has
the invoice items.
I am trying to create a query with a single amount column as a result,
right now my joins create 4 "Amount" columns, is there a way to combine
them into one?
SELECT AR.accountEntryID, AR.clientID, AR.accountingDate,
AR.accountEntryID,
AR.hostingInvoiceID, AR.secondgearInvoiceID,
AR.consultingInvoiceID,
AR.projectInvoiceID, CS.amount AS Amount, HO.amount AS
Amount,
SG.amount AS Amount, PR.amount AS Amount, PY.amount AS
Amount
FROM acct_AccountRegister AR LEFT OUTER JOIN
acct_ConsultingInvoices CS ON
AR.consultingInvoiceID = CS.consultingInvoiceID LEFT OUTER JOIN
acct_HostingInvoices HO ON AR.hostingInvoiceID =
HO.hostingInvoiceID LEFT OUTER JOIN
acct_ProjectInvoices PR ON AR.projectInvoiceID =
PR.projectInvoiceID LEFT OUTER JOIN
acct_SecondGearInvoices SG ON
AR.secondgearInvoiceID = SG.secondgearInvoiceID LEFT OUTER JOIN
acct_Payments PY ON AR.accountPaymentID = PY.accountPaymentID
ORDER BY AR.accountingDatedepends - how is the one determined?
is it a sum of the amount columns?
is it the first non-null amount?
or something else?
jasdeep jaitla wrote:
> I have 4 tables all with an accountingDate [DateTime] and an amount
> [money]. I also have an AccountRegister that acts as a Ledger and has
> the invoice items.
> I am trying to create a query with a single amount column as a result,
> right now my joins create 4 "Amount" columns, is there a way to combine
> them into one?
>
> SELECT AR.accountEntryID, AR.clientID, AR.accountingDate,
> AR.accountEntryID,
> AR.hostingInvoiceID, AR.secondgearInvoiceID,
> AR.consultingInvoiceID,
> AR.projectInvoiceID, CS.amount AS Amount, HO.amount AS
> Amount,
> SG.amount AS Amount, PR.amount AS Amount, PY.amount AS
> Amount
> FROM acct_AccountRegister AR LEFT OUTER JOIN
> acct_ConsultingInvoices CS ON
> AR.consultingInvoiceID = CS.consultingInvoiceID LEFT OUTER JOIN
> acct_HostingInvoices HO ON AR.hostingInvoiceID =
> HO.hostingInvoiceID LEFT OUTER JOIN
> acct_ProjectInvoices PR ON AR.projectInvoiceID =
> PR.projectInvoiceID LEFT OUTER JOIN
> acct_SecondGearInvoices SG ON
> AR.secondgearInvoiceID = SG.secondgearInvoiceID LEFT OUTER JOIN
> acct_Payments PY ON AR.accountPaymentID = PY.accountPaymentID
> ORDER BY AR.accountingDate
>|||the 4 tables are different types of invoices, but they share some
common column names/types: accountingDate, amount, type, invoiceNumber
the amount is a line item (not a sum), but each of the 4 tables has an
amount for each row. If I join the ledger table with each of the 4
tables I end up with 4 amount columns, and for each row 3 are null and
one has a value depending on the table it came from. I want only one
amount column without having to duplicate that information in the
ledger table. The ledger is basically a one-many table that references
the invoices for every client, so I can pull all invoices for a
particular client.
What I decided to do was create a temporary aggregate table and insert
the values from each of the four tables into the temporary table, that
worked fine. If there is a way to do the same result with a join select
query, I'm still interested in that information.|||so you want the non-null value out of the 4? (i counted 5 in the
original post, so don't know which 4 of the 5 to use - in this example,
i've used all 5, but you get the the idea...)
see COALESCE() in BOL.
e.g. instead of just
..., CS.amount, HO.amount, SG.amount, PR.amount, PY.amount, ...
use
..., coalesce(CS.amount, HO.amount, SG.amount, PR.amount, PY.amount) as
amount, ...
this will give the first non-null value out of the list.
jasdeep jaitla wrote:
> the 4 tables are different types of invoices, but they share some
> common column names/types: accountingDate, amount, type, invoiceNumber
> the amount is a line item (not a sum), but each of the 4 tables has an
> amount for each row. If I join the ledger table with each of the 4
> tables I end up with 4 amount columns, and for each row 3 are null and
> one has a value depending on the table it came from. I want only one
> amount column without having to duplicate that information in the
> ledger table. The ledger is basically a one-many table that references
> the invoices for every client, so I can pull all invoices for a
> particular client.
> What I decided to do was create a temporary aggregate table and insert
> the values from each of the four tables into the temporary table, that
> worked fine. If there is a way to do the same result with a join select
> query, I'm still interested in that information.
>|||Thank you so much, that is exactly what I was trying to find!|||Thank you so much, that is exactly what I was trying to find!

combining columns into one table

What is the easiest way to combine the output of a several selects on a table and have each output become a column on a new table?
Thanks
Joel
Assuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel
|||well not exactly what I wanted - here's what I'm looking for. for example, suppose you have one table A with 3 columns as shown below:
oid name desc
-- -- --
1 vase container
2 lamp light
1 desk furniture
2 table furniture
1 table furniture
1 lamp light
then execute "select desc from A where oid=1 and desc=container" -- with result
container
and then execute "select desc from A where oid=1 and desc=furniture" -- with result
furniture
furniture
what I want to do is combine both outputs into 2 columns like this:
container furniture
furniture
furniture
Actually the queries and tables are more involved than this simple example but I hope I am getting the concept across.
Thanks
Joel
|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel
|||You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that I could build the table and then the client (ColdFusion) would iterate thru each row and present the row values via an HTML table. And yes, ther
e really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a column is added to the table thereby increasing the number of columns by 1 each time a column is added? Also when one column (with all rows containing values) to be added is longer
that the table to be added to, then will extra rows (which can be empty) be added so that all columns have same number of rows?
Thanks
Joel
|||Joel,
A table consists of a number of rows, where each row has the same column structure and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname ...". If the table is a result
from a SELECT statement, then you define that structure by the column list in the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows where each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The values for each column in a row is
restricted by the datatype that the column has, and a column can also possibly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a database and also the result of
SELECT statements) with presentation of the data (what you do in a client application).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same row.
> But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added? Also when one column (with all rows
containing values) to be added is longer that the table to be added to, then will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel
sqlsql

combining columns into one table

What is the easiest way to combine the output of a several selects on a tabl
e and have each output become a column on a new table?
Thanks
JoelAssuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel|||well not exactly what I wanted - here's what I'm looking for. for example,
suppose you have one table A with 3 columns as shown below:
oid name desc
-- -- --
1 vase container
2 lamp light
1 desk furniture
2 table furniture
1 table furniture
1 lamp light
then execute "select desc from A where oid=1 and desc=container" -- with r
esult
container
and then execute "select desc from A where oid=1 and desc=furniture" -- wi
th result
furniture
furniture
what I want to do is combine both outputs into 2 columns like this:
container furniture
furniture
furniture
Actually the queries and tables are more involved than this simple example b
ut I hope I am getting the concept across.
Thanks
Joel|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel|||You're right, it is a report that will be displayed via ColdFusion on a dyna
mic web page. I was hoping that I could build the table and then the client
(ColdFusion) would iterate thru each row and present the row values via an
HTML table. And yes, ther
e really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a colu
mn is added to the table thereby increasing the number of columns by 1 each
time a column is added? Also when one column (with all rows containing valu
es) to be added is longer
that the table to be added to, then will extra rows (which can be empty) be
added so that all columns have same number of rows?
Thanks
Joel|||Joel,
A table consists of a number of rows, where each row has the same column str
ucture and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a co
lumn is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname
...". If the table is a result
from a SELECT statement, then you define that structure by the column list i
n the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row
which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows wher
e each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The valu
es for each column in a row is
restricted by the datatype that the column has, and a column can also possib
ly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype
for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a d
atabase and also the result of
SELECT statements) with presentation of the data (what you do in a client ap
plication).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic we
b page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru
each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same r
ow.
> But as a general question is it possible to manufacture a table where a column is
added to the table thereby
increasing the number of columns by 1 each time a column is added? Also whe
n one column (with all rows
containing values) to be added is longer that the table to be added to, the
n will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel

combining columns into one table

What is the easiest way to combine the output of a several selects on a table and have each output become a column on a new table
Thank
JoelAssuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel|||well not exactly what I wanted - here's what I'm looking for. for example, suppose you have one table A with 3 columns as shown below
oid name des
-- -- --
1 vase containe
2 lamp ligh
1 desk furnitur
2 table furnitur
1 table furnitur
1 lamp ligh
then execute "select desc from A where oid=1 and desc=container" -- with resul
containe
and then execute "select desc from A where oid=1 and desc=furniture" -- with resul
furnitur
furnitur
what I want to do is combine both outputs into 2 columns like this
container furnitur
furnitur
furnitur
Actually the queries and tables are more involved than this simple example but I hope I am getting the concept across
Thank
Joel|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel|||You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that I could build the table and then the client (ColdFusion) would iterate thru each row and present the row values via an HTML table. And yes, there really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a column is added to the table thereby increasing the number of columns by 1 each time a column is added? Also when one column (with all rows containing values) to be added is longer that the table to be added to, then will extra rows (which can be empty) be added so that all columns have same number of rows
Thank
Joel|||Joel,
A table consists of a number of rows, where each row has the same column structure and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname ...". If the table is a result
from a SELECT statement, then you define that structure by the column list in the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows where each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The values for each column in a row is
restricted by the datatype that the column has, and a column can also possibly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a database and also the result of
SELECT statements) with presentation of the data (what you do in a client application).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same row.
> But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added? Also when one column (with all rows
containing values) to be added is longer that the table to be added to, then will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel

Combining Columns and Grouping By....

Hi,
I have the following SQL

SELECT Table1.Col1, Table3.Col1 AS Expr1,
COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,
FROM Table3
INNER JOIN Table2 ON Table3.Col1=Table2.Col1
RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2
GROUP BY Table1.Col1, Table3.Col1

The output rows have a value in either Table1.Col1 or Table3.Col1 but not
both.
I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined
column in the result but don't know how.
Thanks gratefullyHi

It would help if you posted the DDL (Create Table Statements) , example data
(as insert statements) and expected output. From your description it is not
100% clear how the tables relate or what results you expect.

If the values of Col1 are unique between each table your solution might be:

SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1

If not

SELECT IsNULL(T1.Col1,T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col2
ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3
ELSE T3.Col3 END ) AS Col3No
FROM Table1 T1
LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2
GROUP BY IsNULL(T1.Col1,T3.Col1)

or more probably

SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No
FROM (
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1 ) A
GROUP BY Col1

John

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:ovWhb.854$_54.168325@.newsfep2-win.server.ntli.net...
> Hi,
> I have the following SQL
> SELECT Table1.Col1, Table3.Col1 AS Expr1,
> COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,
> FROM Table3
> INNER JOIN Table2 ON Table3.Col1=Table2.Col1
> RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2
> GROUP BY Table1.Col1, Table3.Col1
> The output rows have a value in either Table1.Col1 or Table3.Col1 but not
> both.
> I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined
> column in the result but don't know how.
> Thanks gratefully|||Thanks John,
I didn't explain too well so I'll detail tables, releationships and what I'm
trying to do. I have managed to reduce & simplify the issue to two tables:-

Targets table which has columns:
target id - key identity autoincrement integer
locationid - integer

Actions table which has columns:
actionid - key identity autoincrement integer
targetid - integer
locationid integer

relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
Actions.targetid (I want results from all rows in Actions).

I want to count all rows from Actions and group by locationid combined from
both tables.

Targets content:
targetid locationid
1 1
2 1

Actions Content:
actionid targetid locationid
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL

If I use:
SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS actions
FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
GROUP BY Actions.locationid, Targets.locationid

I get:
Actions Actions.locationid Targets.locationid
1 1 NULL
1 2 NULL
1 3 NULL
3 NULL 1

I want to combine both locationid columns in result giving:
Actions locationid
4 1
1 2
1 3

There are more columns than illustrated but if you the above can be cracked,
I'll be away!
Cheers,
Jack

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f886ac8$0$11451$afc38c87@.news.easynet.co.uk. ..
> Hi
> It would help if you posted the DDL (Create Table Statements) , example
data
> (as insert statements) and expected output. From your description it is
not
> 100% clear how the tables relate or what results you expect.
> If the values of Col1 are unique between each table your solution might
be:
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table1
> GROUP BY Col1
> UNION
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table3
> GROUP BY Col1
> If not
> SELECT IsNULL(T1.Col1,T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN
T1.Col2
> ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3
> ELSE T3.Col3 END ) AS Col3No
> FROM Table1 T1
> LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2
> GROUP BY IsNULL(T1.Col1,T3.Col1)
> or more probably
> SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No
> FROM (
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table1
> GROUP BY Col1
> UNION
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table3
> GROUP BY Col1 ) A
> GROUP BY Col1
> John|||John,

Thanks for putting me on the right track. With ref to the example in my
reply post I used:

SELECT ISNULL(Actions.locationid, Targets.locationid) AS Location,
COUNT(Actions.actionid) AS Actions_No
FROM Actions LEFT OUTER JOIN
Targets ON Actions.targetid = Targets.targetid
GROUP BY ISNULL(Actions.locationid, Targets.locationid)

All the other columns I want to count are in the Actions table so I just
need to add them to the SELECT statement.
Thanks again,
Jack

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:Fu0ib.1525$_54.280845@.newsfep2-win.server.ntli.net...
> Thanks John,
> I didn't explain too well so I'll detail tables, releationships and what
I'm
> trying to do. I have managed to reduce & simplify the issue to two
tables:-
> Targets table which has columns:
> target id - key identity autoincrement integer
> locationid - integer
> Actions table which has columns:
> actionid - key identity autoincrement integer
> targetid - integer
> locationid integer
> relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
> Actions.targetid (I want results from all rows in Actions).
> I want to count all rows from Actions and group by locationid combined
from
> both tables.
> Targets content:
> targetid locationid
> 1 1
> 2 1
> Actions Content:
> actionid targetid locationid
> 1 NULL 1
> 2 NULL 2
> 3 NULL 3
> 4 1 NULL
> 5 1 NULL
> 6 2 NULL
> If I use:
> SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS actions
> FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
> GROUP BY Actions.locationid, Targets.locationid
> I get:
> Actions Actions.locationid Targets.locationid
> 1 1 NULL
> 1 2 NULL
> 1 3 NULL
> 3 NULL 1
> I want to combine both locationid columns in result giving:
> Actions locationid
> 4 1
> 1 2
> 1 3
> There are more columns than illustrated but if you the above can be
cracked,
> I'll be away!
> Cheers,
> Jack|||Hi

It sounds like it worked then!

Here is usable DDL and example data in case you need it again.

create table Targets (
targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY KEY,
locationid integer,
)

create table Actions (
actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY KEY,
targetid integer NULL,
locationid integer,
CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetId)
)

INSERT INTO Targets (locationid) VALUES (1)
INSERT INTO Targets (locationid) VALUES (1)

INSERT INTO Actions (targetid, locationid) VALUES (NULL,1)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,2)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,3)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)

SELECT * FROM Targets

/*
targetid locationid
---- ----
1 1
2 1

(2 row(s) affected)
*/
SELECT * FROM Actions

/*
actionid targetid locationid
---- ---- ----
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL

(6 row(s) affected)
*/

-- Your attempt
SELECT A.locationid, T.locationid, COUNT(A.actionid) AS actions
FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid
GROUP BY A.locationid, T.locationid

/*
locationid locationid actions
---- ---- ----
1 NULL 1
2 NULL 1
3 NULL 1
NULL 1 3

(4 row(s) affected)
*/

-- Your second attempt
SELECT ISNULL(A.locationid, T.locationid) AS Location,
COUNT(A.actionid) AS Actions_No
FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid
GROUP BY ISNULL(A.locationid, T.locationid)

/* Gives
Location Actions_No
---- ----
1 4
2 1
3 1

(3 row(s) affected)
*/

John

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:498ib.4706$_54.349437@.newsfep2-win.server.ntli.net...
> John,
> Thanks for putting me on the right track. With ref to the example in my
> reply post I used:
> SELECT ISNULL(Actions.locationid, Targets.locationid) AS Location,
> COUNT(Actions.actionid) AS Actions_No
> FROM Actions LEFT OUTER JOIN
> Targets ON Actions.targetid = Targets.targetid
> GROUP BY ISNULL(Actions.locationid, Targets.locationid)
> All the other columns I want to count are in the Actions table so I just
> need to add them to the SELECT statement.
> Thanks again,
> Jack
> "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> news:Fu0ib.1525$_54.280845@.newsfep2-win.server.ntli.net...
> > Thanks John,
> > I didn't explain too well so I'll detail tables, releationships and what
> I'm
> > trying to do. I have managed to reduce & simplify the issue to two
> tables:-
> > Targets table which has columns:
> > target id - key identity autoincrement integer
> > locationid - integer
> > Actions table which has columns:
> > actionid - key identity autoincrement integer
> > targetid - integer
> > locationid integer
> > relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
> > Actions.targetid (I want results from all rows in Actions).
> > I want to count all rows from Actions and group by locationid combined
> from
> > both tables.
> > Targets content:
> > targetid locationid
> > 1 1
> > 2 1
> > Actions Content:
> > actionid targetid locationid
> > 1 NULL 1
> > 2 NULL 2
> > 3 NULL 3
> > 4 1 NULL
> > 5 1 NULL
> > 6 2 NULL
> > If I use:
> > SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS
actions
> > FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
> > GROUP BY Actions.locationid, Targets.locationid
> > I get:
> > Actions Actions.locationid Targets.locationid
> > 1 1 NULL
> > 1 2 NULL
> > 1 3 NULL
> > 3 NULL 1
> > I want to combine both locationid columns in result giving:
> > Actions locationid
> > 4 1
> > 1 2
> > 1 3
> > There are more columns than illustrated but if you the above can be
> cracked,
> > I'll be away!
> > Cheers,
> > Jack
>|||Thanks John,
Appreciate your informative close-out post and will certainly file for
reference.
Cheers,
Jack

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f891931$0$11446$afc38c87@.news.easynet.co.uk. ..
> Hi
> It sounds like it worked then!
> Here is usable DDL and example data in case you need it again.
> create table Targets (
> targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY
KEY,
> locationid integer,
> )
> create table Actions (
> actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY
KEY,
> targetid integer NULL,
> locationid integer,
> CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetId)
> )
> INSERT INTO Targets (locationid) VALUES (1)
> INSERT INTO Targets (locationid) VALUES (1)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,1)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,2)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,3)
> INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
> INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
> INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)
> SELECT * FROM Targets
> /*
> targetid locationid
> ---- ----
> 1 1
> 2 1
> (2 row(s) affected)
> */
> SELECT * FROM Actions
> /*
> actionid targetid locationid
> ---- ---- ----
> 1 NULL 1
> 2 NULL 2
> 3 NULL 3
> 4 1 NULL
> 5 1 NULL
> 6 2 NULL
> (6 row(s) affected)
> */
> -- Your attempt
> SELECT A.locationid, T.locationid, COUNT(A.actionid) AS actions
> FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid
> GROUP BY A.locationid, T.locationid
> /*
> locationid locationid actions
> ---- ---- ----
> 1 NULL 1
> 2 NULL 1
> 3 NULL 1
> NULL 1 3
> (4 row(s) affected)
> */
> -- Your second attempt
> SELECT ISNULL(A.locationid, T.locationid) AS Location,
> COUNT(A.actionid) AS Actions_No
> FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid
> GROUP BY ISNULL(A.locationid, T.locationid)
> /* Gives
> Location Actions_No
> ---- ----
> 1 4
> 2 1
> 3 1
> (3 row(s) affected)
> */
>
> John

Combining 3 columns into one (not concatenation)

Greetings,
I am trying to "Fix" a poorly normalized table, and I wanted some info on the best way to go about this. It is an orders table that has items associated with it, and also "add-ons" to those items in the same table, like so:

order# Part# Addon1 Addon2 Addon3

What I would like to do is break the addons into a new table. Is there a way using a query/view/SP to bring all the addon fields into one column to create a new table with? or would I have to create some form of append to add the additional columns one at a time. Here is an example of what I want:

Old Table: addon1 Addon2 Addon3

New Table:
Addon1
Addon2
Addon3

Of course I would also provide a link between the part and the applicable addons.

Thanksselect Order, Part, Addon1 as Addon from [YourTable] where Addon1 is not null
UNION
select Order, Part, Addon2 as Addon from [YourTable] where Addon2 is not null
UNION
select Order, Part, Addon3 as Addon from [YourTable] where Addon3 is not null

combining 2 select with count and datediff into 1 select. need help.

I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete Jobs completed within 5 days

10 5

-

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

select each one as a sub select - this will only work if each one only returns 1 column and 1 row

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

)

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)

|||thanks for the response.. However, this will not work. I'm using Visual Web Developer 2005 express to test your script and it returns 0. I believe its because you it select (....) <-nothing.|||

I missed the comma between the two... I really should check my syntax better!

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

) AS 'Jobs Completed within 5 days',

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)AS 'Total Jobs Completed'

|||

thanks for the update. It runs and returned:

Total Jobs Completed Jobs Completed within 5 days

0 0

It should return 6 and 3. So, the subset is not returning the right values.

|||

I have also ran the query just this:

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

it return:

Jobs Completed within 5 days

0

|||

with some messing around.. I found that this code below works properly.

-

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request '))
AS 'Total Jobs Completed',
(SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project AS Project_1
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

Many thanks for you help.

|||

Hi,

I am thinking through this same process, and I could be wrong, but I don't think that this code is going to be accurate for determining weekdays. The reason is that if the first day you're counting is a Sunday (in this case, your DateintoSD), then you will have one more weekday than SQL is going to count. It will count the first week interval 6 days later, between Saturday and Sunday. You're multiplying your week DateDiff by 2, so you'll get that saturday and sunday subtracted from your total days of the month, but that first Sunday never gets subtracted. Am I wrong?

There is a solution I found elsewhere that involves building a calendar table in SQL, and if you Google that it will come up in your results. That's a bit more involved, however.

Andy

|||

A "calander" table should be added to any database as standard practice, loaded with weekday/end flags, public holidays and various date formats (these can be very useful when dealing with system interfaces)