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  Combining multiple rows into one in a table

Author  Topic  akgyun Starting Member 2 Posts Posted - 2008-05-07 : 19:47:19 Dear all SQL Experts!! I am using ACCESS SQL query to prepare a simple report. I have these tables that show how each person can have one to many PIN numbers. Each PIN number assigned can be of 2 types – Personal or Business [PIN_Mapping] table shows the mapping between a person and PIN number, and its assigned type. Below are the table structures: PERSON Person_ID | FirstName | LastName ------------------------------------------------- 2 Ryan Kang PIN PIN_ID | PIN_NO ----------------------- 13 1329 21 2411 PIN_MAPPING Person_ID | PIN_ID | IsPersonal ------------------------------------------- 2 13 Yes 2 21 No This is the SQL query used for reporting: SELECT [LastName] & " " & [FirstName] AS Name, IIf([PIN_MAPPING]![IsPersonal]=True,"",[PIN]![PIN_NO]) AS Business_PIN, IIf([PIN_MAPPING]![IsPersonal]=False,"",[PIN]![PIN_NO]) AS Personal_PIN FROM [PIN] INNER JOIN ([PERSON] INNER JOIN [PIN_MAPPING] ON [PERSON].[Person_ID]=[PIN_MAPPING].[PersonID]) ON [PIN].[PIN_ID]=[PIN_MAPPING].[PIN_ID] ORDER BY [LastName] & " " & [FirstName]; This is the result of query: Name Personal PIN Business PIN ------------------------------------------------------------------ Ryan Kang 1329 Ryan Kang 2411 But This is not what I want!!! How can I make it look like Name Personal PIN Business PIN ------------------------------------------------------------------ Ryan Kang 1329 2411 By combining the row rows into one Any idea? It looks rather a straightforward job but I could not come up with SQL commands that can do this. Any help from SQL experts would be so appreciated Thanks!!