create query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create query

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2013-04-18 : 14:56:58

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-18 : 15:13:02

[code]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;[/code]

eugz
Posting Yak Master

210 Posts

Posted - 2013-04-18 : 17:00:35

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-18 : 17:21:10

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

eugz
Posting Yak Master

210 Posts

Posted - 2013-04-18 : 17:47:59

Thanks a lot. Very helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources