How to pivot multiple columns without aggregation

Question

I use SqlServer and i have to admit that i'm not realy good with it ... This might be and easy question for the advanced users (I hope)

I have two tables which look like this

First table (ID isn't the primary key)

ID      IdCust   Ref
1       300      123
1       300      124
2       302      345

And the second (ID isn't the primary key)

ID     Ref      Code    Price
1      123      A       10
1      123      Y       15
2      124      A       14
3      345      C       18

In the second table, the column "Ref" is the foreign key of "Ref" in the first table

I'm trying to produce the following output:

[EDIT] The column "Stock", "Code" and "Price" can have x values, so I don't know it, in advance...

I tried so many things like "PIVOT" but it didn't give me the right result, so i hope someone can solve my problem ...

Answer 1

Use row_number() function and do the conditional aggregation :

select id, IdCust, Ref,
       max(case when Seq = 1 then stock end) as [Stock A], -- second table *id*
       max(case when Seq = 1 then code end) as [Code 1],
       max(case when Seq = 1 then price end) as [Price1],
       max(case when Seq = 2 then stock end) as [Stock B], -- second table *id*
       max(case when Seq = 2 then code end) as [Code 2],
       max(case when Seq = 2 then price end) as [Price2]
from (select f.*, s.Id Stock, s.Code, s.Price,
             row_number() over (partition by f.Ref order by s.id) as Seq
     from first f
     inner join second s on s.Ref = f.Ref 
     ) t
group by id, IdCust, Ref;

However, this would go with known values else you would need go with dynamic solution for that.

Answer 2

@YogeshSharma's provided an excellent answer.

Here's the same done using Pivot; SQL Fiddle Demo.

Functionally there's no difference between the two answers. However, Yogesh's solution's simpler to understand, and performs better; so personally I'd opt for that... I included this answer only because you mention PIVOT in the question:

select ft.Id
, ft.IdCust
, ft.Ref
, x.Stock1
, x.Code1
, x.Price1
, x.Stock2
, x.Code2
, x.Price2
from FirstTable ft
left outer join (
  select Ref
  , max([Stock1]) Stock1
  , max([Stock2]) Stock2
  , max([Code1]) Code1
  , max([Code2]) Code2
  , max([Price1]) Price1
  , max([Price2]) Price2
  from
  (
    select Ref
    , Id Stock
    , Code
    , Price
    , ('Stock' + cast(Row_Number() over (partition by Ref order by Id, Code) as nvarchar)) StockLineNo
    , ('Code' + cast(Row_Number() over (partition by Ref order by Id, Code) as nvarchar)) CodeLineNo
    , ('Price' + cast(Row_Number() over (partition by Ref order by Id, Code) as nvarchar)) PriceLineNo
    from SecondTable
  ) st
  pivot (max(Stock) for StockLineNo in ([Stock1],[Stock2])) pvtStock
  pivot (max(Code) for CodeLineNo in ([Code1],[Code2])) pvtCode
  pivot (max(Price) for PriceLineNo in ([Price1],[Price2])) pvtPrice
  Group by Ref
) x
on x.Ref = ft.Ref
order by ft.Ref

Like Yogesh's solution, this will only handle as many columns as you specify; it won't dynamically alter the number of columns to match the data. For that you'd need to do dynamic SQL. However; if you need to do that, it's more likely you're attempting to solve the problem in the wrong way... so consider your design / determine if you really need additional columns per result rather than additional rows / some alternate approach...

---

Here's a Dynamic SQL implementation based on @YogeshSharma's answer: DBFiddle

declare @sql nvarchar(max) = 'select id, IdCust, Ref'
select @sql = @sql + '
        ,max(case when Seq = 1 then stock end) as [Stock' + rowNumVarchar + '] 
        ,max(case when Seq = 1 then code end) as [Code' + rowNumVarchar + ']
        ,max(case when Seq = 1 then price end) as [Price' + rowNumVarchar + ']
'
from 
(
    select distinct cast(row_number() over (partition by ref order by ref) as nvarchar) rowNumVarchar
    from second s
) z
set @sql = @sql + '
    from (select f.*, s.Id Stock, s.Code, s.Price,
                 row_number() over (partition by f.Ref order by s.id) as Seq
         from first f
         inner join second s on s.Ref = f.Ref 
         ) t
    group by id, IdCust, Ref;
'
print @sql --see what the SQL produced is
exec (@sql)

(Here's a SQL Fiddle link for this one; but it's not working despite the SQL being valid