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:

The result what i want

[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 ...

Indeed you need PIVOT. It would be interesting to see the code you tried. Question: How many line will the same Ref have max. at second table? two? — George Menoutis
– George Menoutis, Commented May 25, 2018 at 15:36

Yogesh Sharma · Accepted Answer · 2018-05-25 17:02:49Z

3

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.

edited May 25, 2018 at 17:02

answered May 25, 2018 at 15:40

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SylvainL Over a year ago

Thank you for the answer which is realy interesting But, as you said, i need to use dynamic solution, because I can't know in advance the number of "Seq" Do you have any idea, for the dynamic solution ?

JohnLBevan · Accepted Answer · 2018-05-28 08:43:47Z

3

@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

edited May 28, 2018 at 8:43

answered May 25, 2018 at 16:27

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

6 Comments

SylvainL Over a year ago

Thank you for your solution ! But yeah, the problem (sorry I forget to write this in my first post) is this the same because I have to make dynamix SQL ... Unfortunately I have to add it per columns :/ What do you mean with "additional rows" ? Do you have an additional solution for dynamic SQL ?

JohnLBevan Over a year ago

No worries, dynamic SQL created... but as mentioned normally needing to add columns at runtime is a sign that something's wrong with the design. By addtional rows I simply mean that for 3 entries with the same REF value your current solution requires you to add 3 sets of stock, code & price columns; whereas you could have those same results show by having the fixed number of columns (i.e. one price column, one code, and one stock), and having one row per result.

SylvainL Over a year ago

Thank you for your answer. I tried both answer and that didn't work ... The problem come from ... me :/ I made a huge misstake, i do not work on sql Server but on mySql ... realy sorry for the wrong post. I'm gonna edit my post. I tried your solution and edit some informations and I have an error line 25 "('Stock' + cast(Row_Number() over (partition by Ref order by Id, Code) as nvarchar)) StockLineNo" Error : [...] right syntax to use near 'over (partition by Ref order by Id, Code) as nvarchar)) StockLineNo , ('Code' at line 25). Do you have a suggestion ?

JohnLBevan Over a year ago

No worries. I'm not that familiar with the more advanced bits of MySQL... I'd suggest you're best off making a new post with the correct tags and pointing to this question saying "I'm trying to create dynamic SQL in MySQL to achieve the same as this T-SQL", and marking Yogesh's answer above as the accepted answer for this post as Yogesh resolved your original question with the best solution.

SylvainL Over a year ago

Not a bad idea :) I cannot upvote your both answer, but i will follow your suggestion. Thank you for your time and for your answer ;)

|

Collectives™ on Stack Overflow

How to pivot multiple columns without aggregation

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related