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
 Pivoting 2 fields
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

RiciH83
Starting Member

1 Posts

Posted - 05/13/2014 :  11:10:35  Show Profile  Reply with Quote
Hi,

I'm a sql newby and wondering if somebody can help.

I have a table with example data like below:

FranchiseName:          UseID:
Audi                           3
Audi                          10
Audi                          15
BMW                            7
BMW                            6
Land Rover                     1


What i want is to be able view it as

Audi            BMW           Land Rover
3                7                1
10               6
15


New franchises add on and some drop off all the time so i dont want to hard code the individual columns.

Is this possible?

Thank you

gbritton
Aged Yak Warrior

642 Posts

Posted - 05/13/2014 :  11:59:50  Show Profile  Reply with Quote
You're going to need to use dynamic SQL. The query that generates the dynamic part would pull the franchise names from the source to build a column and pull the UseIds. Then, execute the dynamic part. If you're not familiar with Dynamic SQL, spend a little time reading up on it. It's not too hard. Think of it as writing a C program that generates a C program from some data, then compiles and executes the result. Actually, dynamic SQL is easier than that, but its one analogy.
Go to Top of Page

gbritton
Aged Yak Warrior

642 Posts

Posted - 05/14/2014 :  15:37:25  Show Profile  Reply with Quote
Dunno if you made progress. The first thing I did was try to produce the desired results from the fixed, sample input. I succeeded with this query (#t contains your data as above):


;with

	-- one cte for each franchise
	f1 as (select *, rn=ROW_NUMBER() over (order by (select 1)) 
		   from #t where FranchiseName = 'Audi'),
	f2 as (select *, rn=ROW_NUMBER() over (order by (select 1)) 
	       from #t where FranchiseName = 'BMW'),
	f3 as (select *, rn=ROW_NUMBER() over (order by (select 1)) 
	       from #t where FranchiseName = 'Land Rover'),

	-- maximun number of UseIds per FranchiseName (Not maximum UseID)
	UseIdCount(UseIdCount) as (
		select count(UseId)
		from #t
		group by FranchiseName
	),

	-- Table of numbers to use for final query
	N10 as (select n from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n(n)),
	N1000 as (select n = row_number() over(order by (select 1)) from N10, N10 n100, N10 n1000),

	-- Only need enough numbers to cover the max UseId count.
	N as (select top (select max(UseIdCount) from UseIdCount) n from N1000)

select f1.UseID as Audi, f2.useid as BMW, f3.useid as 'Land Rover' 
from N
left join f1 on f1.rn = N.n left join f2 on f2.rn = N.n  left join f3 on f3.rn = N.n 

Edited by - gbritton on 05/14/2014 15:46:56
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 05/14/2014 :  16:17:57  Show Profile  Reply with Quote
Here's a dynamic version:

create table #FranUse(FranchiseName varchar(15), UseID int)
insert #franuse
select 'Audi',                           3 union all
select 'Audi',                          10 union all
select 'Audi',                          15 union all
select 'BMW',                            7 union all
select 'BMW',                            6 union all
select 'Land Rover',                     1

--get all the current franchiseNames as a column list
declare @cols varchar(max)
       ,@sql varchar(max)
select @cols = coalesce(@cols + ',[' + franchiseName + ']', '[' + franchiseName + ']')
from   (select distinct franchiseName from #franuse) d

--build the PIVOT statement to exec
set @sql = '
select ' + @cols + '
from   (select rank() over (partition by FranchiseName order by useid) as rowid
              , franchisename
              , useid 
        from  #franUse
        ) fu
pivot (max(useid) for FranchiseName in (' + @cols + ')) as p
'
--exec it
exec (@sql)

OUTPUT:

Audi        BMW         Land Rover
----------- ----------- -----------
3           6           1
10          7           NULL
15          NULL        NULL


Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 05/14/2014 :  23:27:25  Show Profile  Reply with Quote
Someone asked me (via direct email) how the rank function prevented only one max(useid) row and allowed for the desired output of one row per useid. Good question as PIVOT behavior is a little mysterious - at least it was to me when it was introduced.

The documentation specifies that you need to use an aggregate in the pivot clause. The behavior seems to group by all other columns in your source. Then takes those results and pivots the specified values from the IN clause to rows. So the only way to get the desired number of rows in this case was to manufacture a value that was unique to UseID (for each franchiseName). I chose to do that with a rank function but row_number would have worked the same as long as you have the partition in there. Without the partition then there would be 6 rows in the result instead of 3.

Be One with the Optimizer
TG
Go to Top of Page

gbritton
Aged Yak Warrior

642 Posts

Posted - 05/15/2014 :  07:49:34  Show Profile  Reply with Quote
quote:
Originally posted by TG

Someone asked me (via direct email) how the rank function prevented only one max(useid) row and allowed for the desired output of one row per useid. Good question as PIVOT behavior is a little mysterious - at least it was to me when it was introduced.

The documentation specifies that you need to use an aggregate in the pivot clause. The behavior seems to group by all other columns in your source. Then takes those results and pivots the specified values from the IN clause to rows. So the only way to get the desired number of rows in this case was to manufacture a value that was unique to UseID (for each franchiseName). I chose to do that with a rank function but row_number would have worked the same as long as you have the partition in there. Without the partition then there would be 6 rows in the result instead of 3.

Be One with the Optimizer
TG



I found a Microsoft whitepaper on PIVOT/UNPIVOT that states the behaviour explicitly:

http://www.ccsglobaltech.com/pdf/PivotAndUnPivot.pdf

Page 3, Paragraph 3:

quote:
PIVOT operates on a table, like other operations,
converting from narrow form to wide form. The column
‘Sales’ in SalesTable provides values for the pivoted
columns, while the values of the Month column define the
mapping describing in which column the value from Sales
belongs. The IN list describes the values of interest from
the Month column as well as the names of the new
columns to create in PIVOT. The remaining columns
from SalesTable, though not listed, implicitly divide the
rows of SalesTable into groups.
Each group of rows
becomes a single output row as a result of PIVOT.


This is in reference to a simple query:


SELECT * FROM
(SalesTable PIVOT (Sales for Month in ('Jan', 'Feb', 'Mar'))


Note that the example query is not valid in SQL Server. The paper deals with theoretical implications and implementation strategies, but gives a clear description of the grouping strategy.
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.06 seconds. Powered By: Snitz Forums 2000