SQL concat query help

Hi Experts

I have 5 columns all contain numbers which I need to concatanate in a dataset to look like this with pipe symbols inbetween each number.    e.g.


It will not always be the case that all 5 columns will contain data and in which case I only need to see the numbers without the remaining pipe symbols e.g.


The bold part of the query  code contains the field names for the columns I need to concatanate in this way.  

The purpose is that we will use the the dataset to import products from our back office system into our website - the numbers will map to the product categories on our website.

SELECT T0.[U_Garment_Title]'Name', T0.[U_GarmentDesc]'Description', T1.[Name]'Colour', T2.[Name]'Size', T0.[U_GarmentSKU]'SKU', T0.[ItemCode]'SubSKU', T0.[U_Active]'Active', T0.[OnHand]'Stock', T0.[U_Threshold],

T3.[U_Website_ID], T4.[U_Website_ID], T5.[U_Website_ID], T6.[U_Website_ID], T7.[U_Website_ID]

FROM OITM T0  INNER JOIN [dbo].[@GARMENT_COLOUR]  T1 ON T0.U_GarmentColour = T1.Code LEFT JOIN [dbo].[@GARMENT_SIZE]  T2 ON T0.U_GarmentSize = T2.Code LEFT JOIN [dbo].[@GARMENT_TID]  T3 ON T0.U_GarmentTID1 = T3.Code LEFT JOIN [dbo].[@GARMENT_TID2]  T4 ON T0.U_GarmentTID2 = T4.Code LEFT JOIN [dbo].[@GARMENT_TID3]  T5 ON T0.U_GarmentTID3 = T5.Code LEFT JOIN [dbo].[@GARMENT_TID4]  T6 ON T0.U_GarmentTID4 = T6.Code LEFT JOIN [dbo].[@GARMENT_TID5]  T7 ON T0.U_GarmentTID5 = T7.Code LEFT JOIN [dbo].[@TERM_ID]  T8 ON T3.U_Website_ID = T8.Code

WHERE T0.[U_Active] ='Y'

order by T0.[U_GarmentTID1], T0.[U_GarmentTID2], T0.[U_GarmentTID3], T0.[U_GarmentTID4], T0.[U_GarmentTID5]
Who is Participating?
Brendt HessConnect With a Mentor Senior DBACommented:
Both of the above are almost complete.  To handle the case where T7.U_Website_ID is null, you can use this version:

            WHEN RIGHT(PDL, 2) = '||'
                  THEN LEFT(pdl, LEN(pdl)-2)
            ELSE pdl
      END AS pdl
            T0.[U_Garment_Title] 'Name',
            ISNULL(CAST(T3.[U_Website_ID] AS varchar(20)) + '||', '') +
            ISNULL(CAST(T4.[U_Website_ID] AS varchar(20)) + '||', '') +
            ISNULL(CAST(T5.[U_Website_ID] AS varchar(20)) + '||', '') +
            ISNULL(CAST(T6.[U_Website_ID] AS varchar(20)) + '||', '') +
            ISNULL(CAST(T7.[U_Website_ID] AS varchar(20)), '') pdl,
      FROM OITM T0  
            ON T0.U_GarmentColour = T1.Code
      LEFT JOIN [dbo].[@GARMENT_SIZE]  T2
            ON T0.U_GarmentSize = T2.Code
      LEFT JOIN [dbo].[@GARMENT_TID]  T3
            ON T0.U_GarmentTID1 = T3.Code
      LEFT JOIN [dbo].[@GARMENT_TID2]  T4
            ON T0.U_GarmentTID2 = T4.Code
      LEFT JOIN [dbo].[@GARMENT_TID3]  T5
            ON T0.U_GarmentTID3 = T5.Code
      LEFT JOIN [dbo].[@GARMENT_TID4]  T6
            ON T0.U_GarmentTID4 = T6.Code
      LEFT JOIN [dbo].[@GARMENT_TID5]  T7
            ON T0.U_GarmentTID5 = T7.Code
      LEFT JOIN [dbo].[@TERM_ID]  T8
            ON T3.U_Website_ID = T8.Code
      WHERE T0.[U_Active] ='Y'
      ) Src
order by
Christopher GordonConnect With a Mentor Senior Developer AnalystCommented:
Something like this using isnull and a CAST function?

declare @myTable table (id int identity(1,1),
      myCode1 int
,      myCode2 int
,      myCode3 int
,      myCode4 int
,      myCode5 int)

insert into @myTable values (null, null, 4, 5, 3)
insert into @myTable values (300, 12, 4, 5, 3)


      isnull(cast(myCode1 as nvarchar(20)) + ' || ','')  +
      isnull(cast(myCode2 as nvarchar(20)) + ' || ','') +
      isnull(cast(myCode3 as nvarchar(20)) + ' || ','') +
      isnull(cast(myCode4 as nvarchar(20)) + ' || ','')  +
      isnull(cast(myCode5 as nvarchar(20)) ,'') as myCodes,


from      @myTable
TempDBAConnect With a Mentor Commented:
You just need to concatenate those field using "+" operator. Since you wanted to concatenate the numeric field with character, you need to cast the numeric data into character and check for null values if any. This is because NULL + something = NULL which you would definitely want to ignore...

SELECT T0.[U_Garment_Title] AS Name,
       T0.[U_GarmentDesc] AS Description,
       T1.[Name]AS Colour,
       T2.[Name] AS Size,
       T0.[U_GarmentSKU] AS SKU,
       T0.[ItemCode] AS SubSKU,
       T0.[U_Active] AS Active,
       T0.[OnHand] AS Stock,
       U_Website_ID = CAST(ISNULL(T3.[U_Website_ID],'') AS VARCHAR) + '|' + CAST(ISNULL(T4.[U_Website_ID],'') AS VARCHAR) + '|' + CAST(ISNULL(T5.[U_Website_ID],'') AS VARCHAR) + '|'  + CAST(ISNULL(T6.[U_Website_ID],'') AS VARCHAR) + '|'  + CAST(ISNULL(T7.[U_Website_ID],'') AS VARCHAR)
            ON  T0.U_GarmentColour = T1.Code
       LEFT JOIN [dbo].[@GARMENT_SIZE] T2
            ON  T0.U_GarmentSize = T2.Code
       LEFT JOIN [dbo].[@GARMENT_TID] T3
            ON  T0.U_GarmentTID1 = T3.Code
       LEFT JOIN [dbo].[@GARMENT_TID2] T4
            ON  T0.U_GarmentTID2 = T4.Code
       LEFT JOIN [dbo].[@GARMENT_TID3] T5
            ON  T0.U_GarmentTID3 = T5.Code
       LEFT JOIN [dbo].[@GARMENT_TID4] T6
            ON  T0.U_GarmentTID4 = T6.Code
       LEFT JOIN [dbo].[@GARMENT_TID5] T7
            ON  T0.U_GarmentTID5 = T7.Code
       LEFT JOIN [dbo].[@TERM_ID] T8
            ON  T3.U_Website_ID = T8.Code
WHERE  T0.[U_Active] = 'Y'
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.