Need help with a simple query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Need help with a simple query

Author

Topic

procyon4476
Starting Member

4 Posts

Posted - 2009-06-14 : 17:28:24

Hello, new to sql and had a quick question about how to write a query, if its even possible.

My data is a little more complicated than this but this should give you a good idea what I want to do. Say I have one table that has names of people:

NAME
BRIAN
BOBBY

Second table has the same names and cities they have lived in, duplicating names are on multiple lines:

NAME   CITY
BRIAN    PHOENIX
BRIAN    SEATTLE
BOBBY    MIAMI
BOBBY    ATLANTA
BOBBY    DALLAS

I want to write a query that shows every name on a single line and showing each city they have lived in left to right, each city in multiple columns. For the sake of argument, say I know the maximum number of cities that any person has lived in. So the query results would look something like this:

NAME     CITY1   CITY2   CITY3
BRIAN    PHOENIX SEATTLE
BOBBY    MIAMI   ATLANTA DALLAS

Ok, so I guess for this example the first table is pointless, but in my actual data it matters.

Anyway to write something like this? Multiple queries are ok if necessary. Thanks for any help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-14 : 21:13:02

[code]
DECLARE @people TABLE
(
[name] varchar(10)
)

DECLARE @city TABLE
(
[name] varchar(10),
[city] varchar(10)
)

INSERT INTO @people
SELECT 'BRIAN' UNION ALL
SELECT 'BOBBY'

INSERT INTO @city
SELECT 'BRIAN', 'PHOENIX' UNION ALL
SELECT 'BRIAN', 'SEATTLE' UNION ALL
SELECT 'BOBBY', 'MIAMI' UNION ALL
SELECT 'BOBBY', 'ALANTA' UNION ALL
SELECT 'BOBBY', 'DALLAS'

SELECT [NAME] = [name],
[CITY1] = [1],
[CITY2] = [2],
[CITY3] = [3],
[CITY4] = [4],
[CITY5] = [5]
FROM
(
SELECT p.[name], c.[city],
seq_no = row_number() OVER (PARTITION BY p.[name] ORDER BY c.[city])
FROM @people p
INNER JOIN @city c ON p.[name] = c.[name]
) d
pivot
(
MAX([city])
FOR seq_no IN ([1], [2], [3], [4], [5])
) p

/*
NAME CITY1 CITY2 CITY3 CITY4 CITY5
---------- ---------- ---------- ---------- ---------- ----------
BOBBY ALANTA DALLAS MIAMI NULL NULL
BRIAN PHOENIX SEATTLE NULL NULL NULL

(2 row(s) affected)
*/
[/code]

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

procyon4476
Starting Member

4 Posts

Posted - 2009-06-14 : 23:19:50

Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources