Solved

SQL concat query help

Posted on 2012-03-26
3
197 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

747 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

12 Experts available now in Live!

Get 1:1 Help Now