SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Multiple values listed as separate columns?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JeTmAn81
Starting Member

11 Posts

Posted - 02/07/2013 :  16:39:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 02/07/2013 :  17:03:41  Show Profile  Reply with Quote
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;
Go to Top of Page

JeTmAn81
Starting Member

11 Posts

Posted - 02/07/2013 :  17:52:01  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 02/07/2013 :  18:06:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000