Combining multiple records into one record

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 records into one record

Author

Topic

sarorelasoul
Starting Member

29 Posts

Posted - 2007-12-20 : 14:19:04

Hi All,

I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record,
Table1 has the primary key
id
--
1
2
3
4
Table2 has the follwing records
id year subject
-----------------
1 2000 English
1 2002 French
2 2004 English
2 2005 English
2 2006 English
3 2007 French
I want the result to be like this
id 2000 2001 2002 2003 2004 2005 2006 2007
-----------------------------------------------------------
1 English null French null null null null null
2 null null null null English English English null
3 null null null null null null null English

Appretiate your assistance

sarorelasoul
Starting Member

29 Posts

Posted - 2007-12-20 : 14:29:01

Sorry last record should look like this
3 null null null null null null null French

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 14:32:30

SELECT ID,
MAX(CASE WHEN [Year] = 2000 THEN [Subject] ELSE NULL END) AS [2000],
MAX(CASE WHEN [Year] = 2001 THEN [Subject] ELSE NULL END) AS [2001],
MAX(CASE WHEN [Year] = 2002 THEN [Subject] ELSE NULL END) AS [2002],
MAX(CASE WHEN [Year] = 2003 THEN [Subject] ELSE NULL END) AS [2003],
MAX(CASE WHEN [Year] = 2004 THEN [Subject] ELSE NULL END) AS [2004],
MAX(CASE WHEN [Year] = 2005 THEN [Subject] ELSE NULL END) AS [2005],
MAX(CASE WHEN [Year] = 2006 THEN [Subject] ELSE NULL END) AS [2006],
MAX(CASE WHEN [Year] = 2007 THEN [Subject] ELSE NULL END) AS [2007]
FROM Table2
GROUP BY ID
ORDER BY ID

E 12°55'05.25"
N 56°04'39.16"

sarorelasoul
Starting Member

29 Posts

Posted - 2007-12-20 : 15:04:37

Thanks for your help, it's working
But a small question what does the MAX do here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 15:06:14

To select the one and only record for the [200x] column that is not NULL.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources