Combining Columns In Output

Feb 23, 2006

Using MySQL 5, MySQLQuery (latest). Complete Newbie,

I have the following query:

SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;

It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.

I think the query above is pretty self explanatory!

Many thanks.

View 4 Replies


ADVERTISEMENT

Combining Output PDF Files

Feb 7, 2007

Is there a way to output reports to the same PDF file. Basicly Appending several reports to the one PDF.



View 5 Replies View Related

Combining Columns

Mar 3, 2000

I have 2 columns in a table and would like to combine
the 2 columns into 1 column separates by a delimiter.

Do anyone know the syntax??
Thanks, Vic

View 1 Replies View Related

Combining Columns

Jun 19, 2008

Hello everybody,

I have the following problem.

I have a database containing about 300 million record made out of 4 years worth of Exchange Logfiles.

I would like to make a query that retreives al send emails, its recipients and message_size.

SELECT msgid, sender_address, recipient_address, number_recipients, total_bytes FROM tbllogfiles

Results:
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl 3 55
5566 me@domain.nl 2@domain.nl 3 55
5566 me@domain.nl 3@domain.nl 3 55

But i'd like to get;

MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl,3@domain.nl,2@domain.nl 3 55

Can anyone help me with this query?

Best Regards,

Sidney

View 3 Replies View Related

Combining Columns

Aug 29, 2005

Hi

What is the best way to add two columns into a single column. Lets say I have two columns with first and last names. How can I have a new column with first,last name??

Thanks

View 8 Replies View Related

Combining Columns

Jul 18, 2007

I need to combine my first 3 columns into 1.

SELECT [Category]
,[Sub Category - I]
,[Sub Category - II]
,[MSC #]
,[Price]
FROM [Pricing Analysis].[dbo].[Table1]

View 2 Replies View Related

Combining 2 Columns Into 1

Mar 21, 2008

Hey everyone,

I have a windows app with a particular report that has 2 date columns in it. I need to combine these two columns based on a choice that the user makes on a previous form. So the user enters two dates (beginning and ending) and then makes a selection. So if my query brings back

Column1 Column2
1/1/2000 1/1/2004
2/2/2002 2/2/2007
3/3/2005 3/3/2008
.... .....

If the user selects option 1 column1 is displayed on the report, option 2 then column2, and if they select option 3 then columns 1 and 2 must be combined together based on other criteria.

I'm passing a parameter with the selection to my report, is there any way to combine these columns strictly in the report, or would it be easiest to do this in my sql query, and if so how? Keep in mind that I must keep each column separate because after the user makes the selection I have to make certain comparisons to each column before combining them. If any more information is needed then just let me know and I will post it as well.

Thanks in advance,
Aaron

View 4 Replies View Related

Combining 3 Columns Into One (not Concatenation)

Oct 13, 2005

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.

Thanks

View 1 Replies View Related

Combining Results Into Columns

Oct 18, 2013

I have a query (SELECT * FROM Pricing) that produces the following results

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23

I want to convert this to the following :

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23

We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23

View 1 Replies View Related

Combining Columns From 2 Tables

Aug 7, 2014

I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.

I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B

SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID

I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.

View 3 Replies View Related

Combining Data From Two Columns

Feb 8, 2007

Basically what I am doing is uploading inventory information from our point of sale/inventory management software into our website. I have a third party app that does most of the work for me but I need a view of our item table in our POS that this 3rd party software will use as the data source for what gets uploaded into the web. I have created the view with the following sql statement:
CREATE VIEW dbo.vwsfwebitemswine
AS
SELECT dbo.Item.*, dbo.Category.Name AS CategoryName, dbo.Department.Name AS DepartmentName
FROM dbo.Item INNER JOIN
dbo.Category ON dbo.Item.CategoryID = dbo.Category.ID INNER JOIN
dbo.Department ON dbo.Item.DepartmentID = dbo.Department.ID
WHERE (dbo.Item.DepartmentID = 7) AND (dbo.Item.WebItem = 1)


As you can see I am pulling from 3 tables. Item, Category and Department so that I can get the actual category and department names not just their numeric ID codes referenced in the standalone item table. The view data looks like this:

UPC ….. CategoryName DepartmentName
12345….. MerlotWine
34556….. ChadonnayWine
Etc…

What I need is an additional column created that combines the DepartmentName column values with the CategoryName column values and includes a ~ in between like this.

UPC ….. CategoryName DepartmentName Combined
12345….. MerlotWine Wine~Merlot
34556….. ChardonnayWineWine~Chardonnay
Etc…

I’m not a SQL expert by any stretch. I’ve gotten this far using this forum but I’m stuck. Any help would be greatly appreciated.

View 1 Replies View Related

Combining Columns And Grouping By....

Jul 20, 2005

Hi,I have the following SQLSELECT Table1.Col1, Table3.Col1 AS Expr1,COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,FROM Table3INNER JOIN Table2 ON Table3.Col1=Table2.Col1RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2GROUP BY Table1.Col1, Table3.Col1The output rows have a value in either Table1.Col1 or Table3.Col1 but notboth.I'd like to combine Table1.Col1 and Table3.Col1 and group by the combinedcolumn in the result but don't know how.Thanks gratefully

View 5 Replies View Related

Combining Established Columns Into One

Aug 2, 2006

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?

View 5 Replies View Related

Query Assistance Combining Columns Into One

Oct 5, 2006

I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column. My Query: Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUEFrom EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTESWhere EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_IDORDER BY Product_ID DESC  

View 10 Replies View Related

Transact SQL - Combining Two Columns Of Data

Oct 23, 2001

How do I combine two columns (ex: [first name] [last name]) to get a combined result in a new column of ([first name] [last name]).

Thank you,

Andrew

View 2 Replies View Related

External Columns Vs. Output Columns?

May 23, 2006

Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?

TIA,



Barkingdong

View 5 Replies View Related

Getting Output Of A One Row Into 4 Columns

May 12, 2005

I am trying to find a way how to query following data from a table that looks like this:
ColA   ColB   ColC
1            2         3
Output should look like this:
ColA
1
2
3

View 2 Replies View Related

Re-sizing Output Columns

Apr 2, 2002

I've got a question that I can't seem to find an answer for, I was hoping someone here might be able to point me in the right direction. I've set up a stored procedure that will email someone if any entries are added to a table . However, the output is garbled looking (see below)

Client Number SSN Client Name Old SD
New SD
------------- ----------- ---------------------------------------- --------
--------
800901 899-34-3482 John Smith 04/20/20
05/01/20
400909 144-23-0029 John Smith 04/09/20
04/11/20
447788 445-89-9967 kjl;j;j 04/05/20
04/12/20
300099 234-90-7815 John Johnson 04/08/20
04/15/20

What's happened is the client name field is too wide, so the New SD field kicks down to the next line. I'd like to clean this up. Is there a way I can either increase the length of the row before it moves to the next line, or can I re-size the client name field to match the size of the data. In other words, cli_name_vc is declared as a varchar(40). If the longest name that comes up in the query is 18 characters long, can I re-size the output so that it does not take up 40 characters?

Any help is greatly appreciated.

thanks,
-scott

View 1 Replies View Related

How To Put Select Output Into Different Columns?

Oct 27, 2004

I have a table like:

ID Disc
----------------
1 BUSH
2 JOHN
1 GOLE
2 MIKE

I would like output depending on ID to put Disc into two columns. Like:

ID Disc1 Disc2
----------------------------
1 BUSH NULL
2 NULL JOHN
1 GOLE NULL
2 NULL MIKE

Any help will be appreciated. Thanks

ZYT

View 2 Replies View Related

Sum Columns Then Limit Output

Apr 24, 2012

I am trying to add two columns together and find all the entries that equal zero I have:

select table1.nbr, table2.itemvalue as CC
from table1, table2
where table1.nbr=table2.nbr and exists (select (OpnAc1+OpnAc2) as Open from table2

I want to take the sum of OpnAc1 and OpnAc2 and find all that equal 0, (meaning no open 1 or open 2)

View 2 Replies View Related

SQL CASE Output COUNT Into Two Columns

Aug 27, 2007

Hi Everyone,
 I am trying to do a query where I need to use as little C# as possible to build my gridview.  Basically I have a column called statusID.  There are about 15 options for this column but I only want to count certain ones.  I want to count when statusID = 3 and output that into a column called "fullUnitsUsed" but when the value is 4 or > 13 I want it to count and put the result into a column called "halfUnitsUsed".  I also want it to count based on the month.  To accomplish this I have used CASE and GROUP BY.  This has worked to some extent.  Currently if I COUNT for one month I get the correct number of fullUnitsUsed and halfUnitsUsed used for January.  Unfortunately the query returns 2 records for the month.  The first one has a value for fullUnitsUsed and halfUnitsUsed is NULL, the second record has fullUnitsUsed as NULL and halfUnitsUsed has the correct value.  I was hoping to output one record where both fullUnitsUsed and halfUnitsUsed have data.  My other problem is that if I test for the entire year (which is what this query is supposed to do) there are 5 records returned for each month, 3 of the records have fullUnitsUsed and halfUnitsUsed both as NULL and for the other 2, one has fullUnitsUsed with data and the other has halfUnitsUsed with data and the other column in both records is NULL.  The values for fullUnitsUsed and halfUnitsUsed are counted for the entire year as well, which I only want it to count based on each month.  Below is my query, any suggestions about how to approach this will be greatly appreciated.  If any clarification is needed please let me know.  Again if I could get this to work completely with SQL and not need to use any more C# than I have to it would be preferable.
SELECT People.lastName + ', ' + People.firstName AS fullName, Property.Name, NYSDDSORegion.Description, OpenDays.[month], OpenDays.maxOpenDays,CASE Attend.statusID
    WHEN 3 THEN COUNT(Attend.statusID)
    END AS fullUnitsUsed,CASE Attend.statusID
    WHEN 4 THEN COUNT(Attend.statusID)
    WHEN 14 THEN COUNT(Attend.statusID)
    WHEN 15 THEN COUNT(Attend.statusID)
    WHEN 16 THEN COUNT(Attend.statusID)
    WHEN 17 THEN COUNT(Attend.statusID)
    WHEN 18 THEN COUNT(Attend.statusID)
    WHEN 19 THEN COUNT(Attend.statusID)
    WHEN 20 THEN COUNT(Attend.statusID)
END AS halfUnitsUsed
FROM Attend INNER JOIN People ON Attend.personID = People.personID
                     INNER JOIN Property ON Attend.propertyID = Property.propertyID
                     INNER JOIN NYSDDSORegion ON Property.RegionID = NYSDDSORegion.RegionID
                    CROSS JOIN OpenDays
WHERE (Attend.attendDate BETWEEN '1/1/2007' AND '12/31/2007')
GROUP BY Property.Name, People.lastName, NYSDDSORegion.Description, People.firstName, OpenDays.monthID, OpenDays.[month], OpenDays.maxOpenDays, Attend.statusID
ORDER BY Property.Name, fullName, NYSDDSORegion.Description

View 2 Replies View Related

Output Several Columns Based On Rows

Jun 9, 2008

I'll show my schema first, then I'll explain what I'm doing:

--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------


--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------


--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------


--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID | O_Description
--------------------------------------------------


--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID | ER_Name
--------------------------------------------------


--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID | RO_Selection
--------------------------------------------------



There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options).

A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions.

For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this:

----------------------------------------------------------------------
E_Title | ER_Name | OG_Title1 | OG_Title2 | OG_Title3
----------------------------------------------------------------------
Event | Bob | O_Description1 | O_Description2 | O_Description3

So in that example, that event had 3 sessions.

Right now, I can only output E_Title and ER_Name, I don't know how to output the session information

View 2 Replies View Related

Dynamic Union All - Output More Columns

Apr 29, 2014

I am having a bit of trouble writing a query that dynamically outputs what I need.

I have a table that looks like this:

COL1 AAA BBB CCC
CAT 1 3 3
CAT 1 2 2
CAT 1 4 4
DOG 2 3 3
DOG 5 5 5
MICE 1 1 1

I need it to dynamically output based off COL1, the output should look like this. When there are more rows for CAT, it should output more columns. Kind of like merging the columns

COL1 AAA BBB CCC AAA BBB CCC AAA BBB CCC
CAT 1 3 3 1 2 2 1 4 4
DOG 2 3 3 5 5 5 NULL NULL NULL
MICE 1 1 1 NULL NULL NULL NULL NULL NULL

View 3 Replies View Related

Format A Datetime Columns Output?

Jun 7, 2006

Hi All,Currently the query returns 2006-03-27 00:00:00, can I make it output03/27/2006, I want to truncate the time, and replace the hyphens withforward slashes. Any ideas?Thanks In Advance,~CK

View 6 Replies View Related

Can Columns Be Added To An Error Output?

Aug 7, 2006

When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?

I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.

IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;

// Add extra column here, e.g. ErrorMessage

View 7 Replies View Related

OLE DB Command Transform And Output Columns.

Jan 10, 2007

Hi All,

I have an OLE DB transform with a SQL Command of:

sp_get_sponsor_parent ?,? OUTPUT

where sp_get_sponsor_parent is defined like:




CREATE PROCEDURE [dbo].[sp_get_sponsor_parent]

@pEID int,

@results int OUTPUT

AS

BEGIN


.

.

.
END

I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped.
Any light shed would be greatly appreciated.
Many thanks in advance,
Tamim.

View 3 Replies View Related

Excel Task Has No Output Columns

Oct 20, 2006

I created an Excel Source and used a query to get the data,i.e

SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]

The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?

View 3 Replies View Related

Removing Unused Output Columns

Nov 1, 2007

I apologise if this question has been asked before but I have spent ages searching these forums and the internet for an answer and I am yet to find one.

My problem is that I have a package which imports a column, lets call it 'Column A'. Column A is used to create other columns, lets say Columns B, C & D. This is done in a script using asychronous input and once completed column A is no longer used. Other tranformations occur to B, C & D including a split and then finally a merge together again, but all the time A seems to remain a valid input on all processes yet I never choose to use or output it. When I come to the merge process I am required to merge columns B, C & D but also A, surely this is inefficient. Furthermore when the package has run I get a warning telling me that Column A is not required and should be removed, but I can not seem to find anywhere to remove it from the pipe.

I am hoping that I am just missing something obvious here but I have been tearing my hair out so any help would be much appreciated!

Graham.

View 10 Replies View Related

View Definition Includes No Output Columns ...

Jan 18, 2007

hello all,

i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'

below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ...
View definition includes no output columns or no items in the FROM clause.
A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.


query:

select
Case_CaseId,
Logged,
CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting,
CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating,
CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected,
CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved,
CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved,
CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closed
from
(

SELECT
Case_CaseId,
MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged,
MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting,
MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating,

AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved,
MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved,
MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed
FROM
CaseProgress
GROUP BY Case_CaseId
) as temp
order by Case_CaseId

View 2 Replies View Related

Can You Add Output Columns To The Script Transformation Editor On The Fly?

Jun 21, 2006

Can I add Output Columns to the Script Transformation Editor using code? I have to execute a SQL Statement to determine the number of years we have the data for for an item and then create the columns for the months in those years and populate them with the quantities. So my question is can I create output columns to the Script Transformation Editor on the fly that is as it is being executed?

Any input will be good.

Thanks,

MShah

View 3 Replies View Related

XML Source Error Output Columns Set To NTEXT?

Aug 17, 2007

I've read about the XML Source sometimes setting error output columns to DT_WSTR(255), but mine is now setting them to DT_NTEXT.

Anyone have any suggestions short of an XML editor? I'm concerned that I might do something to "refresh" the columns and cause the problem again.

View 2 Replies View Related

Get A List Of Output Columns On Script Transformation

Feb 13, 2007

I am using a script component to transform data. In the script component I created a bunch of fields for the output. Is there any way to loop through that list of columns? Is there code I can use in the script component to access the names, data types, data etc?

I saw a lot of informaiton on the OutputColumnCollection as part of some IDTSOuput90 thing (greek to me). As best I can guess this is for creating your own new columns, but can I see what columns are already defined via the script interface?

View 2 Replies View Related

Formating Of Columns In Output Of A SQL Statement In Query Analyzer

Jan 16, 2004

Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
------------------------- -----------------------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---------- ----------------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved