How do I retrieve a data - 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

General SQL Server Forums

New to SQL Server Programming

How do I retrieve a data

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-20 : 02:10:57

Hi,

I have 2 tables as follows:

Table: Details
---------------
DetailID: GUID (PK)
Title: varchar(250)

Table: DetailsFormula
---------------
DetailsFormulaID : GUID (PK)
DetailID : (FK to the Details Table)
Sign : varchar(2) (used to contain one of the symbols + or - or * or / )
Topic : varchar(50)

Now I want to display the following in a row:

Details.Title , Concat of all (Signs + ' ' + Topic)

How do I do that? When I use inner join, the details.Title is repeated for each Sign.

e.g. If I got the following information

Details Table
----------
Title : Calculation of Formula for Pi
DetailsID : 1

DetailsFormula 1st row
--------------
Topic : 22
Sign : +
DetailsID : 1

DetailsFormula 2nd row
--------------
Topic : 7
Sign : /
DetailsID : 1

I want to display the following (columns separated by comma):

Calculation of Formula for Pi , + 22 / 7

in a single row. Note that topic is usually string and not number. i.e. 22 can be written as twenty-two etc. I don't need the calculated values as this is a just for display about the Details and their formula for calculations.

So if I add the following details:

Details Table
----------
Title : Area of Circle Formula
DetailsID : 2

DetailsFormula
-------------
Sign: +
Topic: pi
DetailsID: 2

Sign : *
Topic : radius
DetailsId: 2

Sign: *
Topic : radius
DetailsId : 2

I will have the following lines (including the first line)

1. Calculation of Formula for Pi , + 22 / 7
2. Area of Circle Formula , + pi * 2 * 2

Any help is very much appreciated. note that the order is not very important. i.e. it is fine to have

1. Calculate of Formula for Pi, / 7 + 22
2. Area of Circle formula * 2 + pi * 2

when I return the results as almost all the calculations are usually + or - only.

Regards,

Eugene

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-20 : 03:56:52

Try the scripts,

CREATE TABLE Details(DetailID INT,Title VARCHAR(250))

INSERT INTO Details VALUES(1,'Calculation of Formula for Pi')
INSERT INTO Details VALUES(2,'Area of Circle Formula')

SELECT * FROM Details

CREATE TABLE DetailsFormula(DetailID INT,Sign VARCHAR(2),Topic VARCHAR(50))

INSERT INTO DetailsFormula VALUES(1,'+','22')
INSERT INTO DetailsFormula VALUES(1,'/','7')

INSERT INTO DetailsFormula VALUES(2,'+','pi')
INSERT INTO DetailsFormula VALUES(2,'*','2')
INSERT INTO DetailsFormula VALUES(2,'*','2')

Create the following function,

CREATE FUNCTION Get_Details(@DetailID INT)
RETURNS VARCHAR(MAX)
as
begin
DECLARE @ValueList varchar(100)
SELECT @ValueList= COALESCE(@ValueList,'') + ' ' + CAST(((Sign + ' ' + Topic)) AS VARCHAR(300))
FROM Details INNER JOIN
DetailsFormula ON Details.DetailID=DetailsFormula.DetailID
where DetailsFormula.DetailID=@DetailID
return @ValueList
end

Now use the script,

SELECT DetailID, Title + dbo.Get_Details(Details.DetailID) FROM Details

The result set is,

1 Calculation of Formula for Pi + 22 / 7
2 Area of Circle Formula + pi * 2 * 2

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-20 : 06:54:00

Hi SQL-programmers

It works well, thank you. However, I do not understand what is going on. In the function, how does it return a string of all the values

select sign + ' ' + topic will return multiple rows of topic.

check out my blog at http://www.aquariumlore.blogspot.com

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-20 : 07:35:45

Hi EugeneLim11,

This behaviour is a little hard to understand but consider the following example


DECLARE @range TABLE ([it] INT)
DECLARE @foo INT

INSERT @range
      SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8

SELECT @foo = [it] FROM @range
SELECT @foo

Here what's happening in the


SELECT @foo = [it] FROM @range

Section is that @foo will iteratively gain the value of each row in the select statement. In this case this expands to be equivalent to


SELECT @foo = 1
SELECT @foo = 2
SELECT @foo = 3
SELECT @foo = 4
etc

So for each row in the select statement then the variable will be set. In sqp-programmers function the variable is added to itself for each row and thus builds up a string. Consider this:


DECLARE @foo INT
SET @foo = 1

DECLARE @range TABLE ([it] INT)

INSERT @range
      SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8

SELECT @foo = @foo + @foo FROM @range

SELECT @foo

Here @foo gets added to itself for each row. The result is actually 2 to the power (number of rows) which gives 256.

Have a play.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-20 : 07:58:02

Hi Charlie,

Thanks for explaining this. I was amazed and wondered why it works well, even when I add 15 rows. Your explanations are very clear. You should try a career as a Professor teaching database - I think makes it easier for students to understand some SQL.

Thanks to you and some of the regulars here (e.g. Visakh16, Peso etc) , I learnt a lot, and was able to contribute to some others who needs assistance. Once again, thank you very much Charlie.

Regards,

Eugene

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-01-20 : 09:48:05

It's a great explanation Charlie, I would just like to point out that I think the concept is much more "visible" using varchar columns:

DECLARE @foo varchar(50)
SET @foo = ''

DECLARE @range TABLE ([it] varchar(2))

INSERT @range
      SELECT '1,'
UNION SELECT '2,'
UNION SELECT '3,'
UNION SELECT '4,'
UNION SELECT '5,'
UNION SELECT '6,'
UNION SELECT '7,'
UNION SELECT '8,'

SELECT @foo = @foo + [it] FROM @range

SELECT @foo

- Lumbago
http://xkcd.com/327/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-20 : 11:18:49

good point -- that would have been more obvious!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources