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

Audi            BMW           Land Rover
3                7                1
10               6
15

;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 

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

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

---

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.

---

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