Sql server Procedure Problem

Question

4.45/5 (4 votes)

See more:

SQL-Server-2012

I have two table like

SQL

CREATE TABLE parent
(ParentNum INT,
 ParentName VARCHAR(50));

CREATE TABLE child
(ChildNum INT,
 ChildParentNum INT,
 ChildName VARCHAR(20));

INSERT INTO parent VALUES(10,'John');
INSERT INTO parent VALUES(20,'Jane');
INSERT INTO parent VALUES(30,'Jackie');

INSERT INTO child VALUES(1,10,'Johnny')
INSERT INTO child VALUES(2,20,'Jackie')
INSERT INTO child VALUES(3,20,'Billy')
INSERT INTO child VALUES(4,20,'Sally')
INSERT INTO child VALUES(5,30,'Monique')
INSERT INTO child VALUES(5,30,'Monu')

now I want result like

ParentName | Child1    | Child2   | Child3   ....etc
John       | Johnny    |          |  
Jane       | Jackie    | Billy    | Sally
Jackie     | Monique   | Monu     |

Posted 12-May-14 21:54pm

Dharmendra-18

Updated 12-May-14 21:55pm

King Fisher

v2

Add a Solution

Comments

phil.o 13-May-14 3:56am

And what did you try?

syed shanu 13-May-14 4:13am

Try using Pivot

CHill60 13-May-14 5:28am

Please see my general comment below

Dinesh.V.Kumar 13-May-14 5:23am

Pivot is the only solution..

CHill60 13-May-14 5:28am

Please see my general comment below

CHill60 13-May-14 5:28am

To the guys suggesting Pivot ... could you give an example. The only results I'm getting when I try is ParentName, Johnny, Jackie, Billy etc etc

3 solutions

Solution 3

An alternative to solution 2 that doesn't involve changing the schema of the base table ...

I'm creating a temporary table #T

Select ParentName, ChildName, 
'Child' + CAST(ROW_NUMBER() OVER 
      (PARTITION BY ParentNum Order by ParentName,ChildName) AS varchar(1)) AS ChildNo
INTO #T
FROM child
INNER JOIN parent ON ChildParentNum=ParentNum
ORDER BY ParentName

SELECT *
FROM (
    SELECT 
        [ParentName],[ChildNo], [ChildName]
    FROM #T
) as s
PIVOT
(
    MAX([ChildName])
    FOR [ChildNo] IN ([Child1],[Child2],[Child3])
) AS pvt

SELECT * FROM #T gives the following results

PARENTNAME 	CHILDNAME 	CHILDNO
Jackie 		Monique 	Child1 
Jackie 		Monu 		Child2 
Jane 		Billy 		Child1 
Jane 		Jackie 		Child2 
Jane 		Sally 		Child3 
John 		Johnny 		Child1

We can now use #T in the PIVOT instead of the base table

SQL

SELECT *
FROM (
    SELECT
        [ParentName],[ChildNo], [ChildName]
    FROM #T
) as s
PIVOT
(
    MAX([ChildName])
    FOR [ChildNo] IN ([Child1],[Child2],[Child3])
) AS pvt

Incidentally, getting the ChildNo column was adapted from this CP article How to use ROW_NUMBER() to enumerate and partition records in SQL Server[^]

As suggested by Syed Asif Iqbal in Solution 2, you can use the contents of #T to create some dynamic SQL which you can then sp_executesql.
The only way I can think to do this is via a CURSOR similar to CBadger in Solution 1

DECLARE @varSQL VARCHAR(MAX)
SET @varSQL = 'SELECT *
FROM (
    SELECT 
        [ParentName],[ChildNo], [ChildName]
    FROM #T
) as s 
PIVOT
(
    MAX([ChildName]) FOR [ChildNo] IN ('

DECLARE @varChildNo VARCHAR(7)
DECLARE curChildNums CURSOR FOR
  SELECT DISTINCT ChildNo FROM #T ORDER BY ChildNo

OPEN curChildNums
FETCH NEXT FROM curChildNums INTO @varChildNo

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @varSQL = @varSQL + '[' + @varChildNo + '],'
  FETCH NEXT FROM curChildNums INTO @varChildNo
END

CLOSE curChildNums
DEALLOCATE curChildNums

-- Remove final comma
SET @varSQL = LEFT(@varSQL, LEN(@varSQL) - 1)
-- Add on the rest of the SQL Statement
SET @varSQL = @varSQL + ') ) AS pvt'

SELECT @varSQL

Posted 13-May-14 1:10am

CHill60

Solution 1

The best way would be to use a Cursor in a Cursor and enter the data into a TempTable.

Here is a very rough example of how to do it

SQL

declare @parent varchar(50),
		@child VARCHAR(20)

declare @DT table
(Parent varchar(50),
 Child1 VARCHAR(20),
 Child2 VARCHAR(20),
 Child3 VARCHAR(20),
 Child4 VARCHAR(20),
 Child5 VARCHAR(20),
 Child6 VARCHAR(20),
 Child7 VARCHAR(20))

declare curP cursor for
select parentnum
from parent

OPEN curP

fetch next from curP
into @parent

while @@FETCH_STATUS = 0
Begin
	declare curC cursor for
	select childname
	from child
	where childParentNum = @parent

	open curc

	fetch next from curC
	into @child

	while @@FETCH_STATUS = 0
	Begin
		insert into @DT
		-- Continue here to use for loop entering all the children

		fetch next from curC into @child
	end

	fetch next from curP into @parent
end

close curC
deallocate curC
close curP
deallocate curP

select * from @DT

Posted 12-May-14 23:24pm

CBadger

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2014-05-13T00:36:00

We have to induct intelligence about the child precedence somewhere to get the required result. I have modified the child structure adding number (1, 2, 3) against each parent. This have resolved the query easily.

SQL

DECLARE @parent TABLE 
(ParentNum INT,
 ParentName VARCHAR(50));
 
DECLARE @child TABLE 
(ID INT,
 ChildNum INT,
 ChildParentNum INT,
 ChildName VARCHAR(20)
 );
 
INSERT INTO @parent VALUES(10,'John');
INSERT INTO @parent VALUES(20,'Jane');
INSERT INTO @parent VALUES(30,'Jackie');
 
INSERT INTO @child VALUES(1,1,10,'Johnny')
INSERT INTO @child VALUES(2,1,20,'Jackie')
INSERT INTO @child VALUES(3,2,20,'Billy')
INSERT INTO @child VALUES(4,3,20,'Sally')
INSERT INTO @child VALUES(5,1,30,'Monique')
INSERT INTO @child VALUES(6,2,30,'Monu')

select ParentName, Isnull(Child1, '') Child1, Isnull(Child2, '') Child2, Isnull(Child3, '') Child3, Isnull(Child4, '') Child4, Isnull(Child5, '') Child5, Isnull(Child6, '') Child6
from
(
  select p.ParentName, ChildName, 'Child'+cast(ChildNum as varchar(2)) as ChildNum
  from @child c INNER JOIN @parent p on c.ChildParentNum = p.ParentNum
) d
pivot
(
  max(ChildName)
  for ChildNum in (Child1, Child2, Child3, Child4, Child5, Child6)
) piv;

For N number of child you can achieve the result by creating dynamic query of the same.