rgb192
asked on
join text to int and separate by commas
CREATE TABLE [dbo].[sitedata](
[productid] [int] NULL,
[pathid] [int] NULL,
[accessoryid] [int] NULL,
[alternatecolors] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[specifications] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[features] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[itemincludes] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[accessories] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[infoandguides] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[rebateinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[sunshineactive] [int] NULL,
[sunshineprice] [money] NULL,
[sunshinefeed] [int] NULL,
[pyxisactive] [int] NULL,
[pyxisprice] [money] NULL,
[pyxisfeed] [int] NULL,
[hdewactive] [int] NULL,
[hdewprice] [money] NULL,
[hdewfeed] [int] NULL,
[shippingtype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[shippingpriceground] [money] NULL,
[shippingprice3day] [money] NULL,
[shippingprice2day] [money] NULL,
[shippingprice1day] [money] NULL,
[shippingpricetruck] [money] NULL,
[shippingpricetruckexpress ] [money] NULL,
[shippingpricetruckwhite] [money] NULL,
[pyxispackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[sunpackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[ebaydescription] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[pdescription] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[xsell] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[ebaytitles](
[titleid] [int] IDENTITY(1,1) NOT NULL,
[title] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
[type] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
[seller] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
[packageid] [int] NULL,
[gallery] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[price] [money] NOT NULL,
[webprice] [money] NOT NULL,
[buyitnowprice] [decimal](7, 2) NULL,
[startprice] [decimal](7, 2) NULL,
[oldstartprice] [decimal](7, 2) NULL,
[itemid] [bigint] NULL,
[date] [datetime] NULL,
[oldbuyitnowprice] [decimal](7, 2) NULL,
[s1] [smallint] NULL,
[b1] [smallint] NULL,
[site] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[site2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[oldstartprice2] [decimal](7, 2) NULL,
[oldbuyitnowprice2] [decimal](7, 2) NULL,
[u1] [decimal](7, 2) NULL,
[ea] [smallint] NULL
) ON [PRIMARY]
want to join sitedata.xsell [text] on ebaytitles.packageid [int]
but sitedata.xsell is numbers separated by commas
so if there are 2 numbers, I want them to be joined 2 times
so if there are 3 numbers, I want them to be joined 3 times
so if there are 4 numbers, I want them to be joined 4 times
only want to do this where sitedata.xsell has values
[productid] [int] NULL,
[pathid] [int] NULL,
[accessoryid] [int] NULL,
[alternatecolors] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_
[specifications] [text] COLLATE SQL_Latin1_General_CP1_CI_
[features] [text] COLLATE SQL_Latin1_General_CP1_CI_
[itemincludes] [text] COLLATE SQL_Latin1_General_CP1_CI_
[accessories] [text] COLLATE SQL_Latin1_General_CP1_CI_
[infoandguides] [text] COLLATE SQL_Latin1_General_CP1_CI_
[rebateinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_
[sunshineactive] [int] NULL,
[sunshineprice] [money] NULL,
[sunshinefeed] [int] NULL,
[pyxisactive] [int] NULL,
[pyxisprice] [money] NULL,
[pyxisfeed] [int] NULL,
[hdewactive] [int] NULL,
[hdewprice] [money] NULL,
[hdewfeed] [int] NULL,
[shippingtype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[shippingpriceground] [money] NULL,
[shippingprice3day] [money] NULL,
[shippingprice2day] [money] NULL,
[shippingprice1day] [money] NULL,
[shippingpricetruck] [money] NULL,
[shippingpricetruckexpress
[shippingpricetruckwhite] [money] NULL,
[pyxispackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_
[sunpackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_
[ebaydescription] [text] COLLATE SQL_Latin1_General_CP1_CI_
[pdescription] [text] COLLATE SQL_Latin1_General_CP1_CI_
[xsell] [text] COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[ebaytitles](
[titleid] [int] IDENTITY(1,1) NOT NULL,
[title] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[type] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[seller] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[packageid] [int] NULL,
[gallery] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_
[price] [money] NOT NULL,
[webprice] [money] NOT NULL,
[buyitnowprice] [decimal](7, 2) NULL,
[startprice] [decimal](7, 2) NULL,
[oldstartprice] [decimal](7, 2) NULL,
[itemid] [bigint] NULL,
[date] [datetime] NULL,
[oldbuyitnowprice] [decimal](7, 2) NULL,
[s1] [smallint] NULL,
[b1] [smallint] NULL,
[site] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[site2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[oldstartprice2] [decimal](7, 2) NULL,
[oldbuyitnowprice2] [decimal](7, 2) NULL,
[u1] [decimal](7, 2) NULL,
[ea] [smallint] NULL
) ON [PRIMARY]
want to join sitedata.xsell [text] on ebaytitles.packageid [int]
but sitedata.xsell is numbers separated by commas
so if there are 2 numbers, I want them to be joined 2 times
so if there are 3 numbers, I want them to be joined 3 times
so if there are 4 numbers, I want them to be joined 4 times
only want to do this where sitedata.xsell has values
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can recommend to change the table structure to improve the performance. The data in [xsell] column needs to be in a separate table to make the design comply with first form of Normalization. It wil help to add scalability and performance to your database.
ASKER
this is the best solution that works
Open in new window
use this function in your code when joining the tables.Open in new window