Sql server程序问题 [英] Sql server Procedure Problem

问题描述

我有两张表

I have two table like

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     | 

推荐答案

解决方案2的替代方案，不涉及更改基表的架构...



我正在创建一个临时表 #T

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 给出以下结果

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

我们现在可以在PIVOT而不是基表中使用 #T

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

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

顺便说一句，获取ChildNo列是根据此CP文章改编的如何使用ROW_NUMBER（）枚举和分区SQL Server中的记录 [< a href =http://www.codeproject.com/Articles/308281/How-to-Use-ROW-NUMBER -to-Enumerate-and-Partition-Rtarget =_ blanktitle =New Window> ^ ]



根据Syed Asif的建议解决方案2中的Iqbal，您可以使用#T的内容创建一些动态SQL然后 sp_executesql 。

我能做的唯一方法认为这样做是通过类似于解决方案1中的CBadger的CURSOR

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

最好的方法是在光标中使用光标并将数据输入到TempTable中。



这是一个非常粗略的例子，如何做到这一点/>

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

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

我们必须在某处获取有关孩子优先权的情报，以获得所需的结果。我修改了子结构，为每个父级添加了数字（1,2,3）。这很容易解决了这个问题。

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.

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.

这篇关于Sql server程序问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！