Solved

SQL concat query help

Posted on 2012-03-26
3
204 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

728 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