Multiple values listed as separate columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple values listed as separate columns?

Author

Topic

JeTmAn81
Starting Member

11 Posts

Posted - 2013-02-07 : 16:39:28

Anybody know how to do this? I'm talking about a situation where I'm selecting from a table which contains a list of people. There is another table that contains a list of interests (Basketball, Choir, etc.) selected by each one of those people, with a foreign key tying it back to that first table. But when I try to select things like this:

Select ID, FirstName, LastName,
(Select top 1 Interest from Interests Where Interests.PersonID = P.ID)
as Interest1,
(Select top 1 Interest from Interests Where Interests.PersonID = P.ID Where not Interest = ((Select top 1 Interest from Interests Where Interests.PersonID = P.ID) ))
as Interest2

From Persons P

So I can keep selecting different interests from that same table but in order to make sure I don't just get the same interests I already selected I have to reiterate the entire query from the previous interest so I can specifically rule out getting that value.

This seems like a super clunky way to do this, but I have no idea what the elegant solution would be. The resident DB expert at my place of work says he doesn't have any better ideas. Help!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 17:03:41

May be better to use PIVOT operator. Here is an example that you can copy and paste to an SSMS window to run. Just make sure you don't have tables named A and B in your database

CREATE TABLE A(Id INT NOT NULL PRIMARY KEY , FirstName VARCHAR(32), LastName VARCHAR(32))
CREATE TABLE B(Id INT NOT NULL PRIMARY KEY , PersonId INT NOT NULL  REFERENCES A(id),
Interest VARCHAR(32));

INSERT INTO A VALUES (1,'Joe','Smith'),(2,'Mary','Jones'),(3,'Kevin','Connor');

INSERT INTO B VALUES (1, 1,'Tennis'),(2,1,'Football'),(3,1,'Racketball'),
(4,2,'Swimming'),(5,2,'Tennis'),(6,3,'Dancing');

SELECT 
	ID,
	FirstName,
	LastName,
	[1] AS Interest1,
	[2] AS Interest2,
	[3] AS Interest3
FROM
(
	SELECT a.ID, a.FirstName, a.LastName, b.Interest ,
		ROW_NUMBER() OVER (PARTITION BY a.Id ORDER BY b.Id) AS N FROM A INNER JOIN B 
	ON a.Id = b.PersonId
) as x PIVOT(MAX(Interest) FOR N IN ([1],[2],[3]))P

DROP TABLE B,A;

JeTmAn81
Starting Member

11 Posts

Posted - 2013-02-07 : 17:52:01

quote:

Originally posted by James K

May be better to use PIVOT operator. Here is an example that you can copy and paste to an SSMS window to run. Just make sure you don't have tables named A and B in your database

CREATE TABLE A(Id INT NOT NULL PRIMARY KEY , FirstName VARCHAR(32), LastName VARCHAR(32))
CREATE TABLE B(Id INT NOT NULL PRIMARY KEY , PersonId INT NOT NULL  REFERENCES A(id),
Interest VARCHAR(32));

INSERT INTO A VALUES (1,'Joe','Smith'),(2,'Mary','Jones'),(3,'Kevin','Connor');

INSERT INTO B VALUES (1, 1,'Tennis'),(2,1,'Football'),(3,1,'Racketball'),
(4,2,'Swimming'),(5,2,'Tennis'),(6,3,'Dancing');

SELECT 
	ID,
	FirstName,
	LastName,
	[1] AS Interest1,
	[2] AS Interest2,
	[3] AS Interest3
FROM
(
	SELECT a.ID, a.FirstName, a.LastName, b.Interest ,
		ROW_NUMBER() OVER (PARTITION BY a.Id ORDER BY b.Id) AS N FROM A INNER JOIN B 
	ON a.Id = b.PersonId
) as x PIVOT(MAX(Interest) FOR N IN ([1],[2],[3]))P

DROP TABLE B,A;

Thanks, I think this might be exactly what I need. I do not think I have used pivots before. Is there any trick to using this within a larger query, say if I wanted to select from multiple pivots (ethnicity values in addition to interest values) as well as selecting multiple columns from the main table?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 18:06:56

You can use multi-column PIVOT, use pivot when the column values are not known in advance etc. But you may have to use dynamic pivot for doing those. Take a look at these blogs:

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx
http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources