Variable colums in stacked table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Variable colums in stacked table

Author

Topic

mrgr8avill
Starting Member

16 Posts

Posted - 2007-12-14 : 19:38:05

Hello, and thanks for taking the time to read this.

NOOB question:

In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:

CREATE TABLE [dbo].[T_Catalog](
[StlyeID] [int]
) ON [PRIMARY]
with data:
1
2

the SIZES table (filled in by the customer with all the size ranges they carry) is:

CREATE TABLE [dbo].[T_Sizes](
[SizeID] [int],
[SizeName] [nchar](10)
) ON [PRIMARY]
with data:

1,Small
2,Medium
3,Large
4,Xtra-Large

and the AVAILSIZES table would be:

CREATE TABLE [dbo].[T_AvailSizes](
[StyleID] [int],
[SizeID] [int]
) ON [PRIMARY]

1,1
1,2
1,3
2,1
2,3
2,4

Basically, then, we know that:

style 1 comes in Small, Medium, and Large
style 2 xomes in Small, Large, and Xtra large

WE know that, but getting SQL to tell us that is a major PIA!!

Now,

SELECT
t_Catalog.StyleId, t_AvailSizes.SizeID
FROM
t_Catalog
INNER JOIN t_AvailSizes ON
t_Catalog.StyleId = t_AvailSizes.StyleId

will give me a nice list of each item number with a separate row for each size number.

My questions are:

How do I get the size NAMES?

How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?

Is this the right way of doing this?

In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.

Thanks for any help and information you can provide.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-17 : 11:58:04

Does this help?

-- Setup Temp tables and data
DECLARE @T_Catalog TABLE ([StyleID] [int]) 
INSERT @T_Catalog 
SELECT 1
UNION ALL SELECT 2

DECLARE @T_Sizes TABLE(
[SizeID] [int],
[SizeName] [nchar](10)
) 

INSERT @T_Sizes
SELECT 1, 'Small'
UNION ALL SELECT 2, 'Medium'
UNION ALL SELECT 3, 'Large'
UNION ALL SELECT 4, 'Xtra-Large'


DECLARE @T_AvailSizes TABLE (
[StyleID] [int],
[SizeID] [int]
) 

INSERT @T_AvailSizes 
SELECT 1,1
UNION ALL SELECT 1,2
UNION ALL SELECT 1,3
UNION ALL SELECT 2,1
UNION ALL SELECT 2,3
UNION ALL SELECT 2,4

-- Select data we want
SELECT 
	t_Catalog.StyleId, 
	t_AvailSizes.SizeID,
	t_sizes.SizeName
FROM 
	@t_Catalog  AS t_Catalog
INNER JOIN 
	@t_AvailSizes AS t_AvailSizes 
	ON t_Catalog.StyleId = t_AvailSizes.StyleId
INNER JOIN
	@T_Sizes AS t_Sizes
	ON t_Sizes.SizeID = T_AvailSizes.SizeID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 12:58:56

If you're using SQL 2005,u can get comma seperated sizenames foir styleid as follows:-

SELECT
t_Catalog.StyleId,
LEFT(NameList.l,LEN(NameList.l)-1) as 'sizenames'
FROM
@t_Catalog AS t_Catalog
CROSS APPLY (SELECT SizeName + ',' AS text()
FROM @T_Sizes AS t_Sizes
INNER JOIN @t_AvailSizes AS t_AvailSizes
ON t_AvailSizes.SizeId = t_Sizes.SizeId
WHERE t_AvailSizes.StyleID= t_Catalog.StyleID
FOR XML PATH(''))NameList(l)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources