Combining Different Column Into A Row
Mar 5, 2008
Hi I want the sql query to display columns of different rows in a single row.The data base is given below. I want to make it in one row with all the details. Please help
1 | Rahul Bose | Male | #13, Bangalore, India, 686691 | Landphone-4452121252
1 | Rahul Bose | Male | #13, Bangalore, India, 686691 | Mobilephone2-558521252
1 | Rahul Bose | Male | #13, Bangalore, India, 686691 | Official_Email-TL@abc.com
The resulu should look like this
1 | Rahul Bose | Male #13, Bangalore, India, 686691 Landphone-4452121252 Mobilephone2-558521252 Official_Email-TL@abc.com
Please help
View 5 Replies
ADVERTISEMENT
Oct 30, 2007
Hi Folks, Im new to SQL, and I am trying to do the following:
I have a table Documents with DocID, Path and FileName.
A second table Keywords has KwdID, KeywordString
A third table DocumentKeywords links the two with DocID,KwdID. Multiple keywords are linked to one document.
I want to create a SELECT query that makes a result table that contains Path, FileName and Keywords columns where the Keywords column contains entries like "Keyword1,Keyword2,Keyword3" ie. a comma delimited list of keyword strings which have been built from the keywords that associate with a specific document.
I found a nice sample here
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
which shows how to return just the comma delimited string itself:
DECLARE @List varchar(100)
SELECT @List = COALESCE(@List + ', ', '') + Keywords.KeywordString
FROM DocumentKeywords
WHERE KwdID = 1
SELECT @List
I cannot seem to integrate this into the query so that it calculates the string for each row on the fly. My suspicion is that the capability is there. Can somebody point me in the right direction?
Thanks
View 4 Replies
View Related
Jul 30, 2007
I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?
Employee Name
John
Mike
Dale
View 5 Replies
View Related
Sep 4, 2007
I have a table of Sales Order Releases. Following is what the table looks like:
SMIPartNo
QtyType
PO
POLine
QtyDue
UOM
DateDue
DateDueType
fsono
fcustno
finumber
frelease
fspq
JI-117933A1
Firm
N40136001
234
200
EA
7/2/2007 0:00
SH Ship Date
E00001
20
001
1
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/9/2007 0:00
SH Ship Date
E00001
20
001
2
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/30/2007 0:00
SH Ship Date
E00001
20
001
3
1800
JI-117933A1
Firm
N40136001
234
400
EA
8/6/2007 0:00
SH Ship Date
E00001
20
001
4
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
8/13/2007 0:00
SH Ship Date
E00001
20
001
5
1800
JI-117933A1
Raw Material
N40136001
234
200
EA
8/20/2007 0:00
SH Ship Date
E00001
20
001
6
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
8/24/2007 0:00
SH Ship Date
E00001
20
001
7
1800
JI-117933A1
Raw Material
N40136001
234
200
EA
9/3/2007 0:00
SH Ship Date
E00001
20
001
8
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
9/10/2007 0:00
SH Ship Date
E00001
20
001
9
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
9/17/2007 0:00
SH Ship Date
E00001
20
001
10
1800
See 2nd post for rest of question
View 1 Replies
View Related
May 14, 2014
Below is my sample table and data
With Item as(
Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all
Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all
Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all
Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all
Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all
Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all
Select 7 as ItemId,'ItemName7' as ItemName,700 as position),
Mapping as (
Select 1 as Parent, 2 as child union all
Select 1 as Parent, 3 as child union all
Select 1 as Parent, 4 as child union all
Select 5 as Parent, 6 as child union all
Select 5 as Parent, 7 as child )Expected Result:
ParentItemIdParentItemNameParentpositionChildItemIdChildItemNameChildposition
1ItemName11002ItemName2200
1ItemName11003ItemName3300
1ItemName11004ItemName4400
5ItemName55006ItemName6600
5ItemName55007ItemName7700
I was thinking to achieve using union all but if i use union all it will combine the result in rows level. but i need in column level.
View 5 Replies
View Related
Apr 6, 2014
I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.
Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.
EXAMPLE: All in one row.
ProgramID | AttributeNames
887 | Studydesign, Control Groups, Primary Outcomes.
I have attached an image of the SQL VIEW that I need to modified so it does this.
THE QUERY:
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID,
dbo.tblAttributes.AttributeName
FROM dbo.tblProgramAttributes INNER JOIN
dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
ORDER BY dbo.tblProgramAttributes.ProgramID DESC
View 5 Replies
View Related
Jul 19, 2007
Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300
View 1 Replies
View Related
Sep 27, 2015
I need to generate weekly report for data usage for my c drive and d drive.the graph should be likefor a particuluar date, I want to display graph like this. even if I change secondary axis, my c: drive used and free space not coming in single column. it comes like c: drive used space, on top of it d: drive used space and in adjacent column c: drive free space, on top of it d: drive free space.I don't want to add two category one for date and another for driver names.
View 6 Replies
View Related
Jun 5, 2007
Dear Developres,
Actually I'm on the half way of making a portal and I get some problem I need your kindly helps.
at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)???
Thanks in advance.
View 5 Replies
View Related
Oct 22, 2007
Hi all,
I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table Table 2. How can I combine these two to get the third table, Table 3 ?
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1
FROM dbo.Orders
WHERE (ShipVia = 1)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 1 Results
EmployeeID CountShipVia1
1
82
2
71
3
81
4
116
5
29
6
48
7
44
8
75
9
29
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2
FROM dbo.Orders
WHERE (ShipVia = 2)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 2 results
EmployeeID CountShipVia2
1
44
2
36
3
45
4
70
5
15
6
25
7
24
8
48
9
19
Table 3 the desired result:
EmployeeID CountShipVia1 CountShipVia2
1
82 44
2
71 36
3
81 45
4
116 70
5
29 15
6
48 25
7
44 24
8
75 48
9
29 19
thanksrobby
View 5 Replies
View Related
Nov 22, 2003
hello everyone
there is a smalllll problem facing mee...well i want to combine the result of 2 queries together
, the queries are :
select x1,x2,x3 from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z where table1.anything = 5
and the other query
select x1, x2 from Table1 where table1.anything = 5
is there anyway????
Thank you
View 2 Replies
View Related
May 9, 2004
Hello everyone,
I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.
I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:
If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:
CREATE PROCEDURE CMRC_Databases_DataTransfer
AS
DELETE FROM CMRC_ZipRegionTest
INSERT INTO CMRC_ZipRegionTest
(
Zip,
UpsRegion,
UspsRegion
)
SELECT
CMRC_ZipRegionUps.Zip,
CMRC_ZipRegionUps.UpsRegion,
CMRC_ZipRegionUsps.UspsRegion
FROM
CMRC_ZipRegion,
CMRC_ZipRegionUsps
GO
Is there any way to do this? Or do I have to manually enter all the entries?
Any help would be great. Thank you.
-Alec
View 1 Replies
View Related
Jan 23, 2006
I have a transactions table that stores prices for products bought and sold.
If I want average buying prices I use:
SELECT AVG(price), product FROM transactions WHERE transactiontype=1 GROUP BY product
and for selling prices:
SELECT AVG(price), product FROM transactions WHERE transactiontype=2 GROUP BY product
Is there a way to combine this into one SQL query, to create one bindable dataset ?
View 2 Replies
View Related
Mar 7, 2006
I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).
insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName"
insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)"
I have tried:
Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName = @uName VALUES (uID, @eventID, @comment)
Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!
Thanks in advance.
View 2 Replies
View Related
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
Aug 27, 2007
Hi,
Can anybody please tell how can I combing all records in field into one field.
For example
If my table is like
Final
_______
aaa
bbb
ccc
ddd
and i want result as
final1
_____
aaabbbcccddd
I do not want to use cursors for this. Please let me know if somebody knows the answer
Thanks
View 2 Replies
View Related
Dec 18, 2006
I have a database table tblobjects like this:
object_name, reference_id
a 1
a 2
a 3
a 4
b 2
b 3
b 1
b 4
c 2
c 4
c 5
c 6
d 2
d 4
d 5
d 6
I now would like to have a SQL query which gives me the number of
unique object and reference combinations, like this:
a
b
c
d shouldn't be displayed, because it's equal to c. The problem is also that a sequence of object references is
also important. So, for instance, object a shouldn't be equal to object b. The solution should also work is MS SQL and Mysql.
Any ideas how can I do this?
Thanks!
View 1 Replies
View Related
Dec 13, 2004
Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at? :confused:
View 12 Replies
View Related
Apr 12, 2006
These similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.
I hope that's clear; if not, maybe the SQL will make it clearer:
SELECT
[tblTickets].[id] AS TicketId,
SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblTickets].[id]
SELECT
[tblCombinations_Tickets].[ticket_id] AS cTicketId,
SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblCombinations_Tickets].[ticket_id]
I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.
I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.
This has got to be laughably simple. But I'm stuck :( Can anyone help?
View 3 Replies
View Related
Jul 12, 2006
Not concatenation, more... err.. I don't know what you'd call it.
SELECT
DISTINCT [C01241 Opened].[Col004] AS OpenerEmail,
[C01241 External Data].[DMCEMAIL] AS ExternalDataEmail,
[C01241 Internal Data].[Col15] AS InternalDataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]
(Apologies for the table/col names, this is all very temporary)
So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.
At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.
What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.
Does that make sense?
View 3 Replies
View Related
May 15, 2007
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 3
View 2 Replies
View Related
Jul 23, 2014
I have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Highlighted in blue is an example of the values I need to return within one record.
Personstartdateenddate Money
7d3397/1/201412/31/2014 1000
7d3391/1/20145/23/2014 355
View 2 Replies
View Related
Apr 3, 2008
Hi,
I was wondering if there's a way to combine last, first and middle name together in one field instead of three different ones?
View 9 Replies
View Related
May 13, 2008
Hello,
I was wondering what is the best way to have multiple joins?
Here are the two statements I've been trying to combine
--------------------------------------------------------------------
SELECT CASE when
t1.Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(t1.Cust_DB_Shipment_Key,1,5,''))
End,
t1.Airway_Bill_No,
t1.Shipper_Reference,
t1.External_Product_Cd,
t1.Chargeable_Weight,
dt.CountTrackingNumber,
dt.SumProductCharge,
t1.Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping t1
Inner Join
(
SELECT Cust_DB_Shipment_Key,
sum(PRODUCT_CHARGE_AMOUNT) as [SumProductCharge],
count(Airway_Bill_No) as [CountTrackingNumber]
FROM Shipping
Where Ship_DT = '2008-05-12' and status != 'voided'
GROUP BY Cust_DB_Shipment_Key
) dt
ON (stuff(t1.Cust_DB_Shipment_Key,1,5,'')) =
(stuff(dt.Cust_DB_Shipment_Key,1,5,''))
Where Ship_DT = '2008-05-12' and status != 'voided'
----------------------------------------------------------------------
SELECT CASE when
Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(Cust_DB_Shipment_Key,1,5,''))
End,
Airway_Bill_No,
Shipper_Reference,
Service_Name,
Chargeable_Weight,
Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping
s JOIN Shipping..Distinct_Service_by_SAS_Code d ON s.External_Product_CD = d.SAS_Code
Where Ship_DT = '2008-05-12' and status != 'voided'
Thanks,
Stephen
View 4 Replies
View Related
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
Jun 19, 2008
I need to combine two queries into one.
Query 1 (main query)
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
Result
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26 (material qty)
1569.26 (production qty)
(where 1314.26 is sum material and 1569.26 is production)
Query 2
selectsum(EmployeeLaborEvent.Hours) as hours
fromdbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = Event.JobGuid
Left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
Result:
1647.50 (which are sum of hours, this figure is correct)
Now I try to merge query 2 into Query 1 like this:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production,
sum(EmployeeLaborEvent.Hours) as hours
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
When I run the query the result is:
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26(material)
1569.26 (production)
NULL (hours)
The material and production stay the same (and is correct). Hours are wrong.
Any clues? Thank you.
View 4 Replies
View Related
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
Feb 2, 2006
Hello,
I have two tables that I would like to combine but with some rules.
Table 1 has two columns with Actual Time and Actual People
Table 2 has two columns also with Planned Time and Planned People
I would like to combine these two tables if the actual time is not more than 30 minutes from a planned time
If I use one of the planned times, i would not want to show it again, even if there is another actual time that is not more than 30 minutes of it.
For ex.
Planned Time has 20:00,23:00
Planned People has 2,5
Actual Time has 19:00,19:30,19:45,21:15
Actual People has 5,2,5,3
Output should be like
Planned_Time,Actual_Time,Planned_People,Actual_People
,19:00,,5
20:00,19:30,2,2
,19:45,,5
,21:15,,3
23:00,,5,
I can do this in asp but if this can be done in sql it would be better.
Any ideas appreciated.
Thanks.
View 4 Replies
View Related
Feb 5, 2007
hi
this is my query:
tabel1:
select userid,user_name,password,role_code,convert(varchar,expiry_date,101) as expiry_date,created_date,active from usermaster where userid='1' and active='1'
if i run this query i will get output like this:
userid user_name password role_code expiry_date created_date
1 karthik karthik AD 01/17/20082007-01-24
active
0:00:00.000 1
i have another table which has the following records
tabel 2:
select * from code master
codename codedescription
AD admin
sp supervisor
so in the first query i need the output as like tithe following:
userid user_name password role_code expiry_date created_date
1 karthik karthik admin 01/17/20082007-01-24
active
0:00:00.000 1
so how to combine this two tables inorder to get the codedescription as admin for the roll_code=ad
so please give me query for this
View 18 Replies
View Related
Jun 9, 2007
is it possible to combine two tables(not related with each other) as onde like we put them together with our hands physically.
this is what i want;here's the two tables to be combined:
table-a ______________table-b
15 _ a ____________ ny _____ arena
25 _ d ____________ fg_____ metus
35 _ f ____________
45 _ f ____________
these two tables above will become table-c like below;
table-c
15__arena
25__metus
35__null
45__null
but a warning , no relation between the tables and, row counts will not be equal anytime one of them may have more rows then the other ,ihe tried many join methods but gave me allways the lots of results more than i want ,please anyone can help? is it possible to put two tables physically like we put them together with our hands ?
View 14 Replies
View Related
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
Dec 20, 2007
Hi all,
I am working from a database containing sets of questions and answers relating to maternity episodes. The answer tables are broken up into two tables, freetext answers and standard drop-down answers. The questions relating to these answers are held in the same table, MAT_QUESTIONS.
When I want to view the freetext answers and questions for a certain episode (based on incidentid), the following code works..
SELECT P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, FA.ANSWERTEXT
FROM MAT_FREEANSWERS FA, MAT_QUESTIONS QS,
MAT_INCIDENTS I, MAT_DELIVERY D,
PASMAIN P
WHERE FA.QUESTIONID=QS.ID
AND FA.INCIDENTID=I.INCIDENTID
AND I.INCIDENTID=D.INCIDENTID
AND D.MOTHER_PAS_NO=P.PAS_NO
AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59'
AND QS.QUESTIONNAIREID=2
AND FA.INCIDENTID=4501
and if I want to view the answers to the standard drop down questions, I use the following code...
select P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, A.ANSWER
from MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P,
MAT_QUESTIONNAIRE Q, MAT_QUESTIONNAIREINCIDENTS QI, MAT_QUESTIONS QS,
MAT_ANSWERS A, MAT_INCIDENTANSWERS IA
WHERE I.INCIDENTID=D.INCIDENTID
AND I.INCIDENTID=QI.INCIDENTID
AND D.INCIDENTID=IA.INCIDENTID
AND D.MOTHER_PAS_NO=P.PAS_NO
AND Q.ID=QI.QUESTIONNAIREID
AND QI.QUESTIONNAIREID=QS.QUESTIONNAIREID
AND QS.ID=A.QUESTIONID
AND A.ID=IA.ANSWERID
AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59'
AND Q.ID=2
AND I.INCIDENTID=4501
However, I would like to join the two together, as I would like to see a list of all questions and answers posed to a particular patient. But, as there are so many tables in common, I'm having difficulty doing this.
Would appreciate any help!
Fiona
View 10 Replies
View Related
Mar 11, 2008
I have a database, and I need data from 3 tables.
From the first table I just need the primary key: issueID.
I can make a left outer join with my second table so I can have this result.
select A.issueID, B.projectID
from issuerequest as A left outer join projects as B on (A.issueID=B.referenceID)
group by A.issueID
gives:
100 | null
101 | P003
102 | P002
103 | null
...
When the value in the second column is null, I need to check the value
in the 3th table.
The join between the first and the thirth is:
select C.issueID, min(D.project) as Project
from issuerequest as C left outer join ProductCustomer
on (C.customer = D.customerID) and
(C.product=D.produktID)
where actuellproduct='1'
group by C.issueID
this gives a result as:
100 | P002
100 | P003
101 | P004
102 | P002
103 | null
...
What I actually want is just 2 columns with
in the first IssueID and in the second Project
and no duplicate data. Everything (except null) is more
important then the same value in column 3.
100 | P002
101 | P003
102 | P002
103 | null
I've tried with except, but can't get all duplicate
data out.
View 3 Replies
View Related
