How to make multiple row in one column with Case Statement?-CodePudding

I need to join multiple row into one column. I have a table like this.

PRD_ID PRD_CONTAINER CONTAINER_LVL QTY
---------- --------------------- -------------
   122   BIG_BOX         4         
   122   MED_BOX         3          6
   122   SML_BOX         2           
   122   NO_BOX          1          50

All the qty value should be under one column starting with level 4 to 1. If the there is no value for Qty then it should be marked as 0/. The result I want is like this

PRD_ID   QTY
---------- -------
  122    0/6/0/50

I tried this

 SELECT PRD_ID, 
 (CASE WHEN CONTAINER_LVL = 4 AND QTY > 0 THEN dbo.FBE_GetThousandSeparator(QTY,0) ELSE '0/' END  
 CASE WHEN CONTAINER_LVL = 3 AND QTY > 0 THEN dbo.FBE_GetThousandSeparator(QTY,0) ELSE '0/' END  
 CASE WHEN CONTAINER_LVL = 2 AND QTY > 0 THEN dbo.FBE_GetThousandSeparator(QTY,0) ELSE '0/' END   
 CASE WHEN CONTAINER_LVL = 1 AND QTY > 0 THEN dbo.FBE_GetThousandSeparator(QTY,0) ELSE '' END) AS 
 QTY
 FROM #TMP2
 GROUP BY PRD_ID,
 (CASE WHEN CONTAINER_LVL = 4 AND QTY > 0 THEN dbo.FBE_GetThousandSeparator(QTY,0) ELSE '0/' END  
 CASE WHEN CONTAINER_LVL = 3 AND QTY > 0 THEN dbo.FBE_GetThousandSeparator(QTY,0) ELSE '0/' END  
 CASE WHEN CONTAINER_LVL = 2 AND QTY > 0 THEN dbo.FBE_GetThousandSeparator(QTY,0) ELSE '0/' END   
 CASE WHEN CONTAINER_LVL = 1 AND QTY > 0 THEN dbo.FBE_GetThousandSeparator(QTY,0) ELSE '' END)

My result becomes like below.

PRD_ID   QTY
---------- -------
122    0/0/0/
122    0/0/0/50
122    0/60/

CodePudding user response：

Maybe using PIVOT can solve your problem, try this;

Select PROD_ID, [1] AS NO, [2] AS SML, [3] AS MED, [4] AS BIG FROM #TMP2 PIVOT (SUM(QTY) FOR CONTAINER_LVL IN ([1],[2],[3],[4]) ) P

CodePudding user response：

Per @Larnu's comment, you can use string aggregation. Also, if you're just checking for nulls (rather than concerned with negatives) you don't need case statements.

select PRD_ID,
string_agg(
    convert(nvarchar(32), coalesce(QTY,0))
    , '/'
) within group (order by CONTAINER_LVL desc) as QTY
from #TMP
group by PRD_ID

In the above we :

use coalesce to replace any null values with 0s
use convert to change from integers to strings
use string_agg to join all strings together, with / as a delimiter.
use within group (order by CONTAINER_LVL desc) to determine the order the fields are returned in (i.e. the QTY for CONTAINER_LVL=4 first, then 3, etc)

If that's not an option (i.e. as string_agg was only introduced in SQL 2017):

select PRD_ID,
convert(nvarchar(32), coalesce(max(case when CONTAINER_LVL = 4 then QTY end),0))
  N'/'  
convert(nvarchar(32), coalesce(max(case when CONTAINER_LVL = 3 then QTY end),0))
  N'/'  
convert(nvarchar(32), coalesce(max(case when CONTAINER_LVL = 2 then QTY end),0))
  N'/'  
convert(nvarchar(32), coalesce(max(case when CONTAINER_LVL = 1 then QTY end),0))
from #TMP
group by PRD_ID

In the above we:

use the CASE statements to get the QTY value for the given CONTAINER_LVL value for the given position; for all rows that don't have that CONTAINER_LVL we'd get NULL here. We also get NULL if the QTY value for that CONTAINER_LVL is NULL.
wrap that in MAX so that the multiple row's values (including NULLS) within the group are "squashed"/aggregated down to a single value; in this case the max. If all rows had NULLs, that would be NULL, if we had one numeric value and the rest null (as generally expected) we'd get that numeric value; if we had multiple numeric values (i.e. as we had 2 rows with identical PRD_ID and CONTAINER_LVL values) we'd get the one with the highest QTY value... though I'm assuming this last scenario isn't a valid one, and that you have a unique contraint to protect against it. Note: you could also use SUM here if you wish; then if you have multiple rows with the same PRD_ID and CONTAINER_LVL values their QTYs will be combined for the value at that position.
we wrap that in a coalesce statement, such that if the value was NULL we replace that with a 0.
we wrap that in a convert statement so we can perfom string concatenation on the value (i.e. '0' '/' is valid... 0 '/' is not).
we then use 'N'/' between each of these to concatenate the 4 values as expected.

SQL Fiddle Demo