Multple rows from one table into one row in output

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

Multple rows from one table into one row in output

Author

Topic

emmodnar
Starting Member

7 Posts

Posted - 2010-12-27 : 19:02:24

SELECT CATEGORY, DOCUMENTID, PUBDNUMBER, AUTHOR1, AUTHOR2
FROM DOCTABLE
JOIN AUTHORS ON DOCTABLE.DOCUMENTID = AUTHORS.DOCUMENTID
WHERE AUTHOR.STATUS IN ('Active', 'Associate', 'Second');

Just an example of what I need to do, above. The output would look like this --

Category DocumentID PubDNumber Active-Author Associate-Author Sec
Article 001 ABC123 Joe Blow Jack Attack Mac
Trial 002 123DDD Rhonda Bee Mick Jagger Ric

How the heck to I do this?

emmodnar

emmodnar
Starting Member

7 Posts

Posted - 2010-12-28 : 23:00:04

Hello? I'm thinking this is something that's not easy to do in SQL, unless you use 1) Cursors or 2) Create a file and insert the data into it?

Any hints?

I'm on MSSQL Server 2005

emmodnar

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-29 : 12:21:46

I don't understand what what the issue is. Can you post DDL, DML and expected output? This link can help you put that togehter:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-12-30 : 01:36:11

perhaps this ?


SELECT	CATEGORY, DOCUMENTID, PUBDNUMBER, [Active], [Associate], [Second]
FROM	(
		SELECT 	CATEGORY, DOCTABLE.DOCUMENTID, PUBDNUMBER, AUTHOR, STATUS
		FROM 	DOCTABLE
			JOIN AUTHORS ON DOCTABLE.DOCUMENTID = AUTHORS.DOCUMENTID
		WHERE 	AUTHORS.STATUS IN ('Active', 'Associate', 'Second')
	) D
	PIVOT
	(
		MAX(AUTHOR)
		for	STATUS IN ([Active], [Associate], [Second])
	) P

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources