number INT
sub_cat char(16)
main_cat char(16)

1, sandals, shoes
2, flipflops, shoes
3, sneakers, shoes
4, no print, t-shirts
5, with print, t-shirts
6, cartoon, t-shirts

100, shoes
101, sandals
102, flipflops
103, sneakers
200, t-shirts
201, no print
202, with print
203, cartoon

quote:

---

Originally posted by mberggren

Is it possible to do this ? Update the 'number' column nr

---

1, san_product 1, sandals, shoes
2, san_product 2, sandals, shoes
3, san_product 3, sandals, shoes
4, flip_product 1, flipflops, shoes
5, flip_product 2, flipflops, shoes
6, flip_product 3, flipflops, shoes
7, snea_product 1, sneakers, shoes
8, snea_product 2, sneakers, shoes
9, snea_product 2, sneakers, shoes
10, nop_product 1, no print, t-shirts
11, nop_product 2, no print, t-shirts
12, nop_product 3, no print, t-shirts
13, wit_product 1, with print, t-shirts
14, wit_product 2, with print, t-shirts
15, wit_product 3, with print, t-shirts
16, car_product 1, cartoon, t-shirts
17, car_product 2, cartoon, t-shirts
18, car_product 3, cartoon, t-shirts

100, shoes
110, sandals
111, san_product 1
112, san_product 2
113, san_product 3
120, flipflops
121, flip_product 1
122, flip_product 2
123, flip_product 3
130, sneakers
131, snea_product 1
132, snea_product 2
133, snea_product 3
200, t-shirts
210, no print
211, nop_product 1
212, nop_product 2
213, nop_product 3
220, with print
221, wit_product 1
222, wit_product 2
223, wit_product 3
230, cartoon
231, car_product 1
232, car_product 2
233, car_product 3

SET NOCOUNT ON
GO

CREATE TABLE #MerchandiceType (
	  MerchTypeCd		int
	, MerchType			char(16)
	, MerchTypeGroup	int
	, MerchTypeDesc		varchar(256)
)

CREATE TABLE #Merchandice (
	  Merch				char(16)
	, MerchTypeCd		char(16)
	, MerchGroup		int
	, MerchDesc			varchar(256)
)
GO

INSERT INTO #MerchandiceType (
			  MerchTypeCd
			, MerchType
			, MerchTypeGroup
			, MerchTypeDesc)
     SELECT 1, 'Shoes', 10, 'We Sell Shoes' UNION ALL
     SELECT 2, 'T-Shirst', 20, 'We Sell Tee''s that Don''t Shrink'
 GO
INSERT INTO #Merchandice (
			  Merch
			, MerchTypeCd
			, MerchGroup
			, MerchDesc)
     SELECT 'Sandals', 1,     1, 'This is not the resort' UNION ALL
     SELECT 'FlipFlops', 1,   2, 'This is Georg Bush Sr.' UNION ALL
     SELECT 'Sneakers', 1,    3, 'This is Slick Willy'    UNION ALL
     SELECT 'No Print', 2,    1, 'This is more a different type than a thing'      UNION ALL
     SELECT 'With Print', 2,  2, 'This is more a different type than a thing 2'    UNION ALL
     SELECT 'Cartoon', 2,     3, 'GW Bush'
GO


SELECT LEFT(CONVERT(varchar(3),MerchTypeGroup) + '0',3) AS Category
		, MerchType AS Description
  FROM #MerchandiceType
UNION 
SELECT LEFT(CONVERT(varchar(3),MerchTypeGroup)
	 +      CONVERT(varchar(1),MerchGroup),3) AS Category
		, '   ' + Merch AS Description
  FROM #Merchandice m
  JOIN #MerchandiceType t
    ON m.MerchTypeCd = t.MerchTypeCd
ORDER BY 1,2


DROP TABLE #MerchandiceType, #Merchandice
GO

SET NOCOUNT OFF
GO

CREATE TABLE custom.main (
	Seq int identity
	main CHAR (50)
    )

CREATE TABLE custom.sub (
	Seq int identity
	sub CHAR (50)
    )

INSERT INTO custom.main (main)
SELECT DISTINCT table1.main
FROM custom.table1
WHERE table1.main IS NOT NULL

INSERT INTO custom.sub (sub)
SELECT DISTINCT table1.sub
FROM custom.table1
WHERE table1.sub IS NOT NULL