作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
假设我在 SQL (server 2005) 中设置了一个父子结构:
CREATE TABLE parent (Id INT IDENTITY PRIMARY KEY, Name VARCHAR(255))
CREATE TABLE child (Id INT IDENTITY PRIMARY KEY, parentId INT, Name VARCHAR(255))
insert into parent select 'parent with 1 child'
insert into parent select 'parent with 2 children'
insert into child(name, parentid) select 'single child of parent 1', 1
insert into child(name, parentid) select 'child 1 of 2 of parent 2', 2
insert into child(name, parentid) select 'child 2 of 2 of parent 2', 2
有没有办法为每个父项返回一行,并将其子项作为列?喜欢:
parent.Id, parent.Name, child(1).Id, child(1).Name, child(2).Id, child(2).Name
开始于:
select * from parent p
left outer join child c1 on c1.parentid = p.id
最佳答案
您的示例接近旋转,但我认为旋转功能不适用于此示例。
为了保持理智,我已将您的示例重命名为使用“部门人员”,而不是“ child parent ”。
所以,首先是表格和一些数据
DECLARE @Department TABLE
(
DepartmentID int
,DepartmentName varchar(50)
)
DECLARE @Person TABLE
(
PersonID int
,PersonName varchar(50)
,DepartmentID int
)
INSERT INTO @Department
( DepartmentID, DepartmentName )
SELECT 1, 'Accounting' UNION
SELECT 2, 'Engineering' UNION
SELECT 3, 'Sales' UNION
SELECT 4, 'Marketing' ;
INSERT INTO @Person
( PersonID, PersonName, DepartmentID )
SELECT 1, 'Lyne', 1 UNION
SELECT 2, 'Damir', 2 UNION
SELECT 3, 'Sandy', 2 UNION
SELECT 4, 'Steve', 3 UNION
SELECT 5, 'Brian', 3 UNION
SELECT 6, 'Susan', 3 UNION
SELECT 7, 'Joe', 4 ;
现在我想展平模型,我将使用临时表,因为我有表变量——但是“真实表”上的 View 也不错。
/* Create a table with:
DepartmentID, DepartmentName, PersonID, PersonName, PersonListIndex
This could be a view instead of temp table.
*/
IF object_id('tempdb.dbo.#tmpTbl','U') IS NOT NULL
DROP TABLE #tmpTbl
;
WITH prs
AS ( SELECT PersonID
,PersonName
,DepartmentID
,row_number() OVER ( PARTITION BY DepartmentID ORDER BY PersonID ) AS [PersonListIndex]
FROM @Person
),
dptprs
AS ( SELECT d.DepartmentID
,d.DepartmentName
,p.PersonID
,p.PersonName
,p.PersonListIndex
FROM @Department AS d
JOIN prs AS p ON p.DepartmentID = d.DepartmentID
)
SELECT * INTO #tmpTbl FROM dptprs
-- SELECT * FROM #tmpTbl
动态列就是动态查询,我会逐行组成一个表
/* Table to compose dynamic query */
DECLARE @qw TABLE
(
id int IDENTITY(1, 1)
,txt nvarchar(500)
)
/* Start composing dynamic query */
INSERT INTO @qw ( txt ) VALUES ( 'SELECT' )
INSERT INTO @qw ( txt ) VALUES ( '[DepartmentID]' )
INSERT INTO @qw ( txt ) VALUES ( ',[DepartmentName]' ) ;
/* fetch max number of employees in a department */
DECLARE @i int ,@m int
SET @m = (SELECT max(PersonListIndex) FROM #tmpTbl)
/* Compose dynamic query */
SET @i = 1
WHILE @i <= @m
BEGIN
INSERT INTO @qw ( txt )
SELECT ',MAX(CASE [PersonListIndex] WHEN '
+ cast(@i AS varchar(10)) + ' THEN [PersonID] ELSE NULL END) AS [Person_'
+ cast(@i AS varchar(10)) + '_ID]'
INSERT INTO @qw ( txt )
SELECT ',MAX(CASE [PersonListIndex] WHEN '
+ cast(@i AS varchar(10)) + ' THEN [PersonName] ELSE NULL END) AS [Person_'
+ cast(@i AS varchar(10)) + '_Name]'
SET @i = @i + 1
END
/* Finish the dynamic query */
INSERT INTO @qw (txt) VALUES ( 'FROM #tmpTbl' )
INSERT INTO @qw (txt) VALUES ( 'GROUP BY [DepartmentID], [DepartmentName]' )
INSERT INTO @qw (txt) VALUES ( 'ORDER BY [DepartmentID]' )
-- SELECT * FROM @qw
现在,将所有查询行连接到一个变量中并执行
/* Create a variable with dynamic sql*/
DECLARE @exe nvarchar(4000)
SET @exe=''
SELECT @exe = @exe + txt + ' ' FROM @qw ORDER BY id
/* execute dynamic sql */
EXEC master..sp_executesql @exe
结果如下:
关于sql-server - 获得单亲,所有 child 排成一排,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1958054/
我是一名优秀的程序员,十分优秀!