Solved

SQL concat query help

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Trigger selecting another database 4 41
MS SQL page split per second is high 19 106
Addition to SQL for dynamic fields 6 48
date diff with Fiscal Calendar 4 57
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

821 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