Single column from multiple rows in child 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

SQL Server 2005 Forums

Transact-SQL (2005)

Single column from multiple rows in child table

Author

Topic

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-10-22 : 04:22:21

Hi All,

I am working On Migrating the data from 1 database in SQL Server 2000 to another Normalized Db ON SQL Server 2005.
The DATABASE IS related TO HealthCare. As per company rules, i am not suppose to give exact table structure.
To be on more safe side,I am giving you requirement using two simple tables.

To cut short the things
We have two TABLE 1.Parent (Temp_EMP) & 2. Child (Temp_Emp_Child)
Linking BETWEEN two tables IS Temp_Emp.Empno = Temp_Emp_Child.ParentRefNo

Table's Structure
Create TABLE Temp_EMP
(
Empno int,
Ename Varchar(100),
Comment_Flag char
)

Create TABLE Temp_EMP_child
(ParentRefNo int,
LineNumber int,
comment Varchar(100)
)

Temporary Data

INSERT INTO Temp_Emp VALUES (1,'Pramod','Y')

INSERT INTO Temp_Emp VALUES (2,'Sunil','N')

INSERT INTO Temp_Emp VALUES (3,'Praveen','Y')

INSERT INTO temp_emp_child VALUES (1,1,'Hello')

INSERT INTO temp_emp_child VALUES (1,2,'How are u')

INSERT INTO temp_emp_child VALUES (3,1,'Welcome')

INSERT INTO temp_emp_child VALUES (3,2,'To My')

INSERT INTO temp_emp_child VALUES (3,2,'Home')

Expected Result

Empno Ename Comment
--------------------------------------
1 Pramod Hello How are u
2 Sunil NULL
3 Praveen Welcome To My Home

Important Note: The solution should be either simple select statement/Derived query / block (Which is not dependent on another block/user defined function/Store procedure).

Thanks in Advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-22 : 04:28:37

Refer this
http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Madhivanan

Failing to plan is Planning to fail

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-10-22 : 04:57:20

I have already checked this link.
As i said already and also repeat that "The solution should be either simple select statement/Derived query / block (Which is not dependent on another block/user defined function/Store procedure)."

This links talk about usage of function / Using looping and others..I am migrating data from table with millions of records, so i feel that it will not be optimum solution.

quote:
Originally posted by madhivanan

Refer this
http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Madhivanan

Failing to plan is Planning to fail

corder
Starting Member

2 Posts

Posted - 2007-10-22 : 10:48:33

I came across this idea.

SELECT
EmpNo,
Comment = LEFT(o.list, LEN(o.list))
FROM
Temp_Emp c
CROSS APPLY
(
SELECT
Comment + ' ' AS [text()]
FROM
Temp_Emp_Child s
WHERE
s.ParentRefNo = c.Empno
ORDER BY
ParentRefNo
FOR XML PATH('')
) o (list)

There are still some issues. I believe it will only work with sql2005 so you'd need the base tables on your 2005 machine. Also, I don't know if it'll ultimately be any faster than looping or a function implementation.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-10-23 : 01:29:55

Hi Coral.

Thanks a lot.. You query is giving me the required result.
As far as performance issue is concerned, i will fine tune it..

Once again thanks a lot..

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 02:37:21

"(Which is not dependent on another block/user defined function/Store procedure)"

I can't imagine why a User Defined Function would be excluded from the possible solutions here, but Hey! I don't make the rules where you work.

Kristen

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-10-23 : 03:06:54

I know that it is very much easy using UDF or Stored Proc but had to look for different solution because the data that i am going to migrate belongs to a Health Trust.
Due of security,Confidential nature of data, only Select right will be granted to us.

The trust has clearly stated that they are not going to allow creation of temporary table, function, stored proc for some reason. Due to this we are facing lot of problems.

It is possible to convince a technical guy but not all (at trust), so i was looking for a solution which does not use UDF / Stored Proc etc..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 03:13:21

No one can stop you from creating a temporary table.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 03:50:37

What an utterly stupid approach.

Send the people from the Trust here so we can explain it to them.

I understand that they may have security concerns. But short of giving NO ONE access to the data AT ALL they need to tool up for managing the security, not get everyone to work with both hands tied behind their backs.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources