SQL Query help - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query help

Author

Topic

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-07-29 : 10:59:36

using SQL 2000
here is the DDL and some sample data
Design is insipired by a legacy system, I have no control over changing it

Can this be done without using a SQL UDF (in a single select statement)?

USE tempdb
Go

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Colors]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Colors]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Colors]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Colors](
[TABLEID] [int] IDENTITY(1,1) NOT NULL,
[Color1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Color1Pct] [int] NULL,
[Color2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Color2Pct] [int] NULL,
[Color3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Color3Pct] [int] NULL,
CONSTRAINT [PK_Colors] PRIMARY KEY CLUSTERED
(
[TABLEID] ASC
)
)
END
GO
SET IDENTITY_INSERT [dbo].[Colors] ON
INSERT [dbo].[Colors] ([TABLEID], [Color1], [Color1Pct], [Color2], [Color2Pct], [Color3], [Color3Pct]) VALUES (1,'GREEN', 100, NULL, NULL, NULL, NULL)
INSERT [dbo].[Colors] ([TABLEID], [Color1], [Color1Pct], [Color2], [Color2Pct], [Color3], [Color3Pct]) VALUES (2,'CYAN', 50, 'Magenta',40,'Yellow', 40)
INSERT [dbo].[Colors] ([TABLEID], [Color1], [Color1Pct], [Color2], [Color2Pct], [Color3], [Color3Pct]) VALUES (3, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Colors] ([TABLEID], [Color1], [Color1Pct], [Color2], [Color2Pct], [Color3], [Color3Pct]) VALUES (4, 'CYAN', 100, NULL, NULL, 'Yellow', 100)
SET IDENTITY_INSERT [dbo].[Colors] OFF

SELECT * FROM Colors

Need to Concatenate Color1, Color1Pct, Color2, Color2Pct, Color3 and Color3Pct Columns

a) When you have percentage of any color as 100 use that percentage as an empty string
i.e. ignore that percentage

b) When you have percentage of any color less than 100 than it nooed be
color + _ColorPercentage%
in the sample data output for secord row would be like
CYAN_50%+Magenta_40%+Yellow_40%

c) if you have more than one color we need + sign in between them
in the sample data fourth row CYAN+Yellow

d) if all the columns has null value then just return an empty string
(chances of that happening will be 1 out of 100)

Output Should be

TABLEID ColorName
1 GREEN
2 CYAN_50%+Magenta_40%+Yellow_40%
3
4 CYAN+Yellow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-29 : 11:07:25

It would be much easier to write this out in a function, thought not a SQL Server UDF -- it should be a function at your presentation layer (reporting tool, web application, etc). A requirement of doing it in in-line SQL will be a huge mess of CASE expressions that will be hard to read, write and maintain.

If you do feel the need to write it in T-SQL, then I would recommend doing it as a UDF. Why wouldn't you use a UDF?

- Jeff
http://weblogs.sqlteam.com/JeffS

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-29 : 11:12:31

I think since you have the table de-normalized it isn't that hard:


select tableid
       ,colorname = color1 + isNull('_' + convert(varchar,nullif(color1Pct,100)) + '%', '') 
              + isNull('+' + Color2 + isNull('_' + convert(varchar,nullif(color2Pct,100)) + '%', ''),'')
              + isNull('+' + Color3 + isNull('_' + convert(varchar,nullif(color3Pct,100)) + '%', ''),'')
from   Colors

output:
tableid     colorname
----------- --------------------------------
1           GREEN
2           CYAN_50%+Magenta_40%+Yellow_40%
3           NULL
4           CYAN+Yellow

Be One with the Optimizer
TG

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-07-29 : 11:13:10

Jeff, this is a SQL 2000 database of a third party system. We can query it and db schema will not change but I thought I should not be creating SQL objects in that database.

If you think UDF is the way to go. Thats' fine. here is my version but I am missing something

ALTER FUNCTION GetColorID
(
@Co1 varchar(100)
,@Co1Percent int
,@Co2 varchar(100)
,@Co2Percent int
,@Co3 varchar(100)
,@Co3Percent int
)
RETURNS varchar(200)
AS
BEGIN

DECLARE @ColorName varchar(200)
DECLARE @Color1Pct varchar(10), @Color2Pct varchar(10), @Color3Pct varchar(10)

IF (@CO1 = '') AND (@CO2 = '') AND (@CO3='')
BEGIN
SET @ColorName = ''
Return @ColorName
END

IF (@Co1Percent = 100)
BEGIN
SET @Color1Pct = ''
END
ELSE
BEGIN
SET @Color1Pct = '_' + CAST(@Co1Percent As varchar(10)) + '%'
END

IF (@Co2Percent = 100)
BEGIN
SET @Color2Pct = ''
END
ELSE
BEGIN
SET @Color2Pct = '_' + CONVERT(varchar(10),@Co2Percent) + '%'
END

IF (@Co3Percent = 100)
BEGIN
SET @Color3Pct = ''
END
ELSE
BEGIN
SET @Color3Pct = '_' + CONVERT(varchar(10),@Co3Percent) + '%'
END

SET @ColorName = @CO1 + @Color1Pct + @Co2 + @Color2Pct + @Co3 + @Color3Pct

RETURN @ColorName

END

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-07-29 : 11:25:14

TG, Thank you.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-29 : 11:28:53

Well done, TG.

- Jeff
http://weblogs.sqlteam.com/JeffS

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-29 : 11:31:59

You're welcome.
But of course the problem with a de-normalized table is that if you want to add a fourth (or more) colors you need to keep changing the table structure and the code. You may want to consider a normalized model and some sort of function as Jeff suggested.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources