SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 create query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eugz
Posting Yak Master

195 Posts

Posted - 04/18/2013 :  14:56:58  Show Profile  Reply with Quote
Hi All.

I have table that looks like:
Case_Name........Time..............ItemID.....Label............RecNo
===================================================
35316E05.10G.....6:46:45 AM.....4...........Last Name......SHARIF
35316E05.10G.....6:46:45 AM.....5...........First Name......YANIS
35316E05.10G.....6:47:00 AM.....1...........Record #........262-50-45
35316E01.15G.....4:46:15 AM.....4...........Last Name.......DOU
35316E01.15G.....4:46:15 AM.....5...........First Name......JOHN
35316E01.15G.....4:47:01 AM.....1...........Record #........232-21-25

How to create query that result of data will displaying like one record? I mean like this:

Case_Name........Time...............Last Name......First Name.....Record No
======================================================
35316E05.10G.....6:46:45 AM.....SHARIF..........YANIS..........262-50-45
35316E01.15G.....4:46:15 AM.....DOU..............JOHN...........232-21-25

Thanks.

Edited by - eugz on 04/18/2013 15:00:33

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 04/18/2013 :  15:13:02  Show Profile  Reply with Quote
SELECT
	Case_Name,
	MIN([Time]) AS [Time],
	MAX(CASE WHEN Label = 'Last Name' THEN RecNo END) AS LastName,
	MAX(CASE WHEN Label = 'First Name' THEN RecNo END) AS FirstName,
	MAX(CASE WHEN Label = 'Record #' THEN RecNo END) AS [Record No]
FROM
	YourTable
GROUP BY
	Case_Name;
Go to Top of Page

eugz
Posting Yak Master

195 Posts

Posted - 04/18/2013 :  17:00:35  Show Profile  Reply with Quote
Hi James. Thanks for replay.

Sorry, I forgot to tell that I need that query for Access 2010 database. If you familiar with that application I will appreaciate for help. In Access I don't know how used CASE statment. I create like this:

SELECT 
IIf(Label="LastName",RecNo) as LName
,IIf(Label="FirstName",RecNo) as FName
,IIf(Label="Record#",RecNo) as RecordNo
FROM Table1
;

and got result

LName......FName......RecordNo
SHARIF
..............YANIS
.............................262-50-45

How to fix to get one line?

Thanks.

Edited by - eugz on 04/18/2013 17:06:41
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 04/18/2013 :  17:21:10  Show Profile  Reply with Quote
I don't know enough to suggest the exact syntax of the query you should use on Acces, but you would need an aggregate function and a group by clause. So perhaps this?
SELECT 
Case_Name
,MAX(IIf(Label="LastName",RecNo)) as LName
,MAX(IIf(Label="FirstName",RecNo)) as FName
,MAX(IIf(Label="Record#",RecNo)) as RecordNo
FROM Table1
GROUP BY
	Case_name
Go to Top of Page

eugz
Posting Yak Master

195 Posts

Posted - 04/18/2013 :  17:47:59  Show Profile  Reply with Quote
Thanks a lot. Very helpful.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000