Solved

SQL concat query help

Posted on 2012-03-26
3
198 Views
Last Modified: 2013-02-20
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.

13||267||228||324||390

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.

13||267||228

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]
0
Comment
Question by:JonYen
3 Comments
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 167 total points
ID: 37765533
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)

select      

      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
0
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 166 total points
ID: 37766314
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,
       T0.[U_Threshold],
       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)
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]
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 167 total points
ID: 37769282
Both of the above are almost complete.  To handle the case where T7.U_Website_ID is null, you can use this version:


SELECT
      [Name],
      [Description],
      [Colour],
      [Size],
      [SKU],
      [SubSku],
      [Active],
      [Stock],
      [U_Threshold],
      CASE
            WHEN RIGHT(PDL, 2) = '||'
                  THEN LEFT(pdl, LEN(pdl)-2)
            ELSE pdl
      END AS pdl
FROM (
      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],
            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,
            T0.[U_GarmentTID1],
            T0.[U_GarmentTID2],
            T0.[U_GarmentTID3],
            T0.[U_GarmentTID4],
            T0.[U_GarmentTID5]
      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'
      ) Src
order by
      Src.[U_GarmentTID1],
      Src.[U_GarmentTID2],
      Src.[U_GarmentTID3],
      Src.[U_GarmentTID4],
      Src.[U_GarmentTID5]
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A short film showing how OnPage and Connectwise integration works.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now