May 16, 2009 at 3:59 am
this is my initial data
Declare @tblMessage Table(Id Int, Description Varchar(50), Message Varchar(256),colName varchar(20))
Declare @tblError Table (ErrorNumber Int, ABC varchar(20),ABC1 varchar(20),ABC2 varchar(20),ABC3 varchar(20))
Insert into @tblMessage(Id,Description,Message, ColName)
Select 1, 'Animal', 'Zebra is @var@ Animal','ABC'
union Select 2, 'Animal', 'Zebra is @var@ Animal','ABC1'
union Select 3, 'Animal', 'Zebra is @var@ Animal','ABC2'
union Select 4, 'Animal', 'Zebra is @var@ Animal','ABC3'
Insert into @tblError(ErrorNumber, ABC, ABC1, ABC2, ABC3)
Select 1,'Good','Best','Bad','Worst'
union Select 2,'Good1','Best1','Bad1','Worst1'
union Select 3,'Good2','Best2','Bad2','Worst2'
union Select 4,'Good3','Best3','Bad3','Worst3'
Now i want Data like this
ErrorNumber | ErrorMessage
1 | Zebra is Good Animal
2 | Zebra is Best1 Animal
3 | Zebra is Bad2 Animal
4 | Zebra is Worst3 Animal
in first table there is a message column which contains '@var@'
i want to replace it with the value which is there in column of particular row whose name is saved in First table's ColName
where @tblError.ErrorNumber = @tblMessage.ID
please reply if some one can help
May 16, 2009 at 5:27 am
Hi
As first, thanks for the very good sample data and required result description!
You can use a CASE block to get the specified column value:
SELECT
m.*,
REPLACE(
m.Message,
'@var@',
CASE m.colName
WHEN 'ABC' THEN e.ABC
WHEN 'ABC1' THEN e.ABC1
WHEN 'ABC2' THEN e.ABC2
WHEN 'ABC3' THEN e.ABC3
END
)
FROM @tblMessage m
JOIN @tblError e ON m.Id = e.ErrorNumber
Greets
Flo
May 16, 2009 at 8:38 am
Thanks Flo for the prompt response
but its not what i actually want
you have restricted the Columns in Case Statement, i want it to be a dynamic one
like if in future i want to show different value in my message variable through different column then what i'll do is just an Update in @tblMessage with the desired column name and here it goes... no change should be required in Query
I hope i have clarified my question
I am also searching for it, but not getting what i want
Cheers
Anila
May 16, 2009 at 10:20 am
Heard about UNPIVOT clause in SQL 2005? This is where it can be used.
DECLARE @tblMessage TABLE
(
Id Int,
Description Varchar(50),
Message Varchar(256),
colName varchar(20)
)
DECLARE @tblError TABLE
(
ErrorNumber Int,
Variable varchar(20),
ABC varchar(20),
ABC1 varchar(20),
ABC2 varchar(20),
ABC3 varchar(20)
)
INSERT@tblMessage( Id, Description, Message, ColName )
SELECT1, 'Animal', 'Zebra is @var@ Animal', 'ABC'
UNION ALL
SELECT2, 'Animal', 'Zebra is @var@ Animal', 'ABC1'
UNION ALL
SELECT3, 'Animal', 'Zebra is @var@ Animal', 'ABC2'
UNION ALL
SELECT4, 'Animal', 'Zebra is @var@ Animal', 'ABC3'
INSERT@tblError( ErrorNumber, Variable, ABC, ABC1, ABC2, ABC3 )
SELECT1, 'ABC', 'Good', 'Best', 'Bad', 'Worst'
UNION ALL
SELECT2, 'ABC1', 'Good1', 'Best1', 'Bad1', 'Worst1'
UNION ALL
SELECT3, 'ABC2', 'Good2', 'Best2', 'Bad2', 'Worst2'
UNION ALL
SELECT4, 'ABC3', 'Good3', 'Best3', 'Bad3', 'Worst3'
SELECTE.ErrorNumber, REPLACE( M.Message, '@var@', E.VariableValue ) AS ErrorMessage
FROM@tblMessage M
INNER JOIN
(
SELECT*
FROM@tblError
UNPIVOT
(
VariableValue FOR VariableName IN( [ABC], [ABC1], [ABC2], [ABC3] )
) U
) E ON M.ColName = E.Variable AND M.ColName = E.VariableName
--Ramesh
May 16, 2009 at 3:57 pm
Ramesh (5/16/2009)
Heard about UNPIVOT clause in SQL 2005? This is where it can be used....
Although this example is also not dynamic.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 16, 2009 at 4:11 pm
anila_jkhwaja (5/16/2009)
Thanks Flo for the prompt responsebut its not what i actually want
you have restricted the Columns in Case Statement, i want it to be a dynamic one
like if in future i want to show different value in my message variable through different column then what i'll do is just an Update in @tblMessage with the desired column name and here it goes... no change should be required in Query
I hope i have clarified my question
I am also searching for it, but not getting what i want
Although it is possible to convert both Florian's and Ramesh's examples to be dynamic, that's not the real problem here.
The real problem is that your design is severely non-relational and is causing these problems. In particular, you design of the @tblError table:
Declare @tblError Table (
ErrorNumber Int,
ABC varchar(20),
ABC1 varchar(20),
ABC2 varchar(20),
ABC3 varchar(20)
)
Insert into @tblError(ErrorNumber, ABC, ABC1, ABC2, ABC3)
Select 1,'Good','Best','Bad','Worst'
union Select 2,'Good1','Best1','Bad1','Worst1'
union Select 3,'Good2','Best2','Bad2','Worst2'
union Select 4,'Good3','Best3','Bad3','Worst3'
When you start talking about addressing columns by name and about adding more columns "as needed" in the future, you are indicating that you are trying to treat these as an array, list or a repeating field and that's a very bad thing in relational databases.
This is closer to how this table should look:
Declare @tblError Table (
ErrorNumber Int,
ColName,
ErrorMsg varchar(20),
Primary Key (ErrorNumber, ColName)
)
Insert into @tblError(ErrorNumber, ColName, ErrorMsg)
Select 1,'ABC', 'Good'
union all Select 1,'ABC1','Best'
union all Select 1,'ABC2','Bad'
union all Select 1,'ABC3','Worst'
union all Select 2,'ABC','Good1'
union all Select 2,'ABC1','Best1'
union all Select 2,'ABC2','Bad1'
union all Select 2,'ABC3','Worst1'
union all Select 3,'ABC','Good2'
union all Select 3,'ABC1','Best2'
union all Select 3,'ABC2','Bad2'
union all Select 3,'ABC3','Worst2'
etc...
Now, its easy to implement your request.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 16, 2009 at 4:14 pm
Here's how Florian's answer would be implemented with this more relational table design:
SELECT
m.*,
REPLACE(m.Message, '@var@', e.ErrorMsg)
FROM @tblMessage m
JOIN @tblError e ON m.Id = e.ErrorNumber
And m.colName = e.ColName
Some what easier, yes? And now it's "dynamic" too!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply