Query - Select Common Data From One Column And Display In Severalcolumns

Feb 28, 2008

Hello,


I have a (big) table which is not normalized, but for i need at the
moment i think
that's no problem. Indeed, what i would like to do is to select the
name field and
the note. The problem is that i want to display the note in 2
different columns.
the first columns will show the number (count) of time that a certain
note (e.g note=4)
appears for a certain name and in the other column the same thing but
for a different note value.

so each column noteX will display the number of time that the note
with the value X appears for each name


for example, to the following table:


Name | note | job | city | id |
----------------------------------------
john | 4 | jb1 | hamb | 1 |
john | 5 | jb2 | hamb | 2 |
john | 5 | jb3 | hamb | 3 |
john | 5 | jb4 | hamb | 4 |
Mark | 4 | jb1 | mun | 5 |
Mark | 4 | jb2 | mun | 6 |
Mark | 4 | jb5 | mun | 7 |
Mark | 5 | jb1 | mun | 8 |
peter | 5 | jb3 | berl | 9 |
peter | 5 | jb5 | berl | 10 |
frank | 4 | jb6 | v.form | 11 |
frank | 5 | jb3 | v.form | 12 |
frank | 5 | jb2 | v.form | 13 |

the result should be:

Name | note5 | note4 |
-------------------------
john | 3 | 1 |
Mark | 1 | 3 |
peter | 2 | 0 |
frank | 2 | 1 |


How should be the right SQL command to show the data i want?


Rui Dias
Join Bytes!



Thanks a lot

View 5 Replies


ADVERTISEMENT

Select Common Data

Sep 21, 2007

Hi - I'm trying to construct a select statement from the following tables(see example)

_________________________________
|Customer | Product |Shop |
|----------|----------|---------|
|Customer1 | Milk |Dairy |
|Customer2 | Cream |Dairy |
|Customer3 | Milk |Dairy |
|Customer1 | Trainers |Sports|
|Customer2 | Football |Sports|
_________________________________

REQUIRED RESULT SET: Milk, Cream.

Basically I want to select all of the Products (no duplicates) where EVERY customer(1, 2 & 3) have bought a from a common shop type: i.e 'Trainers' and 'Football' should not be selected as Customer3 has not bought any goods from a sports store.

This is an example of a larger problem where there can be numerous products, customers and shops. Here's a DESCRIBE of the relevent columns in each of the tables:

CUSTOMER:
customerID

PRODUCT:
productID
customerID
shopID

SHOP:
shopID

This looks like it should be easy but my SQL isn't the best ;-)

I'd really appreciate any help you could give!!

Cheers!

View 1 Replies View Related

Select Query As Column Data

Aug 8, 2013

Outer query: select op.const_cd, (select const_hin from constituency c where c.const_cd=op.const_cd) from PollingStation op, constituency c

where op.const_cd=c.const_cd

group by op.const_cd
order by op.const_cd

Result is:
122 a 205
123 b 205
124 c 235
125 d 191
126 e 226
127 f 159
128 g 165
129 h 175
130 i 225
131 j 213

Inner Query result of: select const_cd, count(polling_cd) from PollingStation p where right(polling_cd,1)='A' group by p.const_cd order by p.const_cd:

122 4
123 4
124 2
125 3
126 1
127 6
128 11
129 9
130 2

I want this output:

const_cd const_name count inner query
122 a 205 4
123 b 205 4
124 c 235 2
125 d 191 3
126 e 226 1
127 f 159 6
128 g 165 11
129 h 175 9
130 i 225 2
131 j 213

View 8 Replies View Related

How To Write Query To Select Second Row Column Data

Nov 18, 2013

I have a requirement like below .

Input table will be like below:

eventdata:
accountID deviceID timestamp speedKPH address
--------- -------- ---------- -------- -------------
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359088796 0 Ullalu Road
preva1 bolero 1359088886 0 Ullalu Road
preva1 bolero 1359088888 8.47 Ullalu Road
preva1 bolero 1359088986 0 Ullalu Road
preva1 bolero 1359088988 45 Ullalu Road
preva1 bolero 1359088996 21 Ullalu Road
preva1 bolero 1359088998 0 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089009 12 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089016 0 Ullalu Road
preva1 bolero 1359089026 0 Ullalu Road

So here i need output table like below:

stoppagedetails:
accountID deviceID from_timestamp to_timestamp diff
--------- -------- ---------- -------- -------------
preva1 bolero 1359088796 1359088888 92
preva1 bolero 1359088986 1359088988 2
preva1 bolero 1359088998 1359089006 8
preva1 bolero 1359089016

How to write mysql query for the above requirement.

View 3 Replies View Related

Data Access :: How To Get Min Of More Than One Column In Select Query

Nov 24, 2015

I am using SQL select query to select MIN from column data but I have 12 columns and want to select MIN of all these 6 columns in one SQL select statement.

I have written as SELECT MIN(temp_sv,humidity_sv,SH1,SH2,SH3,SH4) from production but its not working... How to do this....

View 4 Replies View Related

Select And Display Part Of Column

Dec 5, 2013

How do I only display a part of data from the column ?

Lets say emp_id is E3456 and all i want to display is 3456 without that E?

View 2 Replies View Related

Select Statement - Column To Display Full Name Is 99 Characters

Apr 14, 2015

Select statement. In my database i am using the employee table. I need my first column to display your full name is 99 characters. so like if the employee is john smith it would display Your Full Name Is 9 characters (including the space).

View 2 Replies View Related

How To Display The Column Description In Query Analyser

Jun 1, 2004

Hello All.

I have created a table with short name as the file name but have entered the description in the column box (when creating a new table - see attached). I would like to show this description information in query analyser. Can this be done? I used sp_help <table name> but it doesn't show the description.

Please advise. Thank you.


Best regards

View 2 Replies View Related

SELECT Query Syntax To Display Only The Top Record For Duplicate Records

Oct 6, 2005

Good day!

I just can't figure out how I can display only the top record for the duplicate records in my table.

Example:

Table1
Code Date
01 10/1/05
01 10/2/05
01 10/3/05
02 9/9/05
02 9/9/05
02 9/10/05

My desired result would be:
Table1
Code Date
01 10/1/05
02 9/9/05

Thanks.

View 12 Replies View Related

How To Combine Two Cloumns And Display As One Column In UI ? How To Write Query For This?

Mar 14, 2008

I have 2 Columns FirstName and LastName but i need to show it in UI as User Name ,that means i need to combine both First Name and Last name and display both as 1 field namely UserName ,How to query tht ? What shld i use?

View 2 Replies View Related

T-SQL (SS2K8) :: Query To Display Different Values From Same Column In Same Table?

Sep 18, 2015

I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:

OrderNo. AttributeNumber AttributeValue
1.-Order_1 2001 A
2.-Order_1 2002 B
3.-Order_1 2003 C
4.-Order_2 2001 A
5.-Order_2 2002 B
6.-Order_2 2003 C

So the logic is as follows:

I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.

Not sure how to create my Select here since the values are in the same table

View 2 Replies View Related

How To Display 2 Columns Of Data In 1 Column?

Aug 16, 2007


How to display 2 columns of Data in 1 column?

If I use like this

Select Names, 'myData' from emp

The output is:
Name1 myData
Name2 myData
Name3 myData

But, I need to display like this

Name1
myData
Name2
myData
Name3
myData

please advise

View 5 Replies View Related

Display Output Where Integer Value Is Column Data

Dec 1, 2011

I want to display the following output where integer value is column data.

{[Advertiser].[AdvertiserKey].&[4000],
[Advertiser].[AdvertiserKey].&[4001],
[Advertiser].[AdvertiserKey].&[4002],
[Advertiser].[AdvertiserKey].&[4003],
[Advertiser].[AdvertiserKey].&[3661],
[Advertiser].[AdvertiserKey].&[3662]
}

View 4 Replies View Related

Display Column Data In Multiple Lines

Apr 14, 2014

I have data like this

TableA

ID JunkData
1 1234jdueakj34jfjj4
2 345j5uttuvj5575jkf
3 sjhsdfk283ncfkjsf9

I need the Result to display like this. Split the JunkData Column Data in multiple lines, each line should contain 5 characters.

ID JunkData
1 1234d
ueakj
34jfj
j4
2 345j5
uttuv
j5575
jkf

View 2 Replies View Related

Transact SQL :: Display Different Table Having Same Column With Different Data

Sep 25, 2015

I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e  tbl_NewEmployee having 6 record  and tbl_oldEmployee having 10 record,so i need to display data from  tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).

View 3 Replies View Related

SQL Server 2012 :: SELECT Query - Cursor To Display Result In Single Transaction

May 25, 2015

Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.

CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR

[Code] ....

View 1 Replies View Related

Return The Results Of A Select Query In A Column Of Another Select Query.

Feb 8, 2008

Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?

View 9 Replies View Related

SQL Server 2012 :: Display Data In Blocks (Column Name)

Jan 29, 2014

I want to display data in block. Below is the Create,Insert Script and format of desired output.

CREATE TABLE [dbo].[Test_AA](
[CounterpartID] [varchar](2000) NULL,
[CounterpartName] [varchar](2000) NULL,
[SATName] [varchar](2000) NULL,
[NameOfBO] [varchar](2000) NULL,

[Code] ....

Result Should be like This :

ID:17388
Name:Scottie Berwick
SATName:Scottie Berwick
NameOfBO:
SATAccountNumber:

[Code] ....

View 4 Replies View Related

Reporting Services :: Display Superscript With Bold And Red Colour For One Column Data

Aug 28, 2015

I am new to ssrs super scripts. I want to display superscript as B with bold & red colour for one column data

o/p:
Name:
America B
India B
UAE B

Here B is super script & red color....

View 2 Replies View Related

How To Change Data Display In Sql Query

Oct 2, 2007

Hi there

Assuming this is the query used to return this result:

select dept.name, count(prj.id) from department dept, project prj where prj.id = dept.uid group by dept.name

Dept, TotalProj
A , 10
B , 5
C , 2

Question: How do I change the query so that the result will return as follows:

Dept/TotalProj
A, B, C
10, 5, 2

View 5 Replies View Related

Need Help In Query - To Get Data From 2 Tables In A Particular Format To Display

Jul 14, 2007

Dear FriendsI need to get the data in the required format. Please help----------------------Table TripSheet----------------------Id,TSNo.,JourneyDate(ddmmyy),CustName,RateType,VehicleNo., BillId, journey hours, journey kmDATA
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------Table BillTripSheet-----------------------------Id,BillId,VehicleNo., TotalJourneyHours,TotalJourneyKM,SlabApplied, MinAmt, ExtAmtDATA
1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 502, 234, Vehi99,  13hrs, 250km, SlabA, 500, 50
I need to display data to be displayed in the following format in report(grouping based on vehicle no.)
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 50---------------------------------------------------------------------------------------------------------
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------2, 234, Vehi99,  13hrs, 250km, SlabA, 500, 50----------------------------------------------------------------------------------------------------------
The Calculation logic is lengthly and the number of records involved is huge.also, only the manager has authority to generate the Bill and rest of the team can view the bill report
So i cannot do the calculations while fetching the records, Bill has to be generated and stored. How do i write the query to fetch the data from the two tables "TripSheet" and "BillTripSheet" ? Please Help
ReagardsSara

View 6 Replies View Related

T-SQL (SS2K8) :: Query To Display All Data Connected To ID In One Row

Oct 23, 2014

I have a table that I am trying to query to display all data connected to a id in one row. What is the best way to achieve this?

Something like this:

ID| CRS1 | CRS2 | CRS3| CRS4 ....
1 | A101 | A102 | A103 | A104 ....

CREATE TABLE Class
(
id int,
yr int,
trm varchar(2),
crs varchar(30)

[Code] .....

View 3 Replies View Related

Select From Multiple Tables No Common Field

Oct 17, 2005

I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.

SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL

Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.

View 2 Replies View Related

Doubt In HOW TO: Query And Display Excel Data By Using ASP.NET, ADO.NET, And Visual C# .NET

Jul 20, 2005

Hi,In my application, I need to copy data from an Excel file into a SQLtable. The article related to this can be found athttp://support.microsoft.com/defaul...Ben-us%3B306572Using this,I am first extracting data from given excel file into atemporary DataTable. After making some operations on that DataTable(like splitting one column into two), I am saving the data into actualtable in SQL Server.My doubt is that, in the above given link, there are few steps neededto do on the excel file... eg.--> Highlight the rows and columns where the data resides.--> On the Insert menu, point to Name, and then click Define.--> In the Names in workbook text box, type myRange1, and then clickOK.I don't want my client to do thsese operations everytime he changescontents in that excel file, as this excel file changes almost daily.The extracting of data from excel file is done after clicking UPDATEbutton on webpage. The person saving data into Excel file is differnetfrom the one who updates it into SQL.Is there any other way where we can directly copy data from excel fileinto DataTable without doing the above three steps?I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developinglanguage for this web-based project.Any advice would be greatly appreciated. Thanks in advance.Regards,RK.

View 4 Replies View Related

Union Based On Common Column

Jul 15, 2013

Trying to figure out a SQL query.I have 2 identical tables: Table A and Table B..This query doesnt seem to be working:

SELECT * FROM (

SELECT ClientID, sum(ABC), count(*)
FROM Table A
Where <condition>

[code]...

The result I am looking for is that for each ClientID, I need the total sum of ABC and total count.

View 4 Replies View Related

When Connect Two Table Should They Always Have Column In Common With Each Other?

Jun 27, 2014

When I connect two table should they always have a column in common with each other?For example the 'patients' and 'doctors' tables in that ER are connected to say each doctor can have many patients and each patient can have many doctors, but they have no columns in common. Is it right?

View 8 Replies View Related

Find Common Value In Column For 30 Tables

Mar 21, 2006

Hi,

I am trying to query for a common value in a column called "file_auth_nbr" in 30 different tables. I was going to try something like this (see below) but wasn't sure if this was the most efficient, fastest, or correct, way to get what I'm looking for:

Select distinct a.file_auth_nbr from table1 as a
join table2 as b
on a.file_auth_nbr = b.file_auth_nbr
join table3 as c
on a.file_auth_nbr = c.file_auth_nbr
join table4 as d
on a.file_auth_nbr = d.file_auth_nbr
join table5 as e
on a.file_auth_nbr = e.file_auth_nbr
......etc., etc.

Any suggestions would be much appreciated,
Jeff

View 1 Replies View Related

How To Exclude Records That Have A Common Column

Dec 26, 2007

I am creating a query which will show patients that are enrolled in more than one program, but I need to exclude those patients that enrolled more than once in the same program. Here's part of the code:

SELECT member_id, service_id
FROM pat_prg_info ppi

This query produces results like the following:

member_id service_id
1001 1
1001 2
1003 9
1003 9
1004 2
1004 9

I would like to exclude 1003, since this member_id is enrolled twice in service_id 9. How can I accomplish this?

View 1 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB        123
colA        XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

Reporting Services :: Query To Display In Format For Numeric Data Type

Jul 21, 2015

I have to display the data in the below said formats..Current sample Data in the table and the data type is numeric(23,10)

50.00
0.50
0.00
0.00

To be displayed in the below format

1.25
0.75
0
0
1

I have to map this column in teh report and should dipslay like above.I think if 0.00 is available then it should display as 0..If 1.0 is available then it should display 1.Any value that has postive number after the decimal should display all the values  example : 2.25,3.75,5.06, So in general the solution to display values like 1.75,1,0 we should not dispaly 0 as 0.00 and 1 as 1.00 and 2 as 2.00 and so on...Any Solutions in terms of SQL query  or SSRS expression.

View 5 Replies View Related

Transact SQL :: How To List 3 Derived Columns Based On 4th Common Column

Nov 25, 2015

I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:

Ticket Type  |  Company A Count  | Company B Count  | Company C Count

I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.

DECLARE @startdate date = '20150306'
DECLARE @enddate date = '20151031'
DECLARE @AcctGrp varchar(20) = '111'
;WITH TType
AS
(
SELECT ctp.description as TicketType

[Code] .....

If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:

TicketType
AR Request Credit
Availability/Rush
Cancel Order
Credit Card Payment
Expedite Order
Freight Quote

[Code] ...

How to get the query results? Am I even close to getting it right?

View 3 Replies View Related

Reporting Services :: Display Columns When There Is No Data To Display

Apr 30, 2015

I would like to display a portion of report where there is data or no data

There is data subreport  display   

     Product Name Latex Gloves  
     Product ID      
xxxx5678

 There NO data in the subReport
 
  Product Name                          
   Product ID    

View 3 Replies View Related

Select Query For Column

Mar 13, 2008

i have one table called Healthmanagement
in that i have column HealthTitles in that there r so many rows..few of them r here:

HealthTitles
---------------------------------
Lung Health
Lung Health: General
Lung Health: Asperic Detailed Modified
Diseases
Diseases: in one generation
Attacks
Attacks: Health Related

i want in ouput all rows but which r with ':' i want after that part only and if the rows r without ':' then remain as it is:

so i want

HealthTitles
---------------------------------
Lung Health
General
Asperic Detailed Modified
Diseases
in one generation
Attacks
Health Related

how can i get that?
thanks for any help.

View 4 Replies View Related







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