0

Looking to pivot/transpose with tsql (or something else)? on a table with multiple rows per item number, one row per Code (unit of measure). It would have to be dynamic as there could be lot of different unit of measure codes per item.

Current data table:

item unit of measure table right now

select [Item No_], Code, [Qty_ per Unit of Measure], Weight, Cubage
from [mycompany$Item Unit of Measure]
where [Item No_] in ('007967','007968')

Desired output would be: Desired result output

Addiotional info We have a table that holds all the possible Unit of Measure codes that perhaps could be used in the final code?

enter image description here

select
Code
from [mycompany$Unit of Measure]

How to acchieve this, and what would the SQL code look like?

Suggested solution from @Larnu :

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(Code)
                      FROM [mycompany$Unit of Measure]
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query =

'SELECT *
FROM
(
  SELECT
    o.[Item No_],
    p.Code,
    o.Weight
  FROM [mycompany$Item Unit of Measure] AS o
  INNER JOIN [mycompany$Unit of Measure] AS p ON o.Code = p.Code
) AS t
PIVOT 
(
  MAX(Weight) 
  FOR Code IN( ' + @cols + ' )' +
' ) AS p ; ';

 execute(@query);

However this only give max value for Weight and not Cubage. Also it doesn't meet the desired end results as column heads are not tagged with CODE.Cubage, CODE.Weight etc. (PALLET.Weight, PALLET.Cubage) Screenshot of results with above code: enter image description here

Frank Jonassen
  • 15
  • 5
  • 1
    Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Thom A May 14 '20 at 10:06
  • Does this answer your question? [Dynamic Pivot Columns in SQL Server](https://stackoverflow.com/q/14797691/2029983) – Thom A May 14 '20 at 10:07
  • Why do you keep undoing my edit to display your images... – Thom A May 14 '20 at 10:07
  • Looking at your second link (https://stackoverflow.com/questions/14797691/dynamic-pivot-columns-in-sql-server) i got this working locally but it only works for 1 column (i.e. weight) but when i try to add i.e. Cubage then it doesn't work. – Frank Jonassen May 14 '20 at 10:49
  • Considering that the pivot is dynamic, that doesn't make much sense. Put your actual attempt in your question. – Thom A May 14 '20 at 10:52
  • Added the altered code from your suggested answer in the original post and listed issues with that. – Frank Jonassen May 14 '20 at 11:40

0 Answers0