<div>
Not very clear to me! On row 2, WIP_WH has a value of 06. Is that an error?
</div>

<div>
and what is the math around your QTY columns?
</div>

<div>
I see what you mean now, let me think about it. Do you only have WH id 06 and 35 or can be anything?
</div>

<div>
No, not an error, so let me write it. If the original row 2 was : &nbsp;(i will start at column Size_Desc not to right everything) R41 &nbsp; 35 &nbsp; 35 &nbsp; 06 &nbsp; 2 &nbsp; 0 &nbsp; 3 &nbsp; 2 &nbsp; 5 then, my wishing results would be : R41 &nbsp; 35 &nbsp; 2 &nbsp; 0 &nbsp; 0 &nbsp; 2 &nbsp; 2 R41 &nbsp; 06 &nbsp; 0 &nbsp; 0 &nbsp; 3 &nbsp; 0 &nbsp; 3 note that column avail now &nbsp;is &nbsp;OH QTY -&nbsp; PK QTY and OTS is OH - PK + WIP so basically you see, I arrived with 2 rows instead of 1. and the sum matches 
</div>

<div>
Well, it will be many times only those.. I dont trust so we should make it that we might have another WH number.. lets not pretend its only 06 and 35..
</div>

<div>
For the maths behind QTy columns.. only the avail Now and the OTs has calculation (from the other 3 columns) the main columns OH, PK, WIP has no "math behind" only a fix number.
</div>

<div>
I think I have it. I have replaced OH, PK and WIP with A, B and C to ease my understanding. Give a try to this: <pre><code class="code-10 nolinks" id="code-20-43091313-1">DECLARE @t TABLE ( A_WH INT, B_WH INT, C_WH INT, A_Qty MONEY, B_Qty MONEY, C_Qty MONEY)


INSERT INTO @t
values
(35,35,35,12,39,18)
, (35,35,6,2,0,3)
, (35,35,35,2,0,3)
, (6,35,6,1,39,36)
, (06, 35, 35 &nbsp;, 1, 39, 36)


SELECT * FROM @t


SELECT 1 AS scenario, A_WH AS WH, A_Qty, B_Qty, C_Qty&nbsp; &nbsp;FROM @t&nbsp; &nbsp; &nbsp;&nbsp;WHERE A_WH = B_WH&nbsp; &nbsp; &nbsp;&nbsp;AND A_WH = C_WH
UNION ALL
SELECT 2, A_WH , A_Qty, B_Qty, 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;FROM @t&nbsp; &nbsp; &nbsp;&nbsp;WHERE A_WH = B_WH&nbsp; &nbsp; &nbsp;&nbsp;AND A_WH &lt;&gt; C_WH
UNION ALL
SELECT 3, A_WH , A_Qty, 0, C_Qty&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;FROM @t&nbsp; &nbsp; &nbsp;&nbsp;WHERE A_WH &lt;&gt; B_WH&nbsp; &nbsp; &nbsp;&nbsp;AND A_WH = C_WH
UNION ALL
SELECT 4, A_WH , A_Qty, 0, 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM @t&nbsp; &nbsp; &nbsp;&nbsp;WHERE A_WH &lt;&gt; B_WH&nbsp; &nbsp; &nbsp;&nbsp;AND A_WH &lt;&gt; C_WH


UNION ALL
SELECT 5, B_WH , 0, B_Qty, C_Qty&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;FROM @t&nbsp; &nbsp; &nbsp;&nbsp;WHERE B_WH &lt;&gt; A_WH&nbsp; &nbsp; &nbsp;&nbsp;AND A_WH = C_WH
UNION ALL
SELECT 6, B_WH , 0, B_Qty, 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM @t&nbsp; &nbsp; &nbsp;&nbsp;WHERE B_WH &lt;&gt; A_WH&nbsp; &nbsp; &nbsp;&nbsp;AND A_WH &lt;&gt; C_WH


UNION ALL
SELECT 7, C_WH , 0, 0, C_Qty &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM @t &nbsp; &nbsp; &nbsp;WHERE C_WH &lt;&gt; A_WH &nbsp; &nbsp; &nbsp;AND C_WH &lt;&gt; B_WH
</code></pre>
</div>

<div>
I get what you did, but isn't there a way to do this with one big smart select with pivot or unpivot thing? because right now this is all union all... so very hardcoded, I believe I need 1 select that does all that magic
</div>

<div>
I can't see how a Pivot can be used as you first need to split the various combinations of OH, PH and WIP
</div>

<div>
&gt;&gt;I believe I need 1 select that does all that magic That magic should be hidden in a UDF/Vieew/Stored Proc so once written, you won't see it
</div>

<div>
Eric, look at this, Im almost there, maybe you can help me out : <pre><code class="code-10 nolinks" id="code-20-43091443-1">DECLARE @Table1 TABLE 
&nbsp; &nbsp; ( Style1 varchar(9)
&nbsp; &nbsp;, Style2 varchar(9)
&nbsp; &nbsp;, StyleColor varchar(9)
&nbsp; &nbsp;, Size_Cd&nbsp; &nbsp;varchar(9)
&nbsp; &nbsp;, Size_Desc&nbsp; &nbsp;varchar(9)
&nbsp; &nbsp;, OH_WH&nbsp; &nbsp;varchar(2)
&nbsp; &nbsp;, PK_WH&nbsp; &nbsp;varchar(2)
&nbsp; &nbsp;, WIP_WH varchar(2)
&nbsp; &nbsp;, OH_QTY int, PK_QTY int, WIP_QTY int
)
;

INSERT INTO @Table1 (
&nbsp; &nbsp;&nbsp; Style1
&nbsp; &nbsp;, Style2
&nbsp; &nbsp;, StyleColor
&nbsp; &nbsp;, Size_Cd
&nbsp; &nbsp;, Size_Desc
&nbsp; &nbsp;, OH_WH
&nbsp; &nbsp;, PK_WH
&nbsp; &nbsp;, WIP_WH, OH_QTY, PK_QTY, WIP_QTY )
VALUES
&nbsp; &nbsp; ( '1181401', '216', 'L1', 'L1', 'L40', '', '', '06', 0, 0, 2),
&nbsp; &nbsp;( '3192105', '410', 'R1', 'R1', 'R44', '35', '35', '06', 10, 3, 5),
&nbsp; &nbsp;( '3201010', '410', 'L1', 'L1', 'L50', '35', '', '06', 2, 0, 1);


select * from @Table1


select Style1
&nbsp; &nbsp;, Style2
&nbsp; &nbsp;, StyleColor
&nbsp; &nbsp;, Size_Cd
&nbsp; &nbsp;, Size_Desc, WH, OH_QTY, PK_QTY, WIP_QTY
from @Table1
cross apply
(
&nbsp; &nbsp; values
&nbsp; &nbsp; &nbsp; &nbsp; ('OH_WH', OH_WH),
&nbsp; &nbsp; &nbsp; &nbsp; ('PK_WH', PK_WH),
&nbsp; &nbsp; &nbsp; &nbsp; ('WIP_WH', WIP_WH)
) c(col, WH)
where isnull(WH, '') &lt;&gt; ''
order by Style1
&nbsp; &nbsp;, Style2
&nbsp; &nbsp;, StyleColor
&nbsp; &nbsp;, Size_Cd
&nbsp; &nbsp;, Size_Desc, col;
</code></pre> You see results, the second Grid, I succeeded to get the "WH" into only 1 column. but my problem is the Quantities. on the 2nd row of the first Grid, OH is WH 35 and PK is WH 35, WIP is WH 06. the results should be on second grid: 10 &nbsp;3 &nbsp; &nbsp;0 &nbsp;and another line &nbsp;0 &nbsp; 0 &nbsp;5 <a href="https://filedb.experts-exchange.com/incoming/2020/05_w21/1459426/forEE2.jpg" rel="ugc"> <img src="https://filedb.experts-exchange.com/incoming/2020/05_w21/1459426/forEE2.jpg" class="fr-fic fr-dib"></a> 
</div>

forEE2.jpg

<div>
Your Cross Apply does not consider if your warehouses are equal or different.
</div>

<div>
Your second grid obviously doesn't show what you want for style 319205, since you would like it to shoe 2 lines as 35 10 3 0 and 06 0 0 5. Can we also assume that results for style were also wrong and should have shown 2 rows of 35 2 0 0 and 06 0 0 1? Can we also assume if there is WH of 06 but no 35 (or vice versa), we only need to &nbsp;show the one row? If there were a third WH (e.g. 06 35 20 &nbsp;5 8 &nbsp;10), we would need to show three rows - 06 5 0 0, 35 0 8 0, and 20 0 0 10?
</div>

<div>
<div class="content wysiwyg-content">
Hello EE, I need help to transform an existing table results that I did, to change a bit the way results are, in another format. 
 
See picture below for the columns. 
 
You see around the middle, I have 3 columns &nbsp;OH_WH, PK_WH, WIP_WH. 
then after I have the QTY for each of them OH..PK..WIP. 
 
I would need that, instead having 3 columns for OH_WH, PK_WH, WIP_WH., to only have 1 called : &quot;WH&quot; and only keep the 3 QTY columns. 
 
so that results of WH would be vertically instead of horizontally. 
in other words, if a row was: &nbsp; 06, 35, 35 &nbsp;and QTY were &nbsp;1, 39, 36 
 
the results would be : 
 
06 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp;0 
35 &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; 39 &nbsp; 36 
 
do you follow what I mean ? 
 
 
<a href="https://filedb.experts-exchange.com/incoming/2020/05_w21/1459309/forEE.jpg" target="_blank" title="forEE.jpg (30 KB)"><img alt="forEE.jpg" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2020/05_w21/800_1459309/forEE.jpg" /></a>
</div>
</div>

forEE.jpg

Hello EE, I need help to transform an existing table results that I did, to change a bit the way results are, in another format.

See picture below for the columns.

You see around the middle, I have 3 columns  OH_WH, PK_WH, WIP_WH.
then after I have the QTY for each of them OH..PK..WIP.

I would need that, instead having 3 columns for OH_WH, PK_WH, WIP_WH., to only have 1 called : "WH" and only keep the 3 QTY columns.

so that results of WH would be vertically instead of horizontally.
in other words, if a row was:   06, 35, 35  and QTY were  1, 39, 36

the results would be :

06      1     0      0
35      0     39   36

do you follow what I mean ?




Help with SQL Query

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.