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
 General SQL Server Forums
 New to SQL Server Programming
 Simple Pivot Question - With Source data too!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 01/20/2014 :  10:57:00  Show Profile  Reply with Quote
Hi All! I have the following table:

USERNAME   CITY
Bill.Smith Toronto
Bill.Smith New York
Bill.Smith Boston
Jane.Doe   Atlanta
Jane.Doe   Seattle 

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:

USERNAME   CITY1     CITY2      CITY3
Bill.Smith Toronto   New York   Boston
Jane.Doe   Atlanta   Seattle


How can I acheive this? Here's code with a sample table:

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

Edited by - funk.phenomena on 01/20/2014 15:57:54

nagino
Yak Posting Veteran

Japan
68 Posts

Posted - 01/20/2014 :  21:04:09  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/21/2014 :  05:30:40  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 01/21/2014 :  10:32:24  Show Profile  Reply with Quote
Thanks Nagino - Worked perfectly!
Thanks anyways Visakh, but for some reason it still duplicated the Username on multiple rows ?!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/22/2014 :  06:39:11  Show Profile  Reply with Quote
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
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