Simple Pivot Question - With Source data too!

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

Simple Pivot Question - With Source data too!

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2014-01-20 : 10:57:00

Hi All! I have the following table:
[code]
USERNAME CITY
Bill.Smith Toronto
Bill.Smith New York
Bill.Smith Boston
Jane.Doe Atlanta
Jane.Doe Seattle
[/CODE]
Most users only have 1 city, however they can be assigned up to 4 multiple cities which span multiple rows (there's hundreds of different Cities).

I'd like to Pivot out the city names horzontially, so they span on different columns, with only the unique user ID on each line, so it appears as follows:
[CODE]
USERNAME CITY1 CITY2 CITY3
Bill.Smith Toronto New York Boston
Jane.Doe Atlanta Seattle

[/CODE]
How can I acheive this? Here's code with a sample table:
[CODE]
CREATE TABLE #TEMP (USERNAME VARCHAR(255),CITY VARCHAR(255) );

INSERT INTO #TEMP
SELECT 'BILL.SMITH','TORONTO' UNION
SELECT 'BILL.SMITH','NEW YORK' UNION
SELECT 'BILL.SMITH','CHICAGO' UNION
SELECT 'JANE.DOE','BOSTON' UNION
SELECT 'JANE.DOE','ANCHORAGE'

SELECT USERNAME, CITY FROM #TEMP
[/CODE]

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-20 : 21:04:09

If maximum is 4 multiple cities per user, use CASE clause is one of way, like following
----------------------------------------------------------------------------

SELECT
	USERNAME,
	MAX(CASE WHEN ROWNUM = 1 THEN CITY END) CITY1,
	MAX(CASE WHEN ROWNUM = 2 THEN CITY END) CITY2,
	MAX(CASE WHEN ROWNUM = 3 THEN CITY END) CITY3,
	MAX(CASE WHEN ROWNUM = 4 THEN CITY END) CITY4
FROM (
	SELECT
		ROW_NUMBER() OVER(PARTITION BY USERNAME ORDER BY USERNAME) ROWNUM,
		USERNAME,
		CITY
	FROM #TEMP) TBL
GROUP BY USERNAME

-------------------------------------
From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 05:30:40

[code]
SELECT USERNAME,[1] AS CITY1,[2] AS CITY2,[3] AS CITY3,[4] AS CITY4
FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY USERNAME ORDER BY USERNAME) ROWNUM,
USERNAME,
CITY
FROM #TEMP) TBL
PIVOT (MAX(CITY) FOR ROWNUM IN ([1],[2],[3],[4]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2014-01-21 : 10:32:24

Thanks Nagino - Worked perfectly!
Thanks anyways Visakh, but for some reason it still duplicated the Username on multiple rows ?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 06:39:11

quote:
Originally posted by funk.phenomena

Thanks Nagino - Worked perfectly!
Thanks anyways Visakh, but for some reason it still duplicated the Username on multiple rows ?!

Nope that not true unless you've some additional columns which you've not shown in sample data above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources